Need help with Query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with Query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-11-05 : 14:11:02

I have this query below is working fine but it is slow and I wonder if anyone has a better way to improve
this query. Thank you very much in advance. SQL 2005. The table has about 30 Mil rows.

-- Business rule:
-- If Claimline has multiples rows, return the Max LastUpdate (one row per claimline). Please see result want below.

DROP TABLE TestTable
GO
CREATE TABLE [dbo].[TestTable]
(
[LASTUPDATE] [datetime] NULL,
[CLAIMID] [char](15) NOT NULL,
[CLAIMLINE] [int] NOT NULL,
[RuleId] [char](15) NOT NULL
)
GO

INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:49:14:133PM', '0512666117A1', '0', '245')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:49:14:197PM', '0512666117A1', '0', '311')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:49:14:257PM', '0512666117A1', '1', '219')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:51:01:000PM', '0512666117A1', '3', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:49:14:320PM', '0512666117A1', '4', '503')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Jan 23 2008 3:50:51:000PM', '0512666117A1', '4', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:31:443PM', '0512666117A2', '0', '245')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:31:553PM', '0512666117A2', '0', '311')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:31:663PM', '0512666117A2', '1', '219')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:58:000PM', '0512666117A2', '2', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:58:000PM', '0512666117A2', '3', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:31:773PM', '0512666117A2', '4', '503')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Feb 28 2008 3:42:58:000PM', '0512666117A2', '4', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:21:670PM', '0519967178A1', '0', '311')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:34:690PM', '0519967178A1', '1', '225')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:21:780PM', '0519967178A1', '1', '530')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:56:000PM', '0519967178A1', '1', '917')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:34:813PM', '0519967178A1', '2', '225')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 1:07:21:843PM', '0519967178A1', '2', '530')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 9:52:02:453AM', '0519969980A1', '0', '311')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 9:52:02:577AM', '0519969980A1', '1', '503')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 9:52:02:703AM', '0519969980A1', '1', '530')
INSERT TestTable (LASTUPDATE, CLAIMID, CLAIMLINE, RuleId )
VALUES ('Nov 14 2007 9:52:21:000AM', '0519969980A1', '1', '917')

SELECT *
FROM TestTable
ORDER BY ClaimId ASC
GO

LASTUPDATE CLAIMID CLAIMLINE RuleId
----------------------- --------------- ----------- ---------------
2008-01-23 15:49:14.133 0512666117A1 0 245
2008-01-23 15:49:14.197 0512666117A1 0 311 * Return

2008-01-23 15:49:14.257 0512666117A1 1 219 *
2008-01-23 15:51:01.000 0512666117A1 3 917 *

2008-01-23 15:49:14.320 0512666117A1 4 503
2008-01-23 15:50:51.000 0512666117A1 4 917 *

2008-02-28 15:42:31.443 0512666117A2 0 245
2008-02-28 15:42:31.553 0512666117A2 0 311 *
2008-02-28 15:42:31.663 0512666117A2 1 219 *
2008-02-28 15:42:58.000 0512666117A2 2 917 *
2008-02-28 15:42:58.000 0512666117A2 3 917 *
2008-02-28 15:42:31.773 0512666117A2 4 503
2008-02-28 15:42:58.000 0512666117A2 4 917 *

2007-11-14 13:07:21.670 0519967178A1 0 311 *

2007-11-14 13:07:34.690 0519967178A1 1 225
2007-11-14 13:07:21.780 0519967178A1 1 530
2007-11-14 13:07:56.000 0519967178A1 1 917 *

2007-11-14 13:07:34.813 0519967178A1 2 225 *
2007-11-14 13:07:21.843 0519967178A1 2 530

2007-11-14 09:52:02.453 0519969980A1 0 311 *
2007-11-14 09:52:02.577 0519969980A1 1 503
2007-11-14 09:52:02.703 0519969980A1 1 530
2007-11-14 09:52:21.000 0519969980A1 1 917 *

print @@servername
-- Result want:
LASTUPDATE CLAIMID CLAIMLINE RuleId
----------------------- --------------- ----------- ---------------
2008-01-23 15:49:14.197 0512666117A1 0 311 * Return
2008-01-23 15:49:14.257 0512666117A1 1 219
2008-01-23 15:51:01.000 0512666117A1 3 917
2008-01-23 15:50:51.000 0512666117A1 4 917

2008-02-28 15:42:31.553 0512666117A2 0 311 *
2008-02-28 15:42:31.663 0512666117A2 1 219 *
2008-02-28 15:42:58.000 0512666117A2 2 917 *
2008-02-28 15:42:58.000 0512666117A2 3 917 *
2008-02-28 15:42:58.000 0512666117A2 4 917 *

2007-11-14 13:07:21.670 0519967178A1 0 311 *
2007-11-14 13:07:56.000 0519967178A1 1 917 *
2007-11-14 13:07:34.813 0519967178A1 2 225 *
2007-11-14 09:52:02.453 0519969980A1 0 311 *
2007-11-14 09:52:21.000 0519969980A1 1 917 *

-- Testing...

SELECT *, ROW_NUMBER() OVER (PARTITION BY CLAIMLINE ORDER BY LastUpdate DESC) as 'rn'
FROM TestTable
WHERE rn = 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 14:52:55

First: I don't think your posted query is working fine! It is throwing an error...

Second: I have corrected that query (see below).

Third: Do you have any problems using this query and need to speed it up?


select* from
(SELECT *, ROW_NUMBER() OVER (PARTITION BY CLAIMID,CLAIMLINE ORDER BY LastUpdate DESC) as 'rn' 
FROM TestTable )t
WHERE rn = 1

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 16:46:54

There is very little server strain from what you have illustrated, so the lag is likly due to your CPU is not addiquate, and the queries are not the issue. The only alternative that will increase performance is possibly to use a update statement for the RowNumbering with a clustered index. If you want to try that approach please let us know and someone can help you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-11-05 : 17:46:14

Thank you for your suggestions, I will try the row number first and look into indexes before I try new methods. Again, thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 18:15:21

[code]SELECT tt.*
FROM TestTable AS tt
INNER JOIN (
SELECT ClaimLine,
MAX(LastUpdate) AS LastUpdate
FROM TestTable
GROUP BY ClaimLine
) AS w ON w.ClaimLine = tt.ClaimLine
AND w.LastUpdate = tt.LastUpdate[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources