Solved

Delay in Query Execution time

Posted on 2006-11-21
6
239 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]
ID: 17987012
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
ID: 17987328
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
ID: 17994110
BYTECHINDIA,

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

Best Regards,

Paulo Condeça
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:BYTECHINDIA
ID: 17994915
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
ID: 17995025
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
ID: 18034455
1-Run DBCC SHRINKDATABASE on database B with truncateonly Option
2-Run DBCCCHECHDB on database B Or Run DBCC CHECKTABLE
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

825 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