?
Solved

How do I write this SQL so that it execute faster?

Posted on 2011-05-09
8
Medium Priority
?
244 Views
Last Modified: 2012-05-11
Is there a way to write this SQL differently so that it execute faster.
I would like to do it without adding index.


SELECT DISTINCT Customers.CustomerID
FROM            Customers LEFT OUTER JOIN
                         aspnet_Membership ON Customers.NetUserID = aspnet_Membership.UserId LEFT OUTER JOIN
                         Wishlists ON Customers.CustomerID = Wishlists.CustomerID LEFT OUTER JOIN
                         blog ON Customers.CustomerID = blog.CustomerID
WHERE        (Wishlists.Comments <> N'') AND (aspnet_Membership.CreateDate >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
GROUP BY Customers.CustomerID
HAVING        (COUNT(blog.Comment) = 0)





Execution plan:
-   Hash Match (Affrefate) Cost: 57%
-  Clusted Index Scan (clustered) [blog].[PK_blog_1] Cost: 24%

I use SQL Server 2008
Thank you
0
Comment
Question by:jtuttle99
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35724679

You have LEFT JOIN with aspnet_Membership and Wishlists but there are filter conditions on these tables in WHERE clause. That results in INNER JOIN. Do you want LEFT JOIN or INNER JOIN?
The DISTINCT in SELECT clause is not required as you are grouping on same column.
As you will get a static value for DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0)), you can declare a variable and assign this value and use that variable in the query.
Can you explain what are you trying to achive with your query.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35724747
I agree with Sharath.  As for how to make this faster, you can see how the EXECUTION PLAN improves using this:

SELECT Customers.CustomerID
FROM Customers
JOIN aspnet_Membership ON Customers.NetUserID = aspnet_Membership.UserId
JOIN Wishlists ON Customers.CustomerID = Wishlists.CustomerID
WHERE (Wishlists.Comments <> N'') AND (aspnet_Membership.CreateDate >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
AND NOT EXISTS (
   SELECT 1
   FROM blog
   WHERE Customers.CustomerID = blog.CustomerID
);
0
 

Author Comment

by:jtuttle99
ID: 35724864
Hello, experts!
Thank you for your help.

Basically, I need to get customerID (possibly other filed info in customer table in the future) under these condition.

1. It has more than one comment field (not blank) in wishlist table ( Wishlists.Comments <> N'')
2, It doesn't have any record in blog table  (COUNT(blog.Comment) = 0)
3, within last 7 days (aspnet_Membership.CreateDate >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))

I just put together these conditions and come up with SQL syntax...

The reason that I chose LEFT OUTER JOIN was that blog or wishlist table don't have records sometimes.
I didn't know that if I include condition in WHERE clause results in INNER JOIN..
Please let me know if you have any suggestions.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:jtuttle99
ID: 35724897
oh, with in 7 days part has been changed to


 (aspnet_Membership.CreateDate >= DATEADD(day, - 7, GETDATE()))

I didn't update in the last post
0
 

Author Comment

by:jtuttle99
ID: 35724992
mwvisa1:
Here is the screen shot of your SQL syntax .


002.JPG
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35725044
1)What is the execution time of mwvisa's query and what is your expected exewcution time?
2) What indexes you have on those tables?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35725615
It looks like you are missing some indexes ... the execution plan has *all* index scans.
Based on your criteria, I would also do a derived table on the Wishlist piece since you need where it is more than 1 row.

SELECT Customers.CustomerID
FROM Customers
JOIN aspnet_Membership ON Customers.NetUserID = aspnet_Membership.UserId
JOIN (
   SELECT CustomerID, COUNT(*) AS CommentCount
   FROM Wishlists
   WHERE (Wishlists.Comments <> N'')
   GROUP BY CustomerID
   HAVING COUNT(*) > 1
) Wishlists ON Customers.CustomerID = Wishlists.CustomerID
WHERE (aspnet_Membership.CreateDate >= DATEDIFF(Day, 0, GETDATE() - 7))
AND NOT EXISTS (
   SELECT 1
   FROM blog
   WHERE Customers.CustomerID = blog.CustomerID
);

Run this and see if better.  Probably still need to address some index issues, but may help some.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35728082
SELECT DISTINCT Customers.CustomerID, COUNT(blog.Comment)
FROM            Customers LEFT OUTER JOIN
                         aspnet_Membership ON Customers.NetUserID = aspnet_Membership.UserId LEFT OUTER JOIN
                         Wishlists ON Customers.CustomerID = Wishlists.CustomerID LEFT OUTER JOIN
                         blog ON Customers.CustomerID = blog.CustomerID
WHERE        (Wishlists.Comments <> N'') AND (aspnet_Membership.CreateDate >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
GROUP BY Customers.CustomerID
HAVING        (COUNT(blog.Comment) = 0)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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