Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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