2 exactly same db on one server have big difference in performance

[mm8294]

We have a database DEV on a Win2k server, and we backed it up and restored it on the same server as TEST. Now if we run the following query:

Select a.* from ovrn_nielsen_qtrh_final a where a.CALL_LETTER_ID=173329 and a.NLSN_FIN_QTR_DATE>='9/14/2005 8:00:00 PM' and a.NLSN_FIN_QTR_DATE<='9/14/2005 9:00:00 PM'

On TEST, it takes 10 seconds, but on DEV it takes 27 seconds, and our application users get 'timeout' error. I have updated the statistics, but it does not make any difference.

The structure of the table is:

NLSN_FIN_QTR_ID int 4
NLSN_FIN_QTR_DATE date/time 8
NLSN_FIN_QTR_TIME date/time 8
NLSN_FIN_QTR_HUT float 8
NLSN_FIN_QTR_RAT float 8
NLSN_FIN_QTR_SHARE float 8
CALL_LETTER_ID int 4

And the only index on this table is a clustered index on NLSN_FIN_QTR_ID.

Is there anything else I can do?

Besides, the users need insert 10,000 rows in the table everyday soon. What I am thinking about is: drop the index before insert, and recreate it after. Is there anything else I can do to improve the performance of insert?



Thank you very much.

 

[mm8294]

Guys, help please.

Thanks in advance.

 

[SQLBill]

Have you checked the estimated execution plan to see if the two instances are handling the query differently?

-SQLBill

Posting advice: FAQ481-4875

 

[mm8294]

SQLBill, thank you very much for your advice.

I have not checked it. But, since TEST is an exact clone of DEV, and the queries running are the same, I think the execution plan should be the same, too.

However, I will definitely check it.

 

[yelworcm]

One other thing to note, if you are dealing with large amounts of data, and limited memory, the disk activity can have about this amount of impact on performance. Does re-running the second query come back faster (after data has been cached), or is it the same?