optimization

hi,
i have written a qurey to retive some data form a table,but i want the qurey should be more faster, i dont want to set index, other that that,if any.
advice required!!!!!

THIS THE QUREY
---------

select intpurchaseqty,strCurrencyCode as Currency,intDenomination as Denomination,case
when strPrefix ='' then '0' else strprefix end as Prefix,strSrlnoFrom as From_, "
strsrlnoTo as To_ from tblfxrows where intBranchCode=5
and strInstrumenttype ='TC' and ( left(intEncashmentID,2) like 'TR%' or
(intTransferedFromID like 'IB%' and inttransactionrate = 0)) and datTransactiondate <= convert(datetime,
'05/01/5006',103) and dattransactiondate >= '05/01/2006' "Hi,

I can give an idea that.
1. replace like with '=' sign(like left(intEncashmentID,2) ='TR')
2. instead of case, can use isnull/replace.
3. Use '(nolock)'.

Regards,
Sharmila

Quote:

Originally Posted by logesh

hi,
i have written a qurey to retive some data form a table,but i want the qurey should be more faster, i dont want to set index, other that that,if any.
advice required!!!!!

THIS THE QUREY
---------

select intpurchaseqty,strCurrencyCode as Currency,intDenomination as Denomination,case
when strPrefix ='' then '0' else strprefix end as Prefix,strSrlnoFrom as From_, "
strsrlnoTo as To_ from tblfxrows where intBranchCode=5
and strInstrumenttype ='TC' and ( left(intEncashmentID,2) like 'TR%' or
(intTransferedFromID like 'IB%' and inttransactionrate = 0)) and datTransactiondate <= convert(datetime,
'05/01/5006',103) and dattransactiondate >= '05/01/2006' "

|||Replace
( left(intEncashmentID,2) like 'TR%' with

LEFT(intEncashmentID,2) = 'TR'

also make sure that there is an index on that column