Slow Query

From: Vikram Dayanidhi <vikran_at_bay.uh.edu>
Date: Thu, 29 Nov 2001 11:12:06 -0600
Message-ID: <Pine.OSF.4.33.0111291111210.1364919-100000@bay.uh.edu>

I have a problem with a query thats been driving me crazy for the past few weeks. I hope someone can help me.

We have 3 production databases - A, B, C. Database A supports a web-site, while the other 2 databases are for internal uses. Data from Database A is replicated to the other 2 databases.

My problem is I have a batch job running on one of the internal databases, (database B) which is taking a long time to run. In particular, there is one query that runs against the replicated tables that takes about 2 minutes to run on database B, but takes about only 20 seconds to run on database C. The replicated tables on both the databases have the exact same indexes.

I noticed that the my query requires about 15000 disk reads on database B, where it uses about 5000 disk reads on database C. I checked the execution plans, and they are identical.

I can't seem to figure out what's going on. I would appreciate it if someone would shed some light on this problem

Thanks in Advance
Vikram Received on Thu Nov 29 2001 - 11:12:06 CST