Need help with query performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with query performance

Author

Topic

woiwodr
Starting Member

5 Posts

Posted - 2013-10-09 : 11:04:16

I am fairly new to TSQL and am having a hard time making this query perform well. Right now it is taking well over an hour to complete. The table for the date range provided does have well over 413k records so it is a larger table. But to me, this query seems pretty basic. Not sure why it would take so long to run.

Does anyone have any suggestions as to how I could rewrite the where section or anything else I could do to speed this up? I definitely appreciate any and all suggestions I can get.

SELECT *
FROM A
WHERE (((Right([PHONE_1],10))='1234567891') AND ((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013'))
OR (((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013') AND ((Right([PHONE_2],10))='1234567891'))
OR (((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013') AND ((Right([PHONE_3],10))='1234567891'))
OR (((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013') AND ((Right([PHONE_4],10))='1234567891'))
OR (((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013') AND ((Right([PHONE_5],10))='1234567891'))
OR (((A.[DATE_ADDED]) Between '10/9/2013' And '10/9/2013') AND ((Right([PHONE_6],10))='1234567891'))

ORDER BY A.DATE_ADDED;

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-09 : 11:33:18

You might try:


SELECT * 
FROM A 
WHERE 
	(Phone_1 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013') OR 
	(Phone_2 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013') OR 
	(Phone_3 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013') OR 
	(Phone_4 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013') OR 
	(Phone_5 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013') OR 
	(Phone_6 LIKE '%1234567891' AND Date_Added Between '10/9/2013' And '10/9/2013')

djj

woiwodr
Starting Member

5 Posts

Posted - 2013-10-09 : 11:40:54

DJJ, thanks for the quick reply. I am giving that a try now. My previous query took an just shy of two hours to run so I am definitely needing to speed this up somehow.

The table itself has over 95 million records. Would it be faster if I created a view to limit the records I am looking at? or would that take just as long?

Essentially the table houses a record of when someone is called. Each person could have up to 6 phone numbers hence Phone_1, Phone_2 etc. What I am trying to do with the query is find when the records of when the person was called during the date range provided. In this case I am just searching for 10/9 to 10/9.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-09 : 12:28:55

I think below is the best you can do with the query conditions.
Unfortunately, often poor table/column structures lead to poor query performance :-(.


WHERE (A.[DATE_ADDED] Between '20131009' And '20131009') 
    AND (RIGHT([PHONE_1], 10) = '1234567891'
      OR RIGHT([PHONE_2], 10) = '1234567891'
      OR RIGHT([PHONE_3], 10) = '1234567891'
      OR RIGHT([PHONE_4], 10) = '1234567891'
      OR RIGHT([PHONE_5], 10) = '1234567891'
      OR RIGHT([PHONE_6], 10) = '1234567891')

woiwodr
Starting Member

5 Posts

Posted - 2013-10-09 : 13:16:59

Scott,

Thanks for the suggestion. I tried both DJJ and your suggestions. Unfortunately DJJ's chnage really had no impact on the time it took to complete. And I had to stop yours to run some other queries but it was over 50 minutes and running.

What could I possibly do to improve the table/column structure? I am taking this over from someone else who left and am kind of left in the dark. I started looking at the indexing, but I dont have any experience with indexing other than setting primary keys in Access.

tm
Posting Yak Master

160 Posts

Posted - 2013-10-09 : 16:00:19

What about trying separate queries to see if it gets any quicker.
One question: Is the date range correct as you have the same date?

Anyway ..

-- 1. Get records that are in the date range into a temp table but use >= and > and see how long this takes

SELECT INDEX_COL -- Include index column from table for reference later
, CASE WHEN RIGHT(Phone_1, 10) = '1234567891' THEN 1 ELSE 0 END as PF_1
, CASE WHEN RIGHT(Phone_2, 10) = '1234567891' THEN 1 ELSE 0 END as PF_2
, CASE WHEN RIGHT(Phone_3, 10) = '1234567891' THEN 1 ELSE 0 END as PF_3
, CASE WHEN RIGHT(Phone_4, 10) = '1234567891' THEN 1 ELSE 0 END as PF_4
, CASE WHEN RIGHT(Phone_5, 10) = '1234567891' THEN 1 ELSE 0 END as PF_5
, CASE WHEN RIGHT(Phone_6, 10) = '1234567891' THEN 1 ELSE 0 END as PF_6
INTO #TempDateRange
FROM TABLE_A
WHERE DATE_ADDED >= '20131009' AND DATE_ADDED < '20131010' -- Note: less than 2013-10-10 to get one day for 2013-10-09

-- 2. Then get all phone numbers
SELECT TA.*
FROM #TempDateRange TEMP
INNER JOIN TABLE_A TA
ON TEMP.INDEX_COL = TA.INDEX_COL
WHERE (TEMP.PF_1 = 1 OR TEMP.PF2 = 1 OR TEMP.PF_3 = 1 OR TEMP.PF4 = 1 OR TEMP.PF_5 = 1 OR TEMP.PF6 = 1)

Hope this helps

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-09 : 16:51:26

It'd be faster, but I'm not sure of the other impacts to your system. But, you could create a computed column on each Phone Number columns defined as REVERSE(phone_#). Then you could index those columns and your predicate would become sargable. i.e.: WHERE Phone_1 LIKE '1987654321%'

But, if you are going to do that, then you might want to look at moving whatever is before the phone number to another column.

woiwodr
Starting Member

5 Posts

Posted - 2013-10-10 : 10:53:03

TM,

In this case the date range is correct. For testing purposes only, I was only wanting to search for one day. If I can find a way to get this query to run more efficiently, then the users will want to be able to put in actual date ranges.

I am going to give your suggestion a try here in a bit.

Lamprey,

Thanks for the suggestion, although I am not sure I quite follow. What does sargable mean?

On a side note, I am pretty positive I can get rid of the Right(PHONE_1,10)) or any use of % and use just PHONE_1 as I can use regular expressions to make sure the user has entered a 10 digit number with no other characters. That may help me speed it up a little?

Thanks again everyone

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-11 : 14:24:26

If you're going to add a computed column for searching, I'd just use RIGHT(Phone_1, 10) AS Phone_1_Last10, etc., rather than convoluting the computed column with REVERSE, etc..

woiwodr
Starting Member

5 Posts

Posted - 2013-10-14 : 11:15:18

Thanks everyone for their suggestions. I ended up taking a closer look at the table structure. After adding/changing indexes, I was able to get the query to complete in about a minute which is a heck of a lot better than close to 2 hours as it was before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources