Convert function in join slows stored procedure

[lak201]

hi all,

i am having a problem because sql does not have a "date" variable and has a "datetime" variable instead.

i have 2 tables both with datetime fields, which should actually be just dates.. i have about 2-300,000 rows in each table.

i have to do an inner join on the dates being equal( not the time)... for this i use the convert

ON Convert(Char(10),date1,102)=Convert(Char(10),date2,102)

this convert slows down my stored procedure by a lot( by 60 times atleast)

on the other hand i can try to make sure that the dates i enter always are dates and not datetimes, but i cannot absolutely guarantee that....

is there some way of making this thing faster. ie

1) is there any alternative to using the datetime variable, just the date
2) if i have to use it can i put in some kind of restriction in the table itself to make sure that it is a date and not a datetime.
3)is there some way in which i can compare just the dates and not the datetimes without using the Convert fnuction.

thanks in advance

 

[dbomrrsm]

have you tried creating two new columns in ght table and update them with your convert - then use these columns to do the compare in the SP.

[blue]DBomrrsm[/blue]

 

[donutman]

Please test the follow method of doing the join. I'm very curious how it would perform against the other method.

[Blue]ON[/Blue] [Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]dd[Gray],[/Gray]date1[Gray],[/Gray]date2[Gray])[/Gray][Gray]=[/Gray]0

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I think it's an index issue. If you have the Enterprise version of SQL Server, then I think there might be a solution. Create a view with a computed column which is the converted date. Index that column. Then do your join.
If you only have the Stardard version, then I think the best solution is to put a trigger in each table that truncates the time using your Convert function. Then index on your date column.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]