Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL syntax questiion

Posted on 2013-01-30
5
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 668 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 70

Accepted Solution

by:
Scott Pletcher earned 668 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 664 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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