Web Development

Ask something
Discussion Locked

Optimization of sql statements

0 helpful
1 reply

by rumanapunawala

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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, 90040617028918, 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??

1 reply

by tony hopkinson

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, 90040617028918, 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 ?

This discussion is closed to new replies.