July 18, 2008 at 4:40 am
I want to know if the following query is correct with respect to performance, we are working on performance; I am not sure that following T-SQL may participate in de-grading performance or its OK?
select media_title from tbl_media m, tbl_Schedule s where media_category_id =34 AND '7/14/2008' BETWEEN s.start_date AND s.end_date
I need advice from experts. My own openion is, I must correct such kind of all queries. and definitely may grain raise in performance. the above query retruns 40K rows, wherein requirement might be only to find title (1 record).
My suggestion is:
select media_title from dbo.tbl_media m inner join dbo.tbl_Schedule s on m.media_ID = s.Media_ID
where m.media_category_id =34
AND '7/14/2007' BETWEEN s.start_date AND s.end_date
BTW, i have still have concernt about date range style used above. How it would be corrected if i have to see @DateTimeInput between Program Start and End Time?
-- I am also expecting replies from all experts
-- I hope Gail shaw will be happy to see a new thread
Shamshad Ali.
July 18, 2008 at 5:38 am
I'll move my comment from the other thread, to keep everything together.
What you have there is a cross join. With no join conditions between the tables, each row in the one table will match with each row in the other table. You get a full cartesian product, producing a very large number of rows.
You will definitly see an improvement in teh system performance if you eliminate the cross joins.
As for the variable or literal date, It doesn't make that much of a difference. The literal allows the optimiser to know more precisely how many rows wll be affected by the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply