General discussion

Locked

Optimization of sql statements

By rumanapunawala ·
SELECT NOTIFICATIONID, a.STATUS, NOTIFICATIONREFNO,

(SELECT COUNT (*) FROM COMPANY com WHERE a.forcompanyid=com.parentcompanyid)AS PARENTINDICATOR,

c.companyname as companyname, TRANSACTIONID, REFAMOUNT, REFCURRENCY, NOTIFICATIONDATE, FORUSERID, FORGROUPID, FORCOMPANYID,

NOTIFICATIONHEADER, NOTIFICATIONMESSAGE, BANKACD, FORCOMPANYID, NOTIFICATIONHEADER, NOTIFICATIONMESSAGE, BANKACCOUNTID,

b.NOTIFICATIONTYPEID, NOTIFICATIONTYPE, DESCRIPTION, REPLYREQ, TRXTYPE,

CATEGORY, DECODE(CATEGORY, 'System', '1', CATEGORY) CATORDER

FROM NOTIFICATION a, NOTIFICATIONTYPE b, COMPANY c
WHERE (FORMATCODE NOT LIKE 'Z%' OR FORMATCODE IS NULL)
AND ( ( BANKACCOUNTID IS NULL) OR (BANKACCOUNTID IN ( NULL, 90040617028**8, 90040617028920) ) )
AND a.DELETED is null AND
UPPER(FORCOMPANYID) = c.COMPANYID
AND c.PARENTCOMPANYID = 20040616015753
AND a.NOTIFICATIONTYPEID = b.NOTIFICATIONTYPEID
ORDER BY PARENTINDICATOR DESC,
COMPANYNAME ASC,
CATORDER ASC,
NOTIFICATIONDATE DESC;

Can this statements be further optimized??

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Tony Hopkinson In reply to Optimization of sql state ...

Can't tell from the just statement.
Structure of the tables, existing indexes required.

Then there is the question of whether it's worth optimising. Every index you add increases the transaction overhead on invididual tables. So if you do that a lot and the above query infrequently it might cost you more.

Most dbms query tools have a "show plan" facility which tell you percentage wise wher the bulk of then time is going.

That said,
The order by clause depnding on how much data is in the result set will cost you.

The upper on companyID will cost you, see if you can engineer it out..

( ( BANKACCOUNTID IS NULL) OR (BANKACCOUNTID IN ( NULL, 90040617028**8, 90040617028920) ) )
why null test twice ?

decode not cheap

not like Z%, if you've a standard collation and no lower case (< 'Z' ) will be much faster particulary if there is a usable index on the field.

HTHs


why have you got null bankacoountid in twice ?

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums