CODEWITHSUNDEEP
sqlsql-server
gilad
gilad

Reputation: 297

SQL Query select optimization

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

Upvotes: 2

Views: 166

Answers (4)

AbstractChaos
AbstractChaos

Reputation: 4211

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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                            
                  )

Upvotes: 1

Jodrell
Jodrell

Reputation: 35696

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;

Upvotes: 1

Related Questions