Solved

Poorly performing SQL IN query

Posted on 2011-02-26
11
537 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
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!

 

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

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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