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 |
|
palvinder
Starting Member
4 Posts |
 Posted - 2006-12-03 : 06:03:18
|
| HelloRight i want to ask your opinions on the best way to process data from a table. The way it works is that Table A has 30 records of raw data inserted into it every 5 minutes. I can change the design of this table to anything you like as long as it has one column for this raw data.Once this data is added i then NEED to run a stored procedure that calculates some math and inserts it into Table B. In a test, the stored procedure takes less than 1 second to complete to process a single record, but as i'm inserting 30 records at the same time it hogs the system CPU for around 40-60 seconds, causing other applications to freeze.So what is the best way to run the store procedure so that when data is added to Table A it processes it (i was thinking triggers, but this would cause same cpu bottle neck as above). I would prefer staggered execution so that 5 records are processed at a time. Is this possible? At present the table would look like this:ID: bigint, primary key, Indexedraw_data: int, value returned from an external apptimestamp: default value getdate(), just for reference and orderingprocessed: bit, see if record has been calculated, value inserted into Table Bprocessing: bit, see if record is currently been processed by SPi have the processed and processing columns to let my stored procedure know wether to skip the record or not. Again there might be a better way to do this which will not require these columns.Would love to know your thoughts and ideas of how to achieve this.RegardsPalvinder |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-03 : 08:26:21
|
| You could "assemble" the 30 records in a temporary table, and then insert them into the real table (i.e. in your stored procedure) en masse.I'm surprised that 30 Sproc calls is taking 30 - 40 seconds if all that each one is doing is inserting a record in a table. Might be some efficiency improvements that could be made there?Kristen |
 |
|
|
|
|
|
|
|
Best method to improve multiple execution of SP