Solved

SQL query times out. Is it too complex?

Posted on 2008-10-23
10
229 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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