join problem.. subquery takes time...

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)

join problem.. subquery takes time...

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-11 : 07:41:41

Hi,
I have a table1 with foll. columns:


Col1   StartDate                 EndDate
1     2008-06-29 23:00:00.000  2008-07-09 11:00:00.000
2     2008-08-02 07:00:00.000  2008-08-03 17:00:00.000
3     2008-07-25 23:00:00.000  2008-07-28 11:00:00.000

values for both StartDateSK and EndDateSK are obtained from another table2 with the following columns 

DateSK          DateVal
20080707	2008-07-07 00:00:00.000
20080708	2008-07-08 00:00:00.000
20080709	2008-07-09 00:00:00.000
20080710	2008-07-10 00:00:00.000
20080711	2008-07-11 00:00:00.000
20080712	2008-07-12 00:00:00.000
20080713	2008-07-13 00:00:00.000
20081005	2008-10-05 00:00:00.000
20081006	2008-10-06 00:00:00.000
20081007	2008-10-07 00:00:00.000
20081008	2008-10-08 00:00:00.000
20081009	2008-10-09 00:00:00.000
20081010	2008-10-10 00:00:00.000
20081011	2008-10-11 00:00:00.000

i have shown only sample data, basically it contains each date of the year starting from 1900.

it is quite possible that ime wont match, hence i consider only date while comparing.
my query is as follows:

select
t2.DateSK as StartDateSK,
(select DateSK from table2 where convert(varchar,t1.EndDate,104) = convert(varchar,[date],104)) as EndDateSK,
t1.StartDate as StartDate,
t1.EndDate as EndDate
from table1 t1
inner join table2 t2 on convert(varchar,t1.StartDate, 104) = convert(varchar,t2.[date], 104)

Output required is


Col1 StartDateSK  EndDateSK    StartDate                 EndDate
1    20080629     20080709    2008-06-29 23:00:00.000  2008-07-09 11:00:00.000
2    20080802     20080803    2008-08-02 07:00:00.000  2008-08-03 17:00:00.000
3    20080725     20080728    2008-07-25 23:00:00.000  2008-07-28 11:00:00.000

the second stmt - i.e. subquery takes a lot of time for processing...
Plz. suggest me a way to improve this query
Thnx, Ziangi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 08:16:27

[code]select
t2.DateSK as StartDateSK,
t3.DateSK as EndDateSK,
t1.StartDate as StartDate,
t1.EndDate as EndDate
from table1 t1
inner join table2 t2 on convert(varchar,t1.StartDate, 104) = convert(varchar,t2.[date], 104)
inner join table2 t3 on convert(varchar,t1.EndDate, 104) = convert(varchar,t3.[date], 104)[/code]

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-11 : 09:14:08

The "converts" are killing you - You can't make use of any indices on table2. You shouldn't need "at worst" to do a convert on both sides of the "on" clauses.

By the way...you do have an index on table2 in the 1st place?

Also where are you getting the "time element" of both start/end dates from??... from the sample data provided it's not visible in the input data.

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-11 : 09:36:55

quote:
Originally posted by AndrewMurphy

The "converts" are killing you - You can't make use of any indices on table2. You shouldn't need "at worst" to do a convert on both sides of the "on" clauses.

By the way...you do have an index on table2 in the 1st place?

Also where are you getting the "time element" of both start/end dates from??... from the sample data provided it's not visible in the input data.

i do have an index - DateSK on table2. but unfortunately i need to get this index - DateSK.
if u see the records in table1, it has datetime values in StartDate and Enddate columns. Since time might not match with that of table2, i m doing a convert. Hence after conversion i get a date which i compare with date of table2 to get DateSK....

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-15 : 07:19:13

Does anybody have a better solution to this --
convert(varchar,t1.StartDate, 104) = convert(varchar,t2.[date], 104)

As i have already mentioned, based on the date values, i need to get the corresponding DateSK. reason for using a convert is i have values stored as DATETIME, i need to get only the date as TIME can vary by a second also...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 07:23:44

quote:
Originally posted by zion99

Does anybody have a better solution to this --
convert(varchar,t1.StartDate, 104) = convert(varchar,t2.[date], 104)

As i have already mentioned, based on the date values, i need to get the corresponding DateSK. reason for using a convert is i have values stored as DATETIME, i need to get only the date as TIME can vary by a second also...

dateadd(day,datediff(day,0,t1.StartDate),0) = dateadd(day,datediff(day,0,t2.[date]),0)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 07:34:32

then use DATEADD(d,DATEDIFF(d,0,datevalue),0) to strip time on both sides.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 07:35:10

arghh...didnt refresh before snding

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-15 : 07:37:33

thnx madhivanan

,
this reduced the time to half... it used to take around 9 minutes to process just 7000 records, now it takes around 4 minutes...
but even then, just for 7000 records, it is too long a time...

i was wondering; rather than using a join to get the DateSK, if i calculate the DateSK from table1 itself, that would definitely reduce the processing time... just wanted a suggestion, should i use substring to remove hyphen & get the DateSK??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 07:37:34

Why JOIN at all?

SELECT		Col1,
		CONVERT(CHAR(8), StartDate, 112) AS StartDateSK,
		CONVERT(CHAR(8), EndDate, 112) AS EndDateSK
		StartDate,
		EndDate
FROM		Table1
ORDER BY	Col1

E 12°55'05.25"
N 56°04'39.16"

zion99
Posting Yak Master

141 Posts

Posted - 2008-07-15 : 07:39:46

Thnx a ton to all

, specially to Peso.... u r absolutely great...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 07:49:09

How long do my suggestion take to run?

E 12°55'05.25"
N 56°04'39.16"