I have a table called AllRecords with around 7 million records.The table has following structure:
create table Allrecords(Recordid int primary key,
The data is something like this:
INSERT INTO Allrecords
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
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:
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.