[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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
0
ordo
Asked:
ordo
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
"The logic run for each record is extensive"

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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Mind readers we ain't.  Copy-paste the Stored Procedure into this question in a code block, and perhaps we can help..
0
 
ordoAuthor Commented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ordoAuthor Commented:
To elaborate the stored procedure simple selects about 10 columns from each record that meets the desired created date criteria.

Thanks again.
0
 
Scott PletcherSenior DBACommented:
With such limited info, the only things that are clear so far are:

1) cluster the table by [created date]
2) do as much set-based processing as you can.  if you must use a cursor, make it as efficient as possible.
3) >> 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? <<  Not likely, but still possible.
0
 
ordoAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
Rather than doing this:

DECLARE cursor_name CURSOR ... FOR
SELECT ...
FROM ...
...
WHILE loop
    FETCH NEXT FROM ...
    ...
...


as much as possible use standard "SELECT ..." to process the data.


Again, w/o being able to see the code, that's just a very general guideline.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
another example:

instead of looping each row and doing

column10 = column8 + column9

he's saying:

update table
set column10 = column8 + column9

The logic may not change, but the way you process the logic can be optimized.
0
 
ordoAuthor Commented:
Thank you all.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now