Optimizing the Query..

Question

Post reply

Optimizing the Query..

Viewing 4 posts - 16 through 18 (of 18 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

vijay1327-891581 (10/6/2010)

1: Remove count(*) and replace it with count(any one INTEGER type column).

Great if you're trying to slow the query down or change it's meaning. Not very successful at speeding one up though...

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sahasam Ten Centuries Points: 1001 More actions · Answer 2

Sahasam

Ten Centuries

Points: 1001

October 6, 2010 at 5:16 pm

#1233202

Great Suggestions!

Thanks,

Sahasam

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

vijay1327-891581 (10/6/2010)

There are 2 suggestion for this:

1: Remove count(*) and replace it with count(any one INTEGER type column). Here all you are doing is checking if there is any row > 0

2: In the mentioned query, if there are any static TABLES used, make sue of (NOLOCK) which will help reduce the time

e.x: if the same table is being used by some other object at the same time this will be a problem hence use NOLOCK.

NOTE: Use this NOLOCK only if its astatic table, else you will be DIRTY reading.

Actually, removing COUNT(*) in favor of COUNT(1) is... a myth. In fact, COUNT(*) may work better in some cases because it can take advantage of indexing whereas COUNT(1) cannot.

As previously mentioned, this appears to be an ORACLE query. There is no WITH(NOLOCK) in Oracle. Besides, as a blanket recommendation, it's a bad thing to do because it can allow for the duplication of data and, face it... it's just a mask for bad code. The key is to fix 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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

The two COUNT(*)'s are part of correlated subqueries (joined on the "pm" alias) with aggregates. It would likely be much cheaper computationally if those aggregates were done separately and stored in a Temp Table and then joined to. Unfortunately, Oracle's concept of what a Temp Table is is MUCH different than what SQL Server has.

So... I recommend some "Subquery Refactoring" which is Oracle's fancy name for Common Table Expressions. You could also do the Subquery Refactoring in the FROM clause. The real key is to turn the subqueries into NON correlated subqueries because of the aggregations involved.

Then, there are the other things that other folks have already stated. Non Sargable WHERE clauses like the following make it impossible to get an index seek... even in Oracle.

trunc(pm.last_completion_dt) > trunc(sysdate-7)

--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)
Intro to Tally Tables and Functions