How to improve this query?

Question

Post reply

How to improve this query?

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 9, 2010 at 5:07 am

#223572

I have a table called AllRecords with around 7 million records.The table has following structure:
create table Allrecords(Recordid int primary key,
SubrecordID int,
Sequence_id int,
Substantiation_Indicator char(1),
Substantiation_Sequence_Letter_Code varchar(10))
The data is something like this:
INSERT INTO Allrecords
SELECT 1,5,7,'N','ABC'
UNION
SELECT 38,5,9,'Y','XYZ'
UNION
SELECT 9,6,9,'Y','aaa'
UNION
SELECT 22,6,3,'O','ddd'
UNION
SELECT 27,6,0,'P','lll'
UNION
SELECT 30,2,6,'D','WWW'
There are some other columns but I am concerened with these columns only for my query.
RecordID has clustered index and Subrecords has nonclusterd index.
There can be multiple subrecordid for a recordid.
Now here is the query which I want to improve:
INSERT INTO #Temptable
(s1.SubrecordId,
s1.RecordId)
SELECT s1.SubrecordId, s1.RecordId
FROM dbo.AllRecords as s1
inner join (SELECT MAX(RecordId) AS RecordId
FROM dbo.AllRecordsAS s2
GROUP BY SubrecordId) as sMax
on sMax.RecordId = s1.RecordId
INNER JOIN dbo.Table3 ss
ON s1.Sequence_ID = ss.Sequence_ID
WHERE (s1.Substantiation_Indicator = 'N' OR s1.Substantiation_Sequence_Letter_Code = 'IEPR')
Here I am trying to populate #Temptable with records so that I have Only 1 row for each subrecordID having bigger recordid. So I want Output like this:
RecordidSubrecordIDSequence_idSubstantiation_IndicatorSubstantiation_Sequence_Letter_Code
3859YXYZ
2760Plll
3026DWWW
To filter out max records, I am putting a join of same table with its max resultset and this is killing my proc since I am fetching all records in subquery without filter.
It takes around 30 secs just to run the MAX subquery.
Is there any better way to write it or any other technique may help?
Any help is appreciated.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Please post the actual execution plan for this query.

See the link "Performance Problems" in my signature for how to do this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

natalie.ignatieva SSChasing Mays Points: 622 More actions · Answer 2

Please, try this code and let me know if it works faster for you:

SELECT MAX(RecordId) AS RecordId

INTO #T

FROM dbo.AllRecordsAS s2

GROUP BY SubrecordId

INSERT INTO #Temptable

(s1.SubrecordId,

s1.RecordId)

SELECT s1.SubrecordId, s1.RecordId

FROM dbo.AllRecords as s1

inner join #T as sMax

on sMax.RecordId = s1.RecordId

INNER JOIN dbo.Table3 ss

ON s1.Sequence_ID = ss.Sequence_ID

WHERE (s1.Substantiation_Indicator = 'N' OR s1.Substantiation_Sequence_Letter_Code = 'IEPR')

Lowell SSC Guru Points: 323506 More actions · Answer 3

well, without the execution plan that Wayne requested, we can only offer the obvious advice:

the problem is probably the WHERE statement:

WHERE (s1.Substantiation_Indicator = 'N'

OR s1.Substantiation_Sequence_Letter_Code = 'IEPR')

since you have an OR, it's going to require a table scan, so both conditions can be tested.

do you have any indexes that would be on those two columns? Substantiation_Indicator and Substantiation_Sequence_Letter_Code

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323506 More actions · Answer 4

this is just a WAG to get the max's without the temp table and double join;

does this return the desired results? how does it perform?

SELECT

s1.Recordid,

s1.SubrecordID,

s1.Sequence_id ,

ss.Substantiation_Indicator,

ss.Substantiation_Sequence_Letter_Code

FROM

(SELECT row_number() OVER (PARTITION BY Recordid,SubrecordId ORDER BY SubrecordId,Recordid DESC) As RW,

Recordid,

SubrecordId,

Sequence_id,

FROM dbo.AllRecords) s1

INNER JOIN dbo.Table3 ss

ON s1.Sequence_ID = ss.Sequence_ID

WHERE RW = 1

AND (s1.Substantiation_Indicator = 'N' OR s1.Substantiation_Sequence_Letter_Code = 'IEPR')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 5

Thanks so much for all the responses...Its so great to see that people try to help even when they feel that they dont have all the required inputs. Although i made this query work faster in a different manner...but u guys certainly gave a direction.

This is how I made it to work in 2 seconds from a bad 23 seconds:

I inserted all data in a new temporary staging table from same query but this time the query didnt have where clause and MAX subquery(somehow the MAX subquery was taking the max time since it was grouping all 6 million records). By removing this suquery, i managed to lessen the no of records, since there was inner join.

Then I Put all this data in the actual temp table and use the where clause and MAX subquery. This is fast now since the no of records on which grouping is to be done are less....

Hope I am clear

thanks again all

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.