Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delay in Query Execution time

Posted on 2006-11-21
6
Medium Priority
?
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

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 500 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 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