SQL Select
We have a SQL stored procedure that selects and returns records against a SQL DB table containing 3 million + records.
The selection logic is very simple, any record that has a created date greater than a variable that is passed in the procedure. The resulting selected record set can be as few as a couple dozen or number in the 100,000s.
The logic run for each record is extensive, to the extent that it takes a couple of seconds for each record to be processed.
Could performance be improved if we were to do a select * insert into a new table with just the subset of records that we want, and then run the logic against that limited number of records?
Thanks for your help
The selection logic is very simple, any record that has a created date greater than a variable that is passed in the procedure. The resulting selected record set can be as few as a couple dozen or number in the 100,000s.
The logic run for each record is extensive, to the extent that it takes a couple of seconds for each record to be processed.
Could performance be improved if we were to do a select * insert into a new table with just the subset of records that we want, and then run the logic against that limited number of records?
Thanks for your help
Mind readers we ain't. Copy-paste the Stored Procedure into this question in a code block, and perhaps we can help..
ASKER
Kyle,
The logic/operations preformed against each record isn't going to change and is way to involved to bore you with. I guess my question is more related to memory usage and processor utilization.
Thanks
The logic/operations preformed against each record isn't going to change and is way to involved to bore you with. I guess my question is more related to memory usage and processor utilization.
Thanks
ASKER
To elaborate the stored procedure simple selects about 10 columns from each record that meets the desired created date criteria.
Thanks again.
Thanks again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott,
Table is clustered by [create date] column.
Can you elaborate a little bit on your second suggestion (set-based processing)?
Thanks for your help.
Table is clustered by [create date] column.
Can you elaborate a little bit on your second suggestion (set-based processing)?
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all.
Can you expound on that? What are you doing in the logic run? Does it make more sense to store the data differently?
Without seeing the relevant sections (or an approximation of) it will be difficult to determine if you're better off using a temporary table.