Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

MS SQL syntax questiion

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
j_heck
Asked:
j_heck
3 Solutions
 
ValentinoVBI ConsultantCommented:
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
 
Jerry MillerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
deviprasadgCommented:
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
 
j_heckAuthor Commented:
Thanks for all the information.  I have used the three suggestions with good success.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now