Multiple table left join-Improve response time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple table left join-Improve response time

Author

Topic

devanshi
Starting Member

2 Posts

Posted - 2011-12-15 : 03:35:19

I have a query that uses multiple left joins.I need to improve performance(The response time).
I have created index on required tables.

SELECT ROWNUM
, log.a
, TO_CHAR(log.b,'mm')AS MONTH
, log.c
, log.d
, batch.e
, batch.f
, trx.g
, trx.h
FROM log
LEFT JOIN batch
ON log.id = batch.id
AND log.bid = batch.bid
AND SUBSTR(batch.RPDT_XMT_TY_CD,1,1) = 'I'
LEFT JOIN trx
ON log.id = trx.id
AND SUBSTR(trx.RPDT_XMT_TY_CD,1,1) = 'I'
WHERE log.id = transmID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 03:45:36

its because of SUBSTR expression in join condition. . it wont use index if at all it exists on fields used inside function

try using like instead

AND batch.RPDT_XMT_TY_CD LIKE 'I%'
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

devanshi
Starting Member

2 Posts

Posted - 2011-12-16 : 04:22:03

There was no signiicant improvement by replacing substr with like.The explain plan remained same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:30:54

quote:
Originally posted by devanshi

There was no signiicant improvement by replacing substr with like.The explain plan remained same.

is it using indexes present?
also what are costly steps in execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources