Originally posted by: egerrits
The problem is this: it is extremelty s l o w, taking well over two minutes to get through just 2000 customers with a total of about 11000 invoices.
If this query executed once a day, it does not strike me that 2 min is too long since it is a batch job. If executed 1000 daily, that is a different story.
Take a look at the indexes. Are they up-to-date? Missing indexes?
Since you are using the isInactive flag, that probably forces table scan on the customer table.
Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/