?
Solved

SQL query times out. Is it too complex?

Posted on 2008-10-23
10
Medium Priority
?
233 Views
Last Modified: 2012-05-05
The following SQL query times out for some reason. It doesnt look too complex but perhaps the way it is structured means it cannot be used successfully.

Basically the query looks for EmailAddresses from one table, ensures they're not in another table and also that theyre not in the result of a subquery.

Doesnt sound too bad, but for some reason it times out. I know the constituent parts of the query (such as the sub query) work OK.

The COALESCE is in there because the EmailAddress fields may contain null values. The collate statement is there because the databases for some reason use a different collation setting.
SELECT     TempBroadcast.EmailAddress, TempBroadcast.FirstName, TempBroadcast.LastName, TempBroadcast.FirstName + ' ' + TempBroadcast.LastName AS FullName
FROM         TempBroadcast LEFT OUTER JOIN
                      EmailOptOut ON TempBroadcast.EmailAddress = EmailOptOut.EmailAddress collate SQL_Latin1_General_CP1_CI_AS 
WHERE     (EmailOptOut.EmailAddress IS NULL) AND TempBroadcast.EmailAddress NOT IN (
SELECT        COALESCE(XXX.dbo.Quotes.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress
FROM             XXX.dbo.Quotes
UNION
 SELECT        COALESCE(XXX.dbo.EssentialClicks.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress  
FROM             XXX.dbo.EssentialClicks
UNION
SELECT        COALESCE(XXX.dbo.Customers.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS AS EmailAddress
FROM             XXX.dbo.Customers)

Open in new window

0
Comment
Question by:g-spot
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Accepted Solution

by:
Blackninja2007 earned 1000 total points
ID: 22784206
Just a thought have you checked that you have indexes for email address on all tables ?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 22784242
Check the execution plan for the query, see how that looks. Do you have suitable indexes? Look for where the time is being spent in the execution plan.

You may also want to try using a NOT EXISTS clause instead of a NOT IN clause (see e.g. http://www.themssforum.com/SQLServerDev/EXISTS-INNER/) as I believe this could bre more efficient (possibly depends on your exact environment, indexes etc)
0
 

Author Comment

by:g-spot
ID: 22784287
Hi BN and ATL

I'm not sure what you mean by check the execution plan? I'm running SQL Server 2008 Express so I dont think I have access to that kind of information.

I'm fairly sure I dont have indexes set up on the email address columns so i will do that first.

Thanks.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:openshac
ID: 22784298
Have you checked the Execution Plan, this is the best way to check where the performance hit lies.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 22784314
I haven't used SQL Server 2008 Express, but I would have thought you have the option to see the execution plan. In SQL Server 2005 management studio, you'd write your query and then in the toolbar go to: Query->Include Actual Execution Plan

Then when you run the query, you'll get an extra tab alongside the results, containing a diagram of the execution plan. This will show you where the time is being spent. Things to look out for are: Table Scans (Bad) - if you don't think you have indexes, then you will have table scans which aren't efficient as it has to scan through every row in the table looking for a match. "Index Scans" are better, but generally speaking you want to be seeing "index seeks".

So, adding an index to (e.g.) email address in each table if you haven't already should make a good impact, as it will result in Index Scan/Seek instead of Table Scan
0
 

Author Comment

by:g-spot
ID: 22784487
OK, Thanks. I used the execution plan and ran a revised query that did not have any unions in the sub query (the sub query searched in just one table - "Quotes")

The vast majority of the time (76%) was spent on a Clustered Index Scan on the "Quotes" table (its about 200,000 records)

The only index on the Quotes table is a primary key based on an ID field. Should I index the "EmailAddress" column
0
 
LVL 23

Expert Comment

by:adathelad
ID: 22784514
Yes, index the EmailAddress column in TempBroadcast, EmailOptOut and Quotes tables. (Also in the other tables in the UNION when you add those back in).

High % is not necessarily bad - it's all relative to how much time the actual query took....i.e. 76% of 16ms is not a lot :)
0
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22784518
yes
0
 

Author Comment

by:g-spot
ID: 22785154
OK. All indexed.

So from 3+ minutes were down to 2 seconds.

Took a little while to work out to index the EmailAddress column in TempBroadcast as this table is created on-the-fly by importing a CSV file into SQL Server using ASP.net and the index is created using SQL Management Objects in ASP.net.

Thanks.
0
 

Author Comment

by:g-spot
ID: 22786583
Weird thing is... I changed the records in the ImportTemp table and it all got bogged down again.

However going with adathelad's suggestion of using the EXISTS clause sorted everything out:

The following code works perfectly every time:
SELECT     TempBroadcast.EmailAddress, TempBroadcast.FirstName, TempBroadcast.LastName, TempBroadcast.FirstName + ' ' + TempBroadcast.LastName AS FullName
FROM         TempBroadcast LEFT OUTER JOIN
                      EmailOptOut ON TempBroadcast.EmailAddress = EmailOptOut.EmailAddress collate SQL_Latin1_General_CP1_CI_AS 
WHERE     (EmailOptOut.EmailAddress IS NULL) AND NOT EXISTS (
SELECT        COALESCE(XXX.dbo.Quotes.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS
FROM             XXX.dbo.Quotes
WHERE TempBroadcast.EmailAddress = XXX.dbo.Quotes.EmailAddress
UNION
 SELECT        COALESCE(XXX.dbo.EssentialClicks.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS 
FROM             XXX.dbo.EssentialClicks
WHERE TempBroadcast.EmailAddress = XXX.dbo.EssentialClicks.EmailAddress
UNION
SELECT        COALESCE(XXX.dbo.Customers.EmailAddress, '') collate SQL_Latin1_General_CP1_CI_AS
FROM             XXX.dbo.Customers
WHERE TempBroadcast.EmailAddress = XXX.dbo.Customers.EmailAddress
)

Open in new window

0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 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