SQL Query select optimization

Question

i'm using ms sqlserver 2005. i have a query that need to filter according to date. lets say i have a table containing phone numbers and dates. i need to provide a count number of phone numbers in a time frame (begin date and end date). this phone numbers shouldn't be in the result count if they appear in the past. i'm doing something like this :

select (phoneNumber) from someTbl
where phoneNumber not in (select phoneNumber from someTbl where date<@startDate)

This is looking not efficient at all to me (and it is taking too much time to preform resulting with some side effects that maybe should be presented in a different question) i have about 300K rows in someTbl that should be checked.

after i'm doing this check i need to check one more thing. i have a past database that contains yet another 30K of phone numbers. so i'm adding

and phoneNumber not in (select pastPhoneNumber from somePastTbl)

and that really nail the coffin or the last straw that break the camel or what ever phrase you are using to explain fatal state.

So i'm looking for a better way to preform this 2 actions.

UPDATE i have choose to go with Alexander's solution and ended up with this kind of query :

SELECT t.number
FROM tbl t
WHERE t.Date > @startDate
--this is a filter for different customers
AND t.userId in (
                    SELECT UserId
                    FROM Customer INNER JOIN UserToCustomer ON Customer.customerId = UserToCustomer.CustomerId 
                    Where customerName = @customer
                )
--this is the filter for past number
AND NOT EXISTS (                          
                  SELECT 1
                  FROM pastTbl t2
                  WHERE t2.Numbers = t.number                            
                )
  -- this is the filter for checking if the number appeared in the table before  startdate               
AND NOT EXISTS (                          
                  SELECT *
                  FROM tbl t3
                  WHERE t3.Date<@startDate and t.number=t3.number
                )

Thanks Gilad

What part of the query takes most time in the query execution plan? Is indexes provided? How long time does the query take and what is an acceptable timeframe? — jpw
– jpw, Commented May 1, 2013 at 9:56

AbstractChaos · Accepted Answer · 2013-05-01 14:24:50Z

2

Since its a not in just switch the less than to a greater than.

select phoneNumber from someTbl where date > @startDate

Next to filter out somePastTbl

select s1.phoneNumber from someTbl s1
LEFT JOIN somePastTbl s2 on s1.phoneNumber = s2.phonenumber
where s1.date > @startDate and s2 IS NULL

UPDATE

As Per comment:

Less than month of start date

SELECT COUNT(s1.phoneNumber) FROM someTbl s1
LEFT JOIN somePastTbl s2 on s1.phoneNumber = s2.phonenumber
where DATEADD(MONTH,-1,@startDate) < s1.date AND s1.date < @startDate and s2 IS NULL

edited May 1, 2013 at 14:24

answered May 1, 2013 at 9:55

AbstractChaos

4,2111 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

gilad Over a year ago

hi. thabks for the quick response. i have used your second statement regarding the old data and it indeed improve the performance.

gilad Over a year ago

hi. thabks for the quick response. i have used your second statement regarding the old data and it indeed improve the performance. however , the first condition is not fulfilled. i need to check that a phone number present in the requested time frame (lets say +12127773456 in the last month) is not present also before (if i have +12127773456 before 2 months also i shouldn't count the occurrence of it in current count). hope i made my self clearer.thanks

AbstractChaos Over a year ago

@gilad So if i understand you need to know where it existed less than a month of the start date?

gilad Over a year ago

yes you are correct and from your question it led me to use another NOT EXISTS statement eliminating same numbers with older date. AND NOT EXISTS ( SELECT * FROM tbl t3 WHERE t3.Date<@startDate and t.number= t3.mobile _number) where t is the outer table

AbstractChaos Over a year ago

@gilad I have provided an update on the answer which should also give you what you need

|

Oleksandr Fedorenko · Accepted Answer · 2013-05-01 13:25:05Z

1

One more option

SELECT t.phoneNumber
FROM SomeTbl t
WHERE t.date > @startDate
  AND NOT EXISTS (                          
                  SELECT 1
                  FROM SomePastTbl t2
                  WHERE t2.phoneNumber = t.phoneNumber                            
                  )

answered May 1, 2013 at 13:25

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

1 Comment

gilad Over a year ago

Hi Alexander, thanks for the simple solution . i have adopted your approach for using NOT EXISTS in another filtering i needed to done. i will accept your answer though it is not complete but it is really copy paste to complete it with the different filter i needed. thanks

Jodrell · Accepted Answer · 2013-05-01 10:14:01Z

1

one simple index

CREATE NONCLUSTERED INDEX IX_SomeTbl_date_phoneNumber
    ON SomeTbl
(
    date ASC,
    phoneNumber ASC
)

then

SELECT phoneNumber FROM SomeTbl WHERE date > @startDate
EXCEPT
SELECT phoneNumber FROM SomePastTbl;

answered May 1, 2013 at 10:14

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

5 Comments

gilad Over a year ago

thanks for the reply.i have these indexes already on this table.

Jodrell Over a year ago

@gilad I specify only one covering index, is the statement fast and doing what you want?

gilad Over a year ago

the statement is fast when i'm using it plain simple. when i add some inner join to the first select to filter by userId it get stuck again.

Jodrell Over a year ago

@gilad I don't see that condition in the question.

gilad Over a year ago

yes you are correct . i thought the main issue was the iteration over the old phone numbers with every new phone number as i did so i didn't mention the other innerjoins. when i ran your code it was very fast but when i added two inner join to filter by userId too, it got stuck again.

Gordon Linoff · Accepted Answer · 2013-05-01 14:15:38Z

1

You want phone numbers whose minimum start date is greater than your start date. This suggests aggregation at the phone number level before doing the count (or creating the list).

Here is one way, with the condition in the having clause:

select COUNT(*)
from (select t.phonenumber,
      from someTble t left outer join
           somePastTble pt
           on t.phonenumber = pt.phonenumber
      where pt.phonenumber is null
      having MIN(date) >= @startdate 
     ) t

You can also write this using window functions (SQL 2005 or greater). Here is a version using min():

  select COUNT(distinct t.phonenumber)
  from (select t.*, t.phonenumber, MIN(date) over (partition by phonenumber) as mindate
        from someTble t
       ) t left outer join
       somePastTble pt
       on t.phonenumber = pt.phonenumber
  where pt.phonenumber is null and mindate >= @startdate

edited May 1, 2013 at 14:15

answered May 1, 2013 at 13:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

gilad Over a year ago

hi . thanks for the effort. in the first option (i will stick to it - enough with learning new stuff while learning new stuff..) there is no consideration whether the number is in db before @startdate and then it shouldn't be counted.

Collectives™ on Stack Overflow

SQL Query select optimization

4 Answers 4

6 Comments

1 Comment

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related