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

Opinions Needed

I would like some opinions on how you would deal with the following scenario:

We probably have somewhere from 500 to 1000 reports (written in crystal). We also have about 120 clients; each client has their own database.One of the reasons for so many reports is because a lot of our customers want report A but with this or that extra column added so we end up with a lot of custom versions of one report for a particular client.My question is in converting over to Reporting Services, how would you setup the file structure?

Right now, we are thinking that every client is going to have their own folder which would contain all Reports, Models, and Datasources.What do you guys think?

There is a feature called My Reports that you could use. This allows customers to keep their own versions of the reports in their own folders.

http://msdn2.microsoft.com/en-us/library/aa256334(SQL.80).aspx