insert into #TblDebits(invoice, matter, mattername, mbillaty, lindex, mclient, currency, billdate, marrange, clarrange, ldtype, lddesc, cdesc, cost, fee)
select invoice =
when ledger.linvoice > 999999 then ledger.linvoice
else ledger.linvoice % 100000
ledger.lmatter as matter ....
from ledger, ledcode, matter, periodt, client
where ledger.llcode = ledcode.lccode
and ledger.lmatter = matter.mmatter
and ledger.lperiod = periodt.pe
and matter.mcurrency like @strCurr
and (year(periodt.pebedt) < year(@dtCurrDate) or
year(periodt.pebedt) = year(@dtCurrDate) and month(periodt.pebedt) <= month(@dtCurrDate))
and ledger.ltradat <= '02/24/2004'
and ledger.lzero = 'N'
When I execute this SQL in my local server, it takes four seconds, when I execute in another server, it takes more than 2 minutes. The two servers have the same service pack (3a).
When I tried to subsitute the variables with the real values in the SQL and execute in the second server, it takes 2 seconds.
Any ideas why this happen? Will the variable slow down performace? But looks like my local server does not affected by this.