November 28, 2012 at 6:16 am
Hi Everyone,
I have a huge stored procedure in which I have one main table and lots of left joins with subqueries
like below
SELECT
A.col, B.col, B.col....
FROM
<MainTable> A
Left join
(
Some select statement
) B on A.<col_x> = B.<col_x>
Left join
(
).......
Now if I am executing query its not returning result set even after 30 mins.
But If I will remove some columns from select Its showing me records in 10 mins.
how can selecting and deselecting few columns make difference in query performance.
November 28, 2012 at 6:23 am
Column selectivity has a huge part to play in a statement as it tells the query optimiser what to do.
If you could follow the link in my signature on posting performance problems and also the one on posting code and data for the best help, we can see what is going on.
You might be best including the estimated execution plan instead of the actual execution plan due to the amount of time it takes to run the full query. If you can upload that as a sqlplan file, we can see what is going on.
November 28, 2012 at 6:30 am
Thanks for the reply. I will post other details very soon. But because of confidentiality I can not post the exact query but will send few parts of the query.
November 28, 2012 at 6:43 am
vaibhavktiwari (11/28/2012)
how can selecting and deselecting few columns make difference in query performance.
In general this question can be answered using a general example:
If your query can use an index for covering the join and where clause and you select only columns that are included in this index too the query can use this index. If you use the same query and select some additional columns that are not included in the index it might be that a full table scan is done. And that can slow down your query dramatically.
As anthony.green wrote this is something you would see in the execution plan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply