Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Select

Posted on 2014-09-23
9
269 Views
Last Modified: 2014-09-23
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
Comment
Question by:ordo
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40340047
"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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40340078
Mind readers we ain't.  Copy-paste the Stored Procedure into this question in a code block, and perhaps we can help..
0
 

Author Comment

by:ordo
ID: 40340083
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:ordo
ID: 40340087
To elaborate the stored procedure simple selects about 10 columns from each record that meets the desired created date criteria.

Thanks again.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 334 total points
ID: 40340113
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
 

Author Comment

by:ordo
ID: 40340143
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 334 total points
ID: 40340154
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
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 166 total points
ID: 40340230
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
 

Author Closing Comment

by:ordo
ID: 40340383
Thank you all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question