Solved

SQL query times out. Is it too complex?

Posted on 2008-10-23
10
228 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Accepted Solution

by:
Blackninja2007 earned 250 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What's wrong with this T-SQL Foreign Key? 7 51
Master DB with Masterkey 1 35
Microsoft Access Delete all Records from table but Max 2 24
SQL Syntax 14 35
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

820 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