Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

SQL SERVER QUERY - with LEFT OUTER JOIN and WHERE CLAUSE

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
dteshome
Asked:
dteshome
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
cyberkiwiCommented:
>> "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
 
cyberkiwiCommented:
This will be able to utilize an index on a.statusdate - make sure you have one.
0
 
Ephraim WangoyaCommented:
...
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
jhp333Commented:
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
 
jhp333Commented:
cyberkiwi is right, in this case, you can simply use IN operator instead of the erroneous OR.
0
 
mustaccioCommented:
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
 
jhp333Commented:
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
 
cyberkiwiCommented:
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
 
Ephraim WangoyaCommented:
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
 
dteshomeAuthor Commented:
Just a general comment about your service;
Ingenious business model; a win-win-win (3 way) proposition
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now