SQL server/ ADO.NET performance
-
I have an application (C#/ADO.NET) that collects alarms from equipment at around 100 sites, through concurrent TCP/IP listeners. Per day I receive about 1 million messages (~10/sec). I used to insert them directly into the destination table, but that would lock up the table so user access (read/reporting) was hardly possible. Now I insert the data in a intermediate table and upload it every 5 min. to the final table with SQl agent. First of all, this doesn't look like an elegant solution. Furthermore, performance is still quite bad. Even though I have just this one DB/table, SQL-server uses up all available memory (1.5Gb out of 2Gb) and all available CPU, 24x7; I'm just wainting for a melt-down... Anybody have any experience with this amount of transactions, and/or any suggestions? Thanks.
-
I have an application (C#/ADO.NET) that collects alarms from equipment at around 100 sites, through concurrent TCP/IP listeners. Per day I receive about 1 million messages (~10/sec). I used to insert them directly into the destination table, but that would lock up the table so user access (read/reporting) was hardly possible. Now I insert the data in a intermediate table and upload it every 5 min. to the final table with SQl agent. First of all, this doesn't look like an elegant solution. Furthermore, performance is still quite bad. Even though I have just this one DB/table, SQL-server uses up all available memory (1.5Gb out of 2Gb) and all available CPU, 24x7; I'm just wainting for a melt-down... Anybody have any experience with this amount of transactions, and/or any suggestions? Thanks.
Do you have the relevant clustered index on your table? Your table should have a clustered index so that new items are added to the end of the index and the index does not then need to be reordered. Affordable and reliable hosting? Click here!