Performance Tune Query Against DB of 1 million leads

Posted on 2009-04-17
Last Modified: 2012-05-06
I need help performance tuning the query below.  Obviously none of you have my DB but if anyone can look at it real quick and give me some direction that might help.  I have to search through my leads by a ZipCode Radius Search.  The zipcodes return from the function in less than a second so that isn't the slow down.  I've tried both "where in" statements and joining on zipcode.  Neither seems much faster.  The query is fairly fast, actually until I add the part where I say ZipCode in x and ZipCode not in y.  I need both of those though for more robust searching by my clients.

anyway, i'm pasting the code below.  Any help would be appreciated.
Select Count(b.IndividualID) as Cnt



	from	dbo.IndividualOrigins a with(nolock) join

			dbo.Individuals b with(nolock) on a.IndividualID = b.IndividualID join

			--(Select ZipCode from dbo.ZipCodeRadiusSearch ('23692','180')) zi on b.ZipCode = zi.ZipCode join

			dbo.Origins d with(nolock) on a.OriginID = d.OriginID join

			dbo.Venues y with(nolock) on d.VenueID = y.VenueID join

			(select IndividualID from Individuals with(nolock) where cycle = '1') qq on b.IndividualID = qq.IndividualID 


	b.DNC = 0 and 

	b.Dupe = 0 and 

	b.IndividualID not in


				Select a.IndividualID


				(Select max(IndividualExportsMasterID) as CampaignID,IndividualID

				from IndividualExportsDetail with(nolock)

				group by IndividualID) a 


				IndividualExportsDetail b with(nolock) on a.CampaignID  = b.IndividualExportsMasterID and a.IndividualID = b.IndividualID

				and datediff(day,b.DateExpiration,getdate()) < 0

				) and b.IndividualCategoryID  = '1' 

				and b.ZipCode in (Select ZipCode from dbo.ZipCodeRadiusSearch ('23692','180')) and b.ZipCode not in (Select ZipCode from dbo.ZipCodeRadiusSearch ('23692','60'))

Open in new window

Question by:davidcahan
    LVL 26

    Accepted Solution

    try replacing the last line with this to use set based logic (which db is good at) instead of the not in.  It also lessens the values b.ZipCode has to be compared to.

    and b.ZipCode in (Select ZipCode from dbo.ZipCodeRadiusSearch ('23692','180') EXCEPT Select ZipCode from dbo.ZipCodeRadiusSearch ('23692','60'))

    Open in new window


    Author Comment

    actually...i was wrong about joining back to the main table on zipcode.  I added a join to the zipcodes it should be in and a left join to the ones it shouldn't.  then i look for NULL on the right side and it works.  Got it down to 7 seconds over 1 million records.

    i'll try your way just to see if it speeds it up even more.

    LVL 26

    Assisted Solution

    by:Chris Luttrell
    I was also looking deeper at the upper part of the query and you are basically doing a Select Id from one set Except Select Id from another set.  I wonder if you could structure it with that pattern something like this:
    Select Count(IndividualID) as Cnt
    from (
    Select IndividualID
    From "Top Part"
    Select IndividualID
    From "Lower Part"
    ) x -- it may complain if you don't give an alias here

    I have had good success with using Except instead of NOT IN when it is simple enough to do so and I think this is.

    Author Comment

    let me try that out.  this query is the heart and soul of our application as it is solely responsible for determining how and when leads get distributed out of the system.  anything I can do to increase the performance, the better.  And as you have noticed i do have a bunch of in and not in statements.  


    Author Comment

    yea...that works real well.  Knocked down my time from 7 seconds to 3 seconds using the same query parameters.  I also added an intersect in as well.  Is interesect faster than union?
    LVL 26

    Assisted Solution

    by:Chris Luttrell
    Intersect gives distinct values that are in BOTH sets, where union gives distinct values in EITHER set. So be careful, they are not the same operation.

    Author Comment

    yea...then i want a union actually not an intersect.  I want all the records not just hte ones in both sets.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    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.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now