Hi all,

I am using a SELECT COUNT query with several conditions in the WHERE clause. When I execute the query in the Dev environment, it completes in just a minute or less, but when I execute the same query in the Prod environment with same number of records, table structure and index, the query takes more than 1 hour to complete. What could possibly cause the slow execution in the Prod environment? Thank you.

Despite what you wrote, the first thing to be suspicious of is whether you indeed have all the same fields INDEXED or not. Almost always, something this much slower means the DB is having to do an extensive scan of the actual records instead of being able to work within the indexes.

You don't say what DB your are using. If it's a DB that allows you to view its "execution plan" (example: SQL Server), then do so on both machines and almost surely the differences will pop out at you.

Thanks for the reply old pedant..

My database is Sybase 15.0, regarding the indexes, I have checked the indexes of all tables used in the query and they are the same in dev and prod.

I have also tried using showplan and the results of the plan in the environment is also exactly the same. I still can't find what's causing the difference in execution time..