Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

Performance Tune Query Against DB of 1 million leads

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

  • 4
  • 3
3 Solutions
Chris LuttrellSenior Database ArchitectCommented:
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

davidcahanAuthor Commented:
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.

Chris LuttrellSenior Database ArchitectCommented:
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.
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

davidcahanAuthor Commented:
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.  

davidcahanAuthor Commented:
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?
Chris LuttrellSenior Database ArchitectCommented:
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.
davidcahanAuthor Commented:
yea...then i want a union actually not an intersect.  I want all the records not just hte ones in both sets.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now