Delay in Query Execution time

I have 2 databases , let us say A & B. I run a Query with multiple innere joins  . Database A contains 4 times records than database B.
My query takes 1 seconds to get executed in Database "A" and 5 minutes in Database "B".

The Database structure of A & B is same , In fact "B" has been created from Dump of "A". What could be the possible cause of such delay.
Who is Participating?
frodomanConnect With a Mentor Commented:
I agree.  It sounds like database B was created based on the table structure and data of database A.  In many databases however, indexes don't copy over along with a data dump so your second query is likely doing full table scans on all of the joins.  

Another possibility is caching of the data gathering plan.  On Oracle, for example, the database has a memory area where the execution plan for sql statements are cached.  If you execute a query that has the plan cached it'll be faster than if the db engine has to recreate the plan from scratch - obviously the time difference depends on the complexity of the query.

Finally, you didn't indicate if these databases are running in the same environment.  The processing power, I/O speed, and current workload of the server hosting the database also plays into the speed of getting your results back.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database?
usually, this is due to missing or outdated statistics, or missing indexes.

Create Indexes on Table B , and afterwards issue a update statistics with full scan.

Best Regards,

Paulo Condeça
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

BYTECHINDIAAuthor Commented:
The Databases are in SQl Server 2000.
* As such there are no Indexes in any of the databsae.
* Both the Database are running on the same Machine.
* The database  have been checked and validated and are found to have same Structure.

Kindly suggest any varification/resolution

There could by external fragmentation on the Physical Disk.
You could also run a dbcc showcontig in the 2 DB and compare values.

This will give you a view ( basic ) of your DB's

Best Regards,

Paulo Condeça.
1-Run DBCC SHRINKDATABASE on database B with truncateonly Option
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.