Solved

MS SQL syntax questiion

Posted on 2013-01-30
5
177 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
ID: 38834970
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
ID: 38835254
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:
Scott Pletcher earned 167 total points
ID: 38835867
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
ID: 38838228
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
ID: 38905131
Thanks for all the information.  I have used the three suggestions with good success.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 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