Solved

MS SQL syntax questiion

Posted on 2013-01-30
5
173 Views
Last Modified: 2013-02-19
I am curious what kind of response I would get on this and if anybody could tell me that one way is better than the other or are they the same.  The difference between the two snippets of code is the JOINS in the first code and the WHERE clause in the second code.  I am using si.Corporate = 1 on the top code in the JOIN and on the bottom code it is placed in the WHERE clause.  Any thoughts on this?  One better than the other are they both equal? etc.

*****  First Code JOINS  *****
Select td.StoreId
      ,fm.EndDate
      ,COUNT(1) as CertRecvd
      ,sum(soldprice) as Sales
from TLogDtl td with (nolock)
      Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
      Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId and si.Corporate = 1
      Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId and th.custNbr <> 0
where td.TransDate between '1/1/2012' and '12/29/2012'
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate      

*****  Second Code WHERE clause  *****
Select td.StoreId
      ,fm.EndDate
      ,COUNT(1) as CertRecvd
      ,sum(soldprice) as Sales
from TLogDtl td with (nolock)
      Join vFiscalMonth fm with (nolock) on td.TransDate between fm.BgnDate and fm.EndDate
      Join PSPCorp.dbo.StoreInfo si with (nolock) on td.StoreId = si.StoreId
      Join TLogHdr th with (nolock) on td.TLogHdrId = th.TLogHdrId
where td.TransDate between '1/1/2012' and '12/29/2012'
        and si.Corporate = 1
        and th.CustNbr <> 0
group by td.StoreId, fm.EndDate
order by td.StoreId, fm.EndDate      

Thanks for your thoughts on this.
0
Comment
Question by:j_heck
5 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 167 total points
Comment Utility
AFAIK, they should result in the same execution plan.  You can verify that you by running the query in SSMS with the "Include Execution Plan" option switched on.

However, I would put the filter in condition in the WHERE clause, not in the JOIN.  It just reads better and that's what they've invented the WHERE clause for.

Also, when specifying dates in queries it's better to use the following format: YYYYMMDD.  The format you've used depends on the server settings and thus could result in an error when running the query on a server with different settings.
0
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
I agree with ValentinoV. They return the same (or very similar) execution plans depending upon your database setup. But for ease in reading, use the second option.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
Comment Utility
I'd put it in both the WHERE and the JOIN: it won't hurt anything, and you give SQL as much info as possible to gen a plan.  Why try to guess which will help the optimizer more?!
0
 
LVL 10

Assisted Solution

by:deviprasadg
deviprasadg earned 166 total points
Comment Utility
Here we can check the performance of each query practically and use the one that has best performance.

How to check?
Paste the two queries in the same query window in SQL Server Management studio
Enable Actual Execution Plan and run the queries.

In the Execution results tab, you can find the one that has the lowest Query Cost %.

Use that query.

If both the queries share the same Query Cost% (50%,50%) then go with the one that has best readability.
0
 

Author Closing Comment

by:j_heck
Comment Utility
Thanks for all the information.  I have used the three suggestions with good success.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

17 Experts available now in Live!

Get 1:1 Help Now