long running 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)

long running query

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2010-02-16 : 17:21:42

Table TABLE1
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime )

Table TABLE2
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE1( COL1PK ) )

Table TABLE3
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE2( COL1PK ) )

Table TABLE4
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime,
update_date datetime REFERENCES TABLE2( COL1PK ) )

Table TABLE5
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE2( COL1PK ) )

Table TABLE6
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE2( COL1PK ) )

Table TABLE7
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE6( COL1PK ) )

Table TABLE8
( col1PK int primary key,
col2 varchar(10) ,
update_date datetime REFERENCES TABLE6( COL1PK ) )

TABLE1 is the parent table

TABLE2, TABLE3,TABLE4,TABLE5,TABLE6 REFERENCES TABLE1

TABLE7,TABLE8 REFERENCES TABLE6

I want to find
SELECT T1.COL2 , T2.COL2
FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.COL1PK=T2.COL1PK
WHERE T2.COL2='Some value'
AND

(
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR

(
T2.COL1PK IN ( SELECT T3.COL1PK FROM TABLE3 T3 where convert ( varchar,T3.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)
OR
.... and so on for all the tables mentioned above..

)

The query is to find out all the records of its child and grand child tables which is updated Today.

query is taking a lot of time. can you advise.

Byomjan....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-16 : 18:53:12

A few stray thoughts:
1) Can you store the dates with the time stripped off?
2) Instead of the convert(varchar, xxx, 112) it would be faster to use DatAdd(day, DateDiff(day, 0, XXX), 0)
3) A generic Calendar table might speed things up (broken record?)
4) What indexes are available on the tables?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

byomjan
Starting Member

34 Posts

Posted - 2010-02-16 : 19:03:24

1) i dont need the time. the query is to match the update_date with today's date. is there any other solution to match the dates.
2) i can use the datediffs. But major cause of issue is selecting records from large no of rows thatis updated today.
3) No calandar table available.
4)Indexes are available only on PKs on each table . Not on the Update_Date. Update_date will have lot of nonunique values.so dont consider it as a good candidate for index.Anyways that will be the last option.

Byomjan....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 00:08:06

quote:
Originally posted by byomjan

1) i dont need the time. the query is to match the update_date with today's date. is there any other solution to match the dates.
strip off time part as suggested by Bustaz
DATEADD(dd,DATEDIFF(dd,0,datetimefield),0)
2) i can use the datediffs. But major cause of issue is selecting records from large no of rows thatis updated today.
just do like:-
WHERE datetimefield>= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND datetimefield < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)

3) No calandar table available.

Create one for you reqd period using function below if you want
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

4)Indexes are available only on PKs on each table . Not on the Update_Date. Update_date will have lot of nonunique values.so dont consider it as a good candidate for index.Anyways that will be the last option.

Byomjan....

see responses inline above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byomjan
Starting Member

34 Posts

Posted - 2010-02-17 : 13:01:35

I dont understand how to use it
DATEADD(dd,DATEDIFF(dd,0,datetimefield),0) ?

Instead of
convert(varchar, update_date, 112)= convert(varchar, getdate(), 112)
I used
datediff( day,update_date,getdate())=0

What is "DATEADD(dd,DATEDIFF(dd,0,datetimefield),0)" used for and how ? there is nothing on right hand side ?

Also i wanted to know how to extract records from multiple table where thier respective update_date is equal to today. Even if i use calandar table , i want a replacement of below statement.

SELECT T1.COL2 , T2.COL2
FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.COL1PK=T2.COL1PK
WHERE T2.COL2='Some value'
AND

(
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR

(
T2.COL1PK IN ( SELECT T3.COL1PK FROM TABLE3 T3 where convert ( varchar,T3.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)
OR
.... and so on for all the tables mentioned above..

)

Byomjan....

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 13:19:44

DATEADD(dd,DATEDIFF(dd,0,datetimefield),0)

Converts "datetimefield" to a whole number of days - i.e. sets the TIME part to 00:00:00

This is FAR more efficient than using techniques like

convert(varchar, update_date, 112)= convert(varchar, getdate(), 112)

or

datediff( day,update_date,getdate())=0

Edit: P.S. although it is not more efficient in the isolated example at the top, it needs to be used in a way where the column itself is NOT included in ANY function - then that allows Query Optimiser to use an index for the date column. If a date column is included in a function - e.g. CONVERT(varchar, MyDateColumn, 112) or DATEDIFF(Day, MyDateColumn, GetDate())) - then NO index column for MyDateColumn will be used

byomjan
Starting Member

34 Posts

Posted - 2010-02-17 : 13:40:02

1) DATEADD(dd,DATEDIFF(dd,0,datetimefield),0) is an expression . I need to use it in where condition .How can I ?
2) what is the problem with datediff( day,update_date,getdate())=0 ?

Byomjan....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-17 : 13:44:24

The answers to both your questions are available in this very thread..you just have to read through it..

Answer to question 1 - From Visakh's Response...

quote:
WHERE update_date>= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND update_date < DATEADD(dd,DATEDIFF(dd,0,GETDATE())+1,0)

Answer to question 2 - From Kristen's very recent response..

quote:
P.S. although it is not more efficient in the isolated example at the top, it needs to be used in a way where the column itself is NOT included in ANY function - then that allows Query Optimiser to use an index for the date column. If a date column is included in a function - e.g. CONVERT(varchar, MyDateColumn, 112) or DATEDIFF(Day, MyDateColumn, GetDate())) - then NO index column for MyDateColumn will be used

byomjan
Starting Member

34 Posts

Posted - 2010-02-17 : 13:51:48

ok . Gotcha. By the way there are no indxes on Update_date.

But can anyone advise on the original query.Now i understood i can replace the update_date matching conditions. Still Lots of logical reads are happening while seleing update_date from multiple tables. Can we redesign the query anyhow?

SELECT T1.COL2 , T2.COL2
FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.COL1PK=T2.COL1PK
WHERE T2.COL2='Some value'
AND

(
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR

(
T2.COL1PK IN ( SELECT T3.COL1PK FROM TABLE3 T3 where convert ( varchar,T3.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)
OR
.... and so on for all the tables mentioned above..

)

Byomjan....

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 14:16:50

" By the way there are no indxes on Update_date"

Regardless of that:

1) It is good practice - because:
2) There may well be indexes added in the future - particularly if the performance is poor - or the performance becomes an issue.

I provide free help here to help people learn the best ways of using SQL Server. Your revised query is still full of VARCHAR conversions for date comparison, which we have explained are very inefficient.

If you can't be bothered to change your query to use the best-of-breed advice you have had here I'm not inclined to help you further.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:11:18

quote:
Originally posted by byomjan

ok . Gotcha. By the way there are no indxes on Update_date.

But can anyone advise on the original query.Now i understood i can replace the update_date matching conditions. Still Lots of logical reads are happening while seleing update_date from multiple tables. Can we redesign the query anyhow?

SELECT T1.COL2 , T2.COL2
FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.COL1PK=T2.COL1PK
WHERE T2.COL2='Some value'
AND

(
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR
convert ( varchar,T1.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)

OR

(
T2.COL1PK IN ( SELECT T3.COL1PK FROM TABLE3 T3 where convert ( varchar,T3.UPDATE_DATE , 112) =convert ( varchar,getdate() , 112)
OR
.... and so on for all the tables mentioned above..

)

Byomjan....

Adding to Kristen, You've same condition checked twice . can i ask reason for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources