Solved

SQL query times out. Is it too complex?

Posted on 2008-10-23
10
225 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

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)

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now