Performance Issue

Question

Performance Issue

VastSQL

SSC-Dedicated

Points: 36144
More actions
March 7, 2008 at 6:53 am

#124257

Hi All,
Is there any performance issue in using EXISTS and TOP in queries.
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Ratheesh.K.Nair (3/7/2008)
Hi All,
Is there any performance issue in using EXISTS and TOP in queries.
Thanks in Advance

I'm sure we could find one :). Could you give a bit more info?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Considering their purpose is to limit data from coming back - they're going to cause the query to be "harder", more work, etc... But that's the definition of what they do.

Do you have a specific example in mind?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 3

I agree with Matt... EXISTS in the form of NOT EXISTS is used to find something that has no match in another table. EXISTS by itself should probably be converted to an INNER JOIN. Unless there's a bunch of correlation code involved, there's no real performance advantage to using one or the other but the use of INNER JOIN makes the code more consistant from a readability standpoint.

--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)

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 4

Thanks All,

The problem is that after reviewing the codes they said that EXISTS and TOP have to be removed saying that they will affect the performance....Any solution for this.

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 5

Not without actually seeing the code...

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Ratheesh.K.Nair (3/7/2008)
Thanks All,
The problem is that after reviewing the codes they said that EXISTS and TOP have to be removed saying that they will affect the performance....Any solution for this.

By the way - who's "they"?

And by the way - I'm not denying in any way that EXISTS or TOP don't affect performance. They just don't necessarily affect performance in the way you might think. Selecting the TOP 10 rows in a 100M row table will "affect performance" in a big way - have you seen how long it takes to return 100 Million rows?

The fallacy in the thinking is that a query without those clauses are the same as ones with them. If they don't add anything (meaning -they're not filtering anything out, or you're asking for the Top 20 when you're only going to get 10 back), then yes - they will drag performance down for no good reason. But in most cases - the outcome will be VERY different depending on whether they're there or not.

I do agree that an inner join is often the same. I would usually use an EXISTS instead of an INNER JOIN if I'm checking for children rows and I don't want duplicate "parent" rows, but otherwise I'd use the join method.

Like Jeff said - what's the query in question?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?