Solved

SQL SERVER QUERY - with LEFT OUTER JOIN and WHERE CLAUSE

Posted on 2010-08-16
10
455 Views
Last Modified: 2012-05-10
I would like to thank you for your help ahead of time.

I have the following original query which run for almost 2 minutes before it returned values; so, I modified it by moving the where clause to the bottom (please see the original and modified code below) at which time it run under a second!  

The problem, however, is that it returns "status" values that are different from the ones specified in the AND ... criteria (in addition to the correct ones).   I will appreciate an explanation of why moving the where clause transformed the execution time, and most importantly, why I am getting "status values that are outside of what I "asked" for.

The original and modified code is listed below (in that order):

original code:

SELECT DISTINCT TOP 251 a.city, a.property_type, a.mlsnum, a.status, mls.dbo.fn_sort_status(a.status)as sortOrder
FROM mls.dbo.mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls.dbo.mls_unified_mvo_svo_tbl m (nolock)
ON a.mlsnum= m.mlsnum
LEFT OUTER JOIN mls.dbo.photos_exist b (nolock)
ON a.mlsnum= b.mlsnum
LEFT OUTER JOIN mls.dbo.open_house_list d (nolock)
ON a.mlsnum = d.mlsnum
LEFT JOIN dbo.SCH_SaleType o
ON o.mlsNum = a.mlsnum
WHERE a.city LIKE'los angeles%'
AND a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365
OR a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365

modified code:

SELECT DISTINCT TOP 251 a.city, a.property_type, a.mlsnum, a.status, mls.dbo.fn_sort_status(a.status)as sortOrder
FROM mls.dbo.mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls.dbo.mls_unified_mvo_svo_tbl m (nolock)
ON a.mlsnum= m.mlsnum
LEFT OUTER JOIN mls.dbo.photos_exist b (nolock)
ON a.mlsnum= b.mlsnum
LEFT OUTER JOIN mls.dbo.open_house_list d (nolock)
ON a.mlsnum = d.mlsnum
LEFT JOIN dbo.SCH_SaleType o
ON o.mlsNum = a.mlsnum
AND a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365
OR a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365
WHERE a.city LIKE'los angeles%'
0
Comment
Question by:dteshome
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 125 total points
ID: 33450776
>> "status values that are outside of what I "asked" for.
Because it has become a left join condition. IF it does not match, all it does is NOT-JOIN to SCH_SaleType instead of REMOVING rows (which is the task of WHERE clause)

Write it as follows:

SELECT DISTINCT TOP 251 a.city, a.property_type, a.mlsnum, a.status, mls.dbo.fn_sort_status(a.status)as sortOrder
FROM mls.dbo.mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls.dbo.mls_unified_mvo_svo_tbl m (nolock)
ON a.mlsnum= m.mlsnum
LEFT OUTER JOIN mls.dbo.photos_exist b (nolock)
ON a.mlsnum= b.mlsnum
LEFT OUTER JOIN mls.dbo.open_house_list d (nolock)
ON a.mlsnum = d.mlsnum
LEFT JOIN dbo.SCH_SaleType o
ON o.mlsNum = a.mlsnum
WHERE a.city LIKE'los angeles%'
AND a.statusdate >= dateadd(day, -365, getdate())
AND a.status in (10,20)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33450779
This will be able to utilize an index on a.statusdate - make sure you have one.
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 50 total points
ID: 33450805
...
LEFT JOIN dbo.SCH_SaleType o
ON o.mlsNum = a.mlsnum
AND a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365
OR a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365
 
Basically thse could be intepreted as
LEFT JOIN dbo.SCH_SaleType o  ON (o.mlsNum = a.mlsnum
                                                            AND a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365
                                                           OR a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365 )

Try modifying it to the following and see if it makes a difference. Note the extra parenthesis i included in the where clause. Indexing the status and city fileds may also increase the speed if you have not done so yet

SELECT DISTINCT TOP 251 a.city, a.property_type, a.mlsnum, a.status, mls.dbo.fn_sort_status(a.status)as sortOrder
FROM mls.dbo.mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls.dbo.mls_unified_mvo_svo_tbl m (nolock)
ON a.mlsnum= m.mlsnum
LEFT OUTER JOIN mls.dbo.photos_exist b (nolock)
ON a.mlsnum= b.mlsnum
LEFT OUTER JOIN mls.dbo.open_house_list d (nolock)
ON a.mlsnum = d.mlsnum
LEFT JOIN dbo.SCH_SaleType o
ON o.mlsNum = a.mlsnum
WHERE a.city LIKE'los angeles%'
AND (a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365)
OR (a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365)

0
 
LVL 7

Assisted Solution

by:jhp333
jhp333 earned 75 total points
ID: 33450830
It seems the OR is misused.
Your original where condition will be understood by the server as:

WHERE
(a.city LIKE'los angeles%'AND a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365)
OR
(a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365)

So, the lower parts does not have any condition on city, it need to search nationwide. That's why it takes long.

The correct condition would be:
WHERE a.city LIKE 'los angeles%'
AND (
        a.status =(10) AND DATEDIFF(day, a.statusdate,getdate()) < 365
        OR
        a.status =(20) AND DATEDIFF(day, a.statusdate,getdate()) < 365
)

When you moved up some of the conditions, the OR part then used as a part of the join condition of the last LEFT (outer) JOIN, and since its "LEFT" outer join, any condition that applies only to the left table will be simply ignored by the server, because it does not affect the outer join at all.
0
 
LVL 7

Expert Comment

by:jhp333
ID: 33450842
cyberkiwi is right, in this case, you can simply use IN operator instead of the erroneous OR.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Expert Comment

by:mustaccio
ID: 33450855
Also, you may want to get rid of the DISTINCT, especially if the result set has more than 251 rows in it. DISTINCT causes a sort of (or, more precisely, elimination of duplicate rows from) the entire result set before returning 251 rows.
0
 
LVL 7

Expert Comment

by:jhp333
ID: 33450856
BTW, all your outer joins are unnecessary here, because the fields from those tables are not used anywhere in the SQL.
Unless you omitted part of the SELECT clause.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33450880
The worst performance culprit is to perform a function on a date column.
ALWAY, always, ALWAYS (can I repeat enough?) perform the function on the other side of the test, so that SQL server can take that [constant] value that it works out once, against an index on the date column.

DATEDIFF(day, a.statusdate,getdate()) < 365    ---- bad
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 33450907
Right cyberkiwi
I lways find it better to calculate the dates needed before hand and use some thing like
statusdata >= xxx and statusdata <= yyy
This way I make full use of my indexes
0
 

Author Closing Comment

by:dteshome
ID: 33451779
Just a general comment about your service;
Ingenious business model; a win-win-win (3 way) proposition
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now