0

I have this query on my dev server:

select
    snStudyNumber as ProjectNumber
from CDB.cdb.Quotes q
    inner join CDB.cdb.LineItems l
        on q.PK_quID=l.FK_quID
    inner join CDB.cdb.StudyNumbers s
        on l.FK_snID=s.PK_snId
    left outer join ardb.projects p
        on s.snStudyNumber=p.prProjectNumber
    left outer join b
        on s.snStudyNumber=b.studynumber
where q.quDateWon>''
    and s.snPTWCompletionDate >''
    and p.PK_prID is null
    and l.liCreationDate>'12/31/06'
    and b.studynumber is null
union all

select CAST(ProjectNumber AS int)
from history.ProjectsToProcess a
    left outer join ardb.projects p
        on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc

This used to work ok on the dev server until yesterday. Now it's taking about 1 1/2 minutes to run. However, it works fine on the test server.

Here's the weird part. If i modified the query on dev to take out one of the left outer joins (either one), it would run fine (but the wrong dataset). If i added the join back it slowed down again.

Also, the data between the servers are within 90% similarity. Structure and amount wise.

I also tried rebooting the dev server, but that didn't fix the issue.

So, what could cause this discrepancy? Where should I be looking?

edit- ok, so i missed something in the execution plans. The test server (the one that works) is on the left, the dev server on the right. Difference 1 Difference 2

1

Sounds like your statistics are out of date. Run UPDATE STATISTICS on the tables on the server running slow and see if that fixes the problem.

  • Heh, i had just thought of this, sorta. Rebuilt all of the indexes on the ARDB database, no dice. Saw this answer, ran update statics one table at a time, got to cdb.StudyNumbers and voila! It's working now. Thanks Denny! – DForck42 Sep 9 '11 at 20:45
  • No problem. It's usually something nice and simple that does the trick. – mrdenny Sep 9 '11 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.