Solved

Poorly performing SQL IN query

Posted on 2011-02-26
11
509 Views
Last Modified: 2012-05-11
I have a SQL query that is performing quite poorly. Taking well over 60 minutes to run. Below is the raw query as well as certain facts I have gathers.

Query:
select left(PhoneNumber, 3) as NPA, substring(PhoneNumber, 4, 3) as NXX from vwPhoneNumbers_All
where len(PhoneNumber) = 10
and left(PhoneNumber, 3) = '614'  
and left(PhoneNumber, 3) + substring(PhoneNumber, 4, 3) not in (select NPA_Orig + NXX_Orig from LocalCallingConfig group by NPA_Orig + NXX_Orig)
group by left(PhoneNumber, 3), substring(PhoneNumber, 4, 3)

Fact1:
The vwPhoneNumbers_All  only has 1,200 rows.

Fact2:
The outer query is only 74 rows.
 select left(PhoneNumber, 3) as NPA, substring(PhoneNumber, 4, 3) as NXX from vwPhoneNumbers_All where len(PhoneNumber) = 10 and left(PhoneNumber, 3) = '614'  group by left(PhoneNumber, 3), substring(PhoneNumber, 4, 3)

Fact3:
LocalCallingConfig table has 4,600,000 rows.

Fact4:
The IN query has only 33 rows returned. select NPA_Orig + NXX_Orig from LocalCallingConfig group by NPA_Orig + NXX_Orig

I do recognize that IN statements can be poor for performance but it is a requirement to exclude that recordset from the returned results.

I do recognized that many of my comparisons are against derived values. Example:left(PhoneNumber, 3) + substring(PhoneNumber, 4, 3)

I'm looking for tips to help this query runs fast.

Thanks,
Dean
0
Comment
Question by:dthansen
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 3

Expert Comment

by:sergiobg57
Comment Utility
Well, i'm not a great sql peer because most of the times i only develop drivers and firmwares.
But in your "IN" statement there's a "group by" which will recursively organizing all the results for the comparison.
select NPA_Orig + NXX_Orig from LocalCallingConfig group by NPA_Orig + NXX_Orig

Open in new window



It's obvious that this will have some performance impact.
Having it inside an "IN" statement sounds a bit unnecessary to me.
Is it really necessary as it's not going to be shown to you?(as far as i know)
0
 

Author Comment

by:dthansen
Comment Utility
Without the 'group by' in that query, the resultset of the IN statement is 4,600,000 rows. Using the group by inside that statement drops it to 33 rows. I did this as I expected the entire query to run faster if the IN query only returned 33 rows versus the oriignal 4,600,000.

I had previously tried the same thing you recommended and runtime was still over an hour.

Thanks,
Dean
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Can you check this?
SELECT DISTINCT LEFT(PhoneNumber,3)        AS NPA, 
                SUBSTRING(PhoneNumber,4,3) AS NXX 
  FROM vwPhoneNumbers_All 
 WHERE LEN(PhoneNumber) = 10 
       AND PhoneNumber LIKE '614%' 
       AND NOT EXISTS (SELECT 1 
                         FROM LocalCallingConfig 
                        WHERE SUBSTRING(PhoneNumber,1,7) <> NPA_Orig + NXX_Orig)

Open in new window

0
 

Author Comment

by:dthansen
Comment Utility
The NOT EXISTS() will not return the same recordset that I need to exclude.

Thanks,
Dean
0
 
LVL 3

Expert Comment

by:sergiobg57
Comment Utility
Then there's the possibility that any change in your query would do little difference.

Each record in vwPhoneNumbers_All  will infer in more 4,600,000 iterations.(it's not the exact value as it tends to be higher because the evaluation uses strings and each 8 char comparison is 1 CPU cycle in x86 processors)

What you can do to make things faster is redesigning your software's structure to show results partially using the SQL limit statement.
But if the results go directly to a file or something like that, this approach cannot be used.

Anyway, i'll try to redesign your sql query to see if i can get a better performance.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 7

Expert Comment

by:rmm2001
Comment Utility
See if this works any better:

 
SELECT LEFT(pn.PhoneNumber, 3) as NPA, 
       SUBSTRING(pn.PhoneNumber, 4, 3) as NXX 
FROM vwPhoneNumbers_All pn
LEFT JOIN LocalCallingConfig lcc on lcc.NPA_Orig + lcc.NXX_Orig
WHERE LEN(PhoneNumber) = 10
  AND LEFT(PhoneNumber, 3) = '614'  
  AND lcc.NPA_Orig IS NULL
GROUP BY LEFT(pn.PhoneNumber, 3), SUBSTRING(pn.PhoneNumber, 4, 3)

Open in new window

0
 
LVL 51

Accepted Solution

by:
tedbilly earned 500 total points
Comment Utility
OK, as soon as you add a NOT IN or NOT EXISTS you are forced to do a full table/index scan which is as slow as it gets.

Well there is a couple of considerations here.  How often doest the LocalCallingConfig table change with updates or inserts?  If rarely I'd create a table to hold the results from 'SELECT DISTINCT NPA_Orig + NXX_Orig FROM LocalCallingConfig' and then update the table whenever those two columns in LocalCallingConfig changes.

Then run your query against the new table!

Otherwise, have you considered an index with columns NPA_Orig and NXX_Orig on the LocalCallingConfig table to speed up creating the distinct data set?

If this statement is running in a stored procedure I wonder if the results from 'SELECT DISTINCT NPA_Orig + NXX_Orig FROM LocalCallingConfig' could be stored in a temporary table in the stored procedure for better results?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@dthansen

>>NOT EXISTS() will not return the same recordset that I need to exclude

I think I see the problem.  Try the following:

SELECT DISTINCT LEFT(PhoneNumber,3)        AS NPA, 
                SUBSTRING(PhoneNumber,4,3) AS NXX 
  FROM vwPhoneNumbers_All 
 WHERE LEN(PhoneNumber) = 10 
       AND PhoneNumber LIKE '614%' 
       AND NOT EXISTS (SELECT 1 
                         FROM LocalCallingConfig 
                        WHERE SUBSTRING(PhoneNumber,1,7) = (NPA_Orig + NXX_Orig) )

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You will want to work on the indexes on the LocalCallingConfig table to get it to perform well.
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
if table LocalCallingConfig have index on field NPA_Orig

this may help thing up a bit
SELECT
	DISTINCT LEFT(PhoneNumber, 3) AS NPA,
	SUBSTRING(PhoneNumber,4,3) AS NXX
FROM
	vwPhoneNumbers_All
WHERE
	LEN(PhoneNumber) = 10 AND LEFT(PhoneNumber, 3) = '614'
	AND NOT EXISTS (
		SELECT 1
		FROM LocalCallingConfig
		WHERE NPA_Orig = '614' AND SUBSTRING(PhoneNumber,4,3) = NXX_Orig
	) 
 

Open in new window

Note : comment 34990172 from aikimark
change SUBSTRING(PhoneNumber,1,7) to be SUBSTRING(PhoneNumber,1,6) also working
0
 

Expert Comment

by:MartijnKok
Comment Utility
The following T-SQL statement might work if you are working with SQL 2008 (not sure about 2005).

WITH localCalling
AS 
(
	SELECT DISTINCT NPA_Orig, NXX_Orig from LocalCallingConfig
), 
PhoneAll
AS
(
	SELECT DISTINCT left(PhoneNumber, 3) as NPA, substring(PhoneNumber, 4, 3) as NXX 
	FROM vwPhoneNumbers_All 
	WHERE len(PhoneNumber) = 10 
		and left(PhoneNumber, 3) = '614' 
)
SELECT pa.NPA, pa.NXX, lc.*
FROM PhoneAll pa LEFT JOIN localCalling lc ON pa.NPA = lc.NPA_Orig AND pa.NXX = lc.NXX_Orig
WHERE lc.NPA_Orig is null

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

7 Experts available now in Live!

Get 1:1 Help Now