Improve performance of query

Question

Improve performance of query

raj.prabhu001

Right there with Babe

Points: 758
More actions
September 28, 2015 at 6:54 am

#321504

Hi guys please guide me how can i improve the performance of the below query by making changes to it.
Select Col1
Col2
..... col n
FROM
(
Select col1
col2
... col n
FROM ( Select Col1
.. col n
FROM VIEW1
)a
LEFT JOIN
( Select Col1
.. col n
FROM VIEW1
)b
on a.id=b.id and a.date=b.date
Group by col1
... coln
)x
Group by col1 ...coln

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

raj.prabhu001 (9/28/2015)
Hi guys please guide me how can i improve the performance of the below query by making changes to it.
Select Col1
Col2
..... col n
FROM
(
Select col1
col2
... col n
FROM ( Select Col1
.. col n
FROM VIEW1
)a
LEFT JOIN
( Select Col1
.. col n
FROM VIEW1
)b
on a.id=b.id and a.date=b.date
Group by col1
... coln
)x
Group by col1 ...coln

Post the actual execution plan, please. Also, definitions of any views/functions. Table and index scripts will help too.

LEFT JOIN

( Select Col1

.. col n

FROM VIEW1

)b

SQL Server only retrieves the rows it requires.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

raj.prabhu001 (9/28/2015)
Hi guys please guide me how can i improve the performance of the below query by making changes to it.
Select Col1
Col2
..... col n
FROM
(
Select col1
col2
... col n
FROM ( Select Col1
.. col n
FROM VIEW1
)a
LEFT JOIN
( Select Col1
.. col n
FROM VIEW1
)b
on a.id=b.id and a.date=b.date
Group by col1
... coln
)x
Group by col1 ...coln

Without being given anything else to go on, here's my assessment.

The GROUP BY would appear to be the giveaway on this. It appears that it's being used like a DISTINCT to remove duplicates for the results. That likely means than you have created an accidental CROSS JOIN in the form of a many-to-many between god-knows-what-happens-in-the-views due to insubstantial criteria and that, of course, is a major performance problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)