Solved

Delay in Query Execution time

Posted on 2006-11-21
6
237 Views
Last Modified: 2008-03-17
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.
0
Comment
Question by:BYTECHINDIA
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what database?
usually, this is due to missing or outdated statistics, or missing indexes.
0
 
LVL 42

Accepted Solution

by:
frodoman earned 250 total points
Comment Utility
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.

frodoman
0
 
LVL 5

Expert Comment

by:MrNetic
Comment Utility
BYTECHINDIA,

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

Best Regards,

Paulo Condeça
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:BYTECHINDIA
Comment Utility
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
0
 
LVL 5

Expert Comment

by:MrNetic
Comment Utility
BytechIndra,

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.
0
 
LVL 1

Expert Comment

by:esmail1349
Comment Utility
1-Run DBCC SHRINKDATABASE on database B with truncateonly Option
2-Run DBCCCHECHDB on database B Or Run DBCC CHECKTABLE
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now