x

Can the way this statement is written speed the query up or should I look at table design?

I have been given the following slow performing query which is taking a cut from 1.2 million rows to return about 51,000. It is taking about 56 seconds which is slow for a webpage.

SELECT *    
FROM vwPPEKBaseQuery, vwWeeklyRanges2Only
WHERE OM_Available BETWEEN WeekStart AND WeekEnd

Can I write the JOIN in a different way to speed things up or should I be looking at indexes and so on for the tables? Also I am surprised that sort of join still exists and still runs on SQL Server!

more ▼

asked Feb 24, 2010 at 10:15 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As long as it's an inner join it will work in 2008. The ANSI 89 outer join syntax is deprecated. Based on the tags you're running in this in '05, so it'll work.

As to whether or not changing the syntax will speed it up, it's hard to know without seeing the execution plans for the query. But, in general, I shy away from joining views to views where I can. SQL Server can do a very good job of resolving the view for a given query, but, depending on the view and the query, it might not bother to resolve the view at all and will merely run it as written, whether or not you're referencing all the tables & columns in the view or not. Clearly, that can lead to huge performance problems.

more ▼

answered Feb 24, 2010 at 10:29 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

+1 Marked as the answer because of course I should carry on diving through the views to get at the base query. Thanks for the information on joins it would appear that it works akin to a cross join in nature.
Feb 24, 2010 at 10:31 AM Ian Roke
(comments are locked)
10|1200 characters needed characters left

Just out of interest here is the query plan. I have managed to get it down to 17 seconds now.

Query plan

more ▼

answered Feb 24, 2010 at 10:57 AM

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

Clearly, you want to find a way around that clustered scan.
Feb 24, 2010 at 11:38 AM Grant Fritchey ♦♦
That's what I thought. Thanks for the confirmation Grant.
Feb 24, 2010 at 12:01 PM Ian Roke
(comments are locked)
10|1200 characters needed characters left

Ian... I see no join criteria between the two tables. What you have there is a full blown cross-join. There has to be something that both tables can relate to so you can join them without them being a cross-join.

more ▼

answered Feb 25, 2010 at 04:18 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 3 4 8

Agreed. The reason is because I am trying to select records where the OM_Available date is between two dates in the vwWeeklyRanges2Only table. I suppose it would be much quicker if I used a subquery instead? I think I might try that for comparison.
Feb 25, 2010 at 04:52 AM Ian Roke

Subquery or not, unless you have something to join on between the two views, you have a time consuming cross join.

I also agree with Grant... joing two views together isn't usually a form of computational Nirana.
Mar 07, 2010 at 12:38 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x986
x117

asked: Feb 24, 2010 at 10:15 AM

Seen: 1382 times

Last Updated: Feb 24, 2010 at 10:15 AM