?
Solved

Poorly performing SQL IN query

Posted on 2011-02-26
11
Medium Priority
?
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 2000 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 46

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 46

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 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