Solved

Poorly performing SQL IN query

Posted on 2011-02-26
11
525 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
ID: 34988598
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
ID: 34988631
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
ID: 34988650
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Thanks,
Dean
0
 
LVL 3

Expert Comment

by:sergiobg57
ID: 34988678
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
 
LVL 7

Expert Comment

by:rmm2001
ID: 34989648
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:
Ted Bouskill earned 500 total points
ID: 34990160
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
ID: 34990172
@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
ID: 34990175
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
ID: 34990302
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
ID: 34991795
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
MS SQL + Insert Into Table - If Doesnt Exist 9 32
insert wont work in SQL 14 19
CPU high usage when update statistics 2 28
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…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

786 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