T-SQL Jewel
-
I came across this ORDER BY clause in a T-SQL view when I was asked to fix performance issues (command timouts) on a legacy internal application:
ORDER BY dbo.dt_Downtime.PU, CONVERT(smalldatetime, CONVERT(varchar, dbo.dt_Downtime.StopTime, 101), 101), LEFT(CONVERT(varchar, dbo.dt_Downtime.StopTime, 114), 5)Then to add to the problem the WHERE clause of the query referencing the view looked like this after being concatenated together:and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)>='2/14/2008' and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)<='2/15/2008'Fixing these two lovlies brought the cost of the query plan down to 9 from 77 :doh:Mark's blog: developMENTALmadness.blogspot.com
-
I came across this ORDER BY clause in a T-SQL view when I was asked to fix performance issues (command timouts) on a legacy internal application:
ORDER BY dbo.dt_Downtime.PU, CONVERT(smalldatetime, CONVERT(varchar, dbo.dt_Downtime.StopTime, 101), 101), LEFT(CONVERT(varchar, dbo.dt_Downtime.StopTime, 114), 5)Then to add to the problem the WHERE clause of the query referencing the view looked like this after being concatenated together:and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)>='2/14/2008' and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)<='2/15/2008'Fixing these two lovlies brought the cost of the query plan down to 9 from 77 :doh:Mark's blog: developMENTALmadness.blogspot.com
X| I've had to fix that sort of thing as well. In one case the program had been taking forty minutes to run and after I rectified it (and added a bunch of new functionality) it took only ten minutes to run. The guy who had previously been maintaining it said, "It was like that when I got it, and if it ain't broke..." The craziest part was that the code was doing:
and CONVERT(varchar,CONVERT(varchar,StopTime,101))>='2/14/2008'
and CONVERT(varchar,CONVERT(varchar,StopTime,101))<='2/15/2008'(except in embedded RDB syntax, not T-SQL) When I asked why it was converting varchar to varchar the response was, "The guy who wrote it said it wouldn't work otherwise." :doh:
-
I came across this ORDER BY clause in a T-SQL view when I was asked to fix performance issues (command timouts) on a legacy internal application:
ORDER BY dbo.dt_Downtime.PU, CONVERT(smalldatetime, CONVERT(varchar, dbo.dt_Downtime.StopTime, 101), 101), LEFT(CONVERT(varchar, dbo.dt_Downtime.StopTime, 114), 5)Then to add to the problem the WHERE clause of the query referencing the view looked like this after being concatenated together:and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)>='2/14/2008' and CONVERT(smalldatetime,CONVERT(varchar,StopTime,101),101)<='2/15/2008'Fixing these two lovlies brought the cost of the query plan down to 9 from 77 :doh:Mark's blog: developMENTALmadness.blogspot.com
Any idea what the point was with that? I mean, I know it's bad, but there must have been a reason for it... I hope you replaced it with a stored proc with nice type-specific comparisons.
"Quality Software since 1983!"
http://www.smoothjazzy.com/ - see the "Programming" section for freeware tools and articles. -
Any idea what the point was with that? I mean, I know it's bad, but there must have been a reason for it... I hope you replaced it with a stored proc with nice type-specific comparisons.
"Quality Software since 1983!"
http://www.smoothjazzy.com/ - see the "Programming" section for freeware tools and articles.No body is around who ever had anything to do with writing the application. As far as I can tell from the code the idea was to sort by date then by time :doh: If they had any other intent then they didn't do that right either because that's all that code does (The order by anyway).
Mark's blog: developMENTALmadness.blogspot.com
-
No body is around who ever had anything to do with writing the application. As far as I can tell from the code the idea was to sort by date then by time :doh: If they had any other intent then they didn't do that right either because that's all that code does (The order by anyway).
Mark's blog: developMENTALmadness.blogspot.com
Mark J. Miller wrote:
No body is around
Well hidden I suspect.