Why execution Time different

Question

0.00/5 (No votes)

See more:

SQL

SELECT  i.IMAGE_ID , count( DISTINCT p.formnumber)
FROM image_details i
LEFT JOIN tin_user t ON t.image_id = i.IMAGE_ID
LEFT JOIN population p ON p.tin = t.tin
GROUP BY i.IMAGE_ID
ORDER BY 2

execution time > 41 Minutes
returned rows 6623

SQL

SELECT  i.IMAGE_ID, count( DISTINCT p.formnumber)
FROM image_details i , tin_user t , population p
WHERE t.image_id = i.IMAGE_ID AND p.tin = t.tin
GROUP BY  i.IMAGE_ID
ORDER BY 2

execution time < 27 Seconds

returned row 6623

Table_Name (No of Records) (No of Columns)

SQL

<big>Image_details (6733) (13)
tin_user (746367) (6)
population (743829) (50)</big>

Posted 4-Dec-12 18:54pm

Photon_

Add a Solution

Comments

Sergey Alexandrovich Kryukov 5-Dec-12 1:01am

Even though you can possibly get useful performance recommendation, it does not looks like quite a correct question. How different queries can take the same time? Even the same query can take different time if you repeat it...
--SA

Photon_ 5-Dec-12 1:08am

yes, if i repeat the query it show different time not even less but also more too,

but my question is why so big different i think you didn't visualize the question see the second one shows time in minues that is > 41 but the second one shows result within half minutes this is my question and also the domain of query , condition and even output is also same and ya there is one and only difference is type of join but how much it is different this is the question ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2012-12-04T19:09:00

Solution 1

Each query is optimized by the database engine before execution. This means that the engine tries to find the fastest plan to execute the query.

There are several factors affecting the execution time. For example:
- presence of relevant indexes
- statistics used by the optimizer
- the number of permutations calculated during the optimization
- workload when the query is executed
- presence of the desired data blocks in the memory
- workload on the network when returning results and so on.

So what you should do is to investigate the plans of different queries. To star, have a look at the documentation at: Obtaining Query Execution Plan Information[^]

Posted 4-Dec-12 19:09pm

Wendelius

Comments

Photon_ 5-Dec-12 1:16am

The query is executed on server(locally) no other process were executed during execution of first one although with the other one execution of other were on process.

There is the difference of join and only joining all the conditions and fields taken are same no difference there.

Wendelius 5-Dec-12 1:22am

Yes, the query is always executed on the server. Again, have a look at the plan of the queries and investigate if there are affecting differences.

Photon_ 5-Dec-12 1:26am

on local server machine

Wendelius 5-Dec-12 5:05am

I don't quite understand your reply. The database engine is always responsible for creating the plan and executing the query.

Espen Harlinn 11-Dec-12 17:49pm

5'ed!

Wendelius 12-Dec-12 0:18am

Thank you :)