My question is more of a design question rather than code. I'm trying to INSERT trades on stocks, as they come in, into SQL Server.
我的问题更多的是设计问题而不是代码。我试图将股票的交易插入到SQL Server中。
Trades can come in many times a sec and I'm receiving trades from 15 stocks at the same time so there could potentially be a lot of trades in the same sec. My question is what is the best way to do the INSERT?
交易可以每秒多次进行,我同时接收15种股票的交易,因此可能会在同一秒内进行大量交易。我的问题是INSERT的最佳方法是什么?
-
open one connection at the start of trading session and continuously insert trades as they are received will this slow down my app? The trading session is 12hrs, does SQL Server allow a connection to last that long?
在交易时段开始时打开一个连接,并在收到交易时不断插入交易,这会减慢我的应用程序吗?交易时间是12小时,SQL Server是否允许连接持续那么长时间?
-
Collect trades in memory and do
BulkInsertonce every x mins? I'd rather not keep any trades in memory as this will slow things down and use up a lot of ram ... is there a better way to do this?收集内存中的交易并每隔x分钟进行一次BulkInsert?我宁愿不在记忆中保留任何交易,因为这会减慢事情并耗尽大量的内存......有没有更好的方法来做到这一点?
2 个解决方案
#1
0
The short answer (and rather ambiguous due to your question), is "it depends". What it depends on is what you're using to build the client application.
简短的答案(由于你的问题而相当模糊)是“它取决于”。它依赖的是您用于构建客户端应用程序的内容。
If it's a web application, built with PHP/ASP, there shouldn't really be any problem with multiple inserts. SQL Server intrinsically supports this due to the way it's designed, the same goes for most RDBMS models. Of course, a bulk insert is going to have less strain, it's one operation as opposed to multiple threads of data, but 15 stocks....you're still talking about a small time operations for something that's designed to handle BIG data.
如果它是一个使用PHP / ASP构建的Web应用程序,那么多个插入应该没有任何问题。 SQL Server因其设计方式而内在支持这一点,大多数RDBMS模型也是如此。当然,批量插入的压力会小一些,这是一个操作,而不是多个数据线程,而是15个库存....你还在谈论一些小时间操作,用于处理大数据的东西。
What I would probably suggest is you have an intermediate that "listens" for data, and opens a connection to the database when an transaction is in progress. In PHP we usually call this "long-polling", which is a ubiquitous term for any exchange of data that is "pushed" to and from a server. There's plenty of documentation online to help you out with this, including a Wikipedia page (if you're into that kind of stuff).
我可能建议你有一个“监听”数据的中间件,并在事务进行时打开与数据库的连接。在PHP中,我们通常称之为“长轮询”,这是一种无处不在的术语,用于任何“推送”到服务器和从服务器“推送”的数据交换。网上有很多文档可以帮助你解决这个问题,包括一个*页面(如果你有这样的东西)。
#2
1
You don't describe exactly how many rows per second are being inserted. SQL Server on a good machine (whatever that means) can easily handle hundreds of sequential inserts per second depending of course on what else the machine is doing and how fast the machine/hard disks (SSDS?)/amount of memory available. So what you are asking is can someone drive from NY to California in a week?
您没有准确描述每秒插入的行数。良好的机器上的SQL Server(无论这意味着什么)可以轻松处理每秒数百个顺序插入,当然还取决于机器正在做什么以及机器/硬盘(SSDS?)/可用内存的速度。所以你问的是,有人可以在一周内从纽约开车到加利福尼亚吗?
If you find that your machine cannot handle tens or hundreds of thousand of inserts per second you may try inserting rows into a narrow table (as few narrow columns as possible ) into a table without keys. I have see SQL Server successfully handle enormous amount of inserts into such a table. I would at the end of the day copy the data into another clustered table on which I run all my processing.
如果您发现您的机器每秒无法处理数十或数十万个插入,您可以尝试将行插入到一个窄表(尽可能少的窄列)中,而不是键。我已经看到SQL Server成功处理了大量插入到这样的表中。我会在一天结束时将数据复制到另一个集群表中,我在其上运行所有处理。
Finally you may want to look at Service broker which can use messaging to handle very large volumes asynchronously.
最后,您可能希望查看Service Broker,它可以使用消息传递来异步处理非常大的卷。
An active connection will not time out.
活动连接不会超时。
#1
0
The short answer (and rather ambiguous due to your question), is "it depends". What it depends on is what you're using to build the client application.
简短的答案(由于你的问题而相当模糊)是“它取决于”。它依赖的是您用于构建客户端应用程序的内容。
If it's a web application, built with PHP/ASP, there shouldn't really be any problem with multiple inserts. SQL Server intrinsically supports this due to the way it's designed, the same goes for most RDBMS models. Of course, a bulk insert is going to have less strain, it's one operation as opposed to multiple threads of data, but 15 stocks....you're still talking about a small time operations for something that's designed to handle BIG data.
如果它是一个使用PHP / ASP构建的Web应用程序,那么多个插入应该没有任何问题。 SQL Server因其设计方式而内在支持这一点,大多数RDBMS模型也是如此。当然,批量插入的压力会小一些,这是一个操作,而不是多个数据线程,而是15个库存....你还在谈论一些小时间操作,用于处理大数据的东西。
What I would probably suggest is you have an intermediate that "listens" for data, and opens a connection to the database when an transaction is in progress. In PHP we usually call this "long-polling", which is a ubiquitous term for any exchange of data that is "pushed" to and from a server. There's plenty of documentation online to help you out with this, including a Wikipedia page (if you're into that kind of stuff).
我可能建议你有一个“监听”数据的中间件,并在事务进行时打开与数据库的连接。在PHP中,我们通常称之为“长轮询”,这是一种无处不在的术语,用于任何“推送”到服务器和从服务器“推送”的数据交换。网上有很多文档可以帮助你解决这个问题,包括一个*页面(如果你有这样的东西)。
#2
1
You don't describe exactly how many rows per second are being inserted. SQL Server on a good machine (whatever that means) can easily handle hundreds of sequential inserts per second depending of course on what else the machine is doing and how fast the machine/hard disks (SSDS?)/amount of memory available. So what you are asking is can someone drive from NY to California in a week?
您没有准确描述每秒插入的行数。良好的机器上的SQL Server(无论这意味着什么)可以轻松处理每秒数百个顺序插入,当然还取决于机器正在做什么以及机器/硬盘(SSDS?)/可用内存的速度。所以你问的是,有人可以在一周内从纽约开车到加利福尼亚吗?
If you find that your machine cannot handle tens or hundreds of thousand of inserts per second you may try inserting rows into a narrow table (as few narrow columns as possible ) into a table without keys. I have see SQL Server successfully handle enormous amount of inserts into such a table. I would at the end of the day copy the data into another clustered table on which I run all my processing.
如果您发现您的机器每秒无法处理数十或数十万个插入,您可以尝试将行插入到一个窄表(尽可能少的窄列)中,而不是键。我已经看到SQL Server成功处理了大量插入到这样的表中。我会在一天结束时将数据复制到另一个集群表中,我在其上运行所有处理。
Finally you may want to look at Service broker which can use messaging to handle very large volumes asynchronously.
最后,您可能希望查看Service Broker,它可以使用消息传递来异步处理非常大的卷。
An active connection will not time out.
活动连接不会超时。