?
Solved

Delay in Query Execution time

Posted on 2006-11-21
6
Medium Priority
?
243 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 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