Query Optimization
-
Alright guys, this is going to be a bit messy. Just wondering if anyone had any suggestions on optimizing this query. Let me know if there's a better way to post this:
Query Removed and posted here: http://theninjalist.com/
I didn't want to post the formatted version here because it was too big. That link will show you the full, formatted query.
-
>>Alright guys, this is going to be a bit messy.
That qualifies as understatement of the month
Try these SQL formatting sites:
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
-
Yep, I used http://sqlinform.com/ to format it. You can peep it at http://theninjalist.com/.
-
FROM drs_pending_deduct AS a
LEFT OUTER JOIN settlement AS b
ON a.order_id = b.order_id
WHERE a.company_id = 'TMS2'
AND a.payee_id IN (SELECT id
FROM payee
WHERE status = 'A'
AND non_office_emp = 'Y'
AND company_id = 'TMS2')
AND a.ready_to_pay_flag <> 'V'
AND a.amount IS NOT NULL
For optimization, try removing the IN (SELECT ...).
Make it an inner join to a derived table instead ?
INNER JOIN
(
SELECT DISTINCT id
FROM Payee
WHERE Status = 'A'
AND non-office_emp = 'Y'
AND company_id = 'TMS2'
)
-
Actually, it's a long but fairly simple crosstab. The big performance killers are whereever you have the unqualified correlation of...
a.order_id <> b.order_id
That's IF it were to ever be executed... Can you say wasted code? The join on the tables makes that code impossible ...
FROM drs_pending_deduct as a
LEFT OUTER JOIN settlement as b
ON a.order_id = b.order_id
I haven't analyzed the code deeply, but between the obvious unused code and the horrible date binning methods used, my advise would be, figure out what the code was supposed to do, throw it away, and rewrite it correctly.
Lemme guess... written by Business Objects or some Report Formatter?
--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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply