CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008t-sql
AlanD
AlanD

Reputation: 5

SQL query running very slow

I am trying to run a query in SQL Server 2008 and it's running very slowly when I change the value of one of the variables (SourceID). The below code works fine when the SourceID is set to another available ID but on the one in the code it just hangs... for hours if I let it!

The email, dupe and MyMystery columns are all indexed... any thoughts?

WITH rmdup as (
   SELECT act.Email 
        , act.FirstName
        , act.LastName
        , act.SourceID SID
        , ac.ID CID
        , ROW_NUMBER() OVER (PARTITION BY act.Email ORDER BY  act.Email DESC,act.dateadded DESC) RN 
     from a_customer_test act 
    inner join 
          a_customer ac 
       on act.Email = ac.email 
      and act.sourceID = ac.sourceID
    where act.sourceID in (409) 
      and dupe = 0 
      and mymystrey = 0 
      and act.Email not in (select cemail as email 
                              from a_unsub 
                             union 
                            select email as email 
                              from a_unsubscribe)
)
select REPLACE(Email, ',', '.') as Email
     , FirstName
     , LastName
     , SID
     , CID 
  from rmdup
 where RN=1 
 ORDER BY 
       Email DESC

By the way, I can't run the "Display Estimated Execution Plan" as I don't have permissions and get the following error... story of my life!!

Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database

Upvotes: 0

Views: 381

Answers (3)

William Treadway
William Treadway

Reputation: 35

The best thing to do would be to removed the where clause from the CTE and place it on the ON clause (Only do this with INNER JOINs not LEFT JOIN). The reason is when a Join is made, the conditions on the ON CLAUSE are made at the time of the join and eliminates a lot of the useless data while the data is being put on disk or in memory. When the conditions are on the WHERE Clause the conditions are eliminated AFTER all the data has been writing to disk or memory. The Where clause makes more work.

I would also change the NOT IN clause into another CTE and try to find a way to use something other then NOT IN. NOT IN is not as inclusive as IN. The comparison for NOT IN is not as natural to SQL as IN.

 WITH rmdup as (
    SELECT act.Email 
        , act.FirstName
        , act.LastName
        , act.SourceID SID
        , ac.ID CID
        , ROW_NUMBER() 
     OVER (PARTITION BY act.Email ORDER BY  act.Email DESC,act.dateadded  DESC) RN 
     from a_customer_test act 
    inner join 
          a_customer ac 
       on act.Email = ac.email 
      AND act.sourceID = ac.sourceID
      AND act.sourceID in (409) 
      and dupe = 0 
      and mymystrey = 0 
      and act.Email not in (select cemail as email 
                              from a_unsub 
                             union 
                            select email as email 
                              from a_unsubscribe)
    )
    select REPLACE(Email, ',', '.') as Email
     , FirstName
     , LastName
     , SID
     , CID 
     from rmdup
     where RN=1 
     ORDER BY 
       Email DESC

Upvotes: 0

Liath
Liath

Reputation: 10191

I'll attempt an answer, in addition to the suggestion about updating the statistics I'd try adding an index to both a_customer_test a_customer which includes Email and SourceID.

I'd also check that indicies are in a resonable condition (fill factor and fragmentation).

Upvotes: 0

bjnr
bjnr

Reputation: 3437

I suspect statistics for a_customer_test table are not up-to-date. Execute this in order to update them:

UPDATE STATISTICS a_customer_test;

Upvotes: 4

Related Questions