May 10, 2008 at 10:21 am
I've following table and data
tblSEL
Date_Taken | Time | Main_ID | Value
-----------------------------------------------
5/11/2008 12:00:00 AM | 500 | 233 | 4780
5/11/2008 12:00:00 AM | 500 | 239 | 3122
5/11/2008 12:00:00 AM | 515 | 233 | 5435
5/11/2008 12:00:00 AM | 530 | 233 | 6799
5/11/2008 12:00:00 AM | 545 | 233 | 4090
5/11/2008 12:00:00 AM | 600 | 234 | 2312
5/11/2008 12:00:00 AM | 615 | 233 | 7409
...
...
...
tblSEL having more than 6million rows
*Date_Taken datatype is smalldatetime
tblLocation
Main_ID | Location
---------------------
233 | PRK
234 | PER
239 | SAB
...
...
...
i've following query to getting the last 7month data
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID, t1.WATER_ULEVEL
FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken>=CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101) AND
t1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time
How to adjust this query to make sure i get same data and also run at the best performance.
May 10, 2008 at 4:59 pm
Post the query plan XML for the query. Also, here is a link that explains how to get better results from these forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 11, 2008 at 11:48 am
My first question would be, why do you have a join to the Location table when you're not selecting anything out of it? Are you just using that as a filter?
Next question would be... what are you going to do with the output? Currently, it only takes 6 seconds to return the desired output (117K rows) from 6 million rows spread across 10 years. And what do you want for performance? Is 6 seconds with only primary keys present too slow? If you want to get it down to 2 seconds, you can add the following covering index...
CREATE INDEX [tblSEL2] ON [dbo].[tblSEL]([Main_ID], [Date_Taken], [Time], [Water_ULevel])
Third question would be, how many rows will it return?
Last thing isn't a question... it's a suggestion... "today" is not over yet... change this...
t1.Date_Taken<=CONVERT(VARCHAR(10), GETDATE(), 101)
... to this...
t1.Date_Taken< CONVERT(VARCHAR(10), GETDATE(), 101)
... and, just so you know I'm not guessing at things (even though you didn't post much for help)...
--drop table tblSEL
--===== Create and populate a 6,000,000 row test table.
SELECT TOP 6000000
RowNum = IDENTITY(INT,1,1),
Date_Taken = CAST(FLOOR(RAND(CHECKSUM(NEWID()))*3653.0)+36524.0 AS DATETIME),
Time = ABS(CHECKSUM(NEWID()))%2400,
Main_ID = ABS(CHECKSUM(NEWID()))%3+230,
Water_ULevel = ABS(CHECKSUM(NEWID()))%5000+1000
INTO tblSEL
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE tblSEL
ADD CONSTRAINT PK_tblSEL_RowNum PRIMARY KEY CLUSTERED (RowNum)
--===== Create some locations...
--drop table tblLocation
SELECT ISNULL(230,0) AS Main_ID, 'PRK' AS Location
INTO tblLocation
UNION ALL
SELECT ISNULL(231,0) AS Main_ID, 'PER' AS Location UNION ALL
SELECT ISNULL(232,0) AS Main_ID, 'SAB' AS Location
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE tblLocation
ADD CONSTRAINT PK_tblLocation_Main_ID PRIMARY KEY CLUSTERED (Main_ID)
--===== Add a covering index for max performance on the query
CREATE INDEX [tblSEL2] ON [dbo].[tblSEL]([Main_ID], [Date_Taken], [Time], [Water_ULevel])
--===== Run the query (added Location to the mix)
SELECT t1.Date_Taken,
t1.Time,
t1.Main_ID,
t1.Water_ULevel,
t2.Location
FROM dbo.tblSEL t1
INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken >= CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101) AND
t1.Date_Taken < CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 2:33 pm
Jeff, there's a reference to the location table in the Where clause, "WHERE t2.Location='PRK'".
Sharul, the first thing I'd do for performance, is get rid of the conversion functions in the Where clause. Move those to variables before the select statement.
declare @FirstDate datetime, @LastDate datetime
select @FirstDate = CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101),
@LastDate = CONVERT(VARCHAR(10), GETDATE(), 101)
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID, t1.WATER_ULEVEL
FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken>=@FirstDate AND
t1.Date_Taken<=@LastDate
ORDER BY t1.Date_Taken, t1.Time
That will give you a better chance of using whatever indexes you might have present.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2008 at 7:46 pm
GSquared (5/12/2008)
Jeff, there's a reference to the location table in the Where clause, "WHERE t2.Location='PRK'".Sharul, the first thing I'd do for performance, is get rid of the conversion functions in the Where clause. Move those to variables before the select statement.
declare @FirstDate datetime, @LastDate datetime
select @FirstDate = CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101),
@LastDate = CONVERT(VARCHAR(10), GETDATE(), 101)
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID, t1.WATER_ULEVEL
FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken>=@FirstDate AND
t1.Date_Taken<=@LastDate
ORDER BY t1.Date_Taken, t1.Time
That will give you a better chance of using whatever indexes you might have present.
Missed the filter...thanks Gus.
There's no indexes on GETDATE() and the implicit conversion doesn't prevent an Index Seek in this case.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2008 at 11:31 am
Your right! (Of course.) I'm too used to functions in Where clauses giving index scans. Guess this is another of the exceptions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 1:23 pm
GSquared (5/12/2008)
declare @FirstDate datetime, @LastDate datetime
select @FirstDate = CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101),
@LastDate = CONVERT(VARCHAR(10), GETDATE(), 101)
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID, t1.WATER_ULEVEL
FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken>=@FirstDate AND
t1.Date_Taken<=@LastDate
ORDER BY t1.Date_Taken, t1.Time
Interestingly enough - if you check the execution plan and you compare it to the plan where the getdate() logic is left in - you'll find that they're EXACTLY the same. For better or worse, the compiler picks up that those two calcs are for these purposes scalar constant values, and calculates them once for the operation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 13, 2008 at 2:23 pm
Matt Miller (5/13/2008)
GSquared (5/12/2008)
declare @FirstDate datetime, @LastDate datetime
select @FirstDate = CONVERT(VARCHAR(10),DATEADD(m,-7,GETDATE()),101),
@LastDate = CONVERT(VARCHAR(10), GETDATE(), 101)
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID, t1.WATER_ULEVEL
FROM dbo.tblSEL t1 INNER JOIN dbo.tblLocation t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken>=@FirstDate AND
t1.Date_Taken<=@LastDate
ORDER BY t1.Date_Taken, t1.Time
Interestingly enough - if you check the execution plan and you compare it to the plan where the getdate() logic is left in - you'll find that they're EXACTLY the same. For better or worse, the compiler picks up that those two calcs are for these purposes scalar constant values, and calculates them once for the operation.
Yep. Jeff already pointed that out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2008 at 1:17 am
tq to all of you. 🙂
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply