Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
peo
Starting Member
15 Posts |
 Posted - 2011-11-06 : 04:48:29
|
| My application collects data from hundreds of different sources, each with a unique id. Depending on what calculation has to be done using the incoming data, it is distributed to a number of queues (20-30 queues), each serviced by it's own thread. But in the end, all threads are inserting the result in the same table, at a total rate of 10-15 inserts per second. The keys are the unique id and a timestamp.Would SQL Server Express 2008R2 perform better if each queue/thread had its own table?Another issue: How much does the number of columns in a table, influences the time it takes to do an insert? I suppose the indexing is spending most of the time and therefore to split a table with many columns into several smaller ones is not a good idea, or can the server gain from this, processing several "smaller" inserts in parallell?/P |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-11-15 : 13:55:44
|
| Be aware that narrow tables tend to perform better than wide tables, depending on correct indexing etc. So when you say "many columns" that sends up a warning flag for me. Can you be more specific? How many columns and how wide is the row? |
 |
|
|
peo
Starting Member
15 Posts |
Posted - 2011-12-21 : 12:17:28
|
| Sorry for late response, an accident forced me to spend some weeks horizontal.The table has grown (caused by new demands from my customer) and is today about 80 columns (floats). The index is the same as the keys, ID (string) and timestamp (datetime)./P |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
peo
Starting Member
15 Posts |
Posted - 2011-12-22 : 08:21:10
|
| Well, since I originally posted the question, we have upgraded from SQL Server Express to Web Edition. So right know I don't have a performance problem anymore, as the servern now can take advantage of the 16 GB RAM avaliable and multiple cores, instead of the Express limit of 1 GB and one core.But the question is of interest anyhow, as when the number of data sources grow, the number of inserts per second will grow./P |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-12-26 : 14:27:38
|
| I don't have a good answer in terms of the parallel possibilities.But if you are going to split the tables, you could have a number of new issues. Instead of having one insert stored procedure now you are going to have one for each table. When there is a table change, you may need to change several tables rather than the one. Maybe later you need to do some reporting, same thing, instead of 1 select for 1 table, now you may need several selects or a select involving several tables.In terms of retrieval (select), if your table has several columns but you frequently need only the same few columns, you could create an index (or covering index) on these few columns. So at least from a select point of view, it will be irrelevant how many columns there are.Another idea is to take that single table and partition it. Again this might not help in terms of insert but would help in terms of select. |
|
|
|
|
|
|
|
|
Performance: One or multiple tables