Reduce Compile time - SQL Server Forums

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)

Reduce Compile time

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-14 : 23:49:41

[code]SELECT t.st_code, m.st_desc,
SUM(CASE WHEN t.trx_type IN ('CN', 'CNC') then 0 else t.total_price*h.forex_rate END) as SALES,
SUM(CASE WHEN t.trx_type IN ('CN', 'CNC') then 0 else t.qtt_out-t.quantity END) as QUANTITY,
SUM(t.cost_mr) as COST,
SUM(ISNULL((NULLIF(CASE WHEN t.trx_type IN ('CN', 'CNC') then 0 else t.total_price*h.forex_rate END-t.cost_mr, 0)/NULLIF(CASE WHEN t.trx_type IN ('CN', 'CNC') then 0 else t.total_price*h.forex_rate END, 0)), 0)*100) as SALES,
SUM(ISNULL((NULLIF(CASE WHEN t.trx_type IN ('CN', 'CNC') then 0 else t.total_price*h.forex_rate END-t.cost_mr, 0)/NULLIF(t.cost_mr, 0)), 0)*100) as COST
FROM st_trx t join (
SELECT trx_type,
forex_code,
CASE WHEN trx_type IN ('DO', 'CDO','DOL') then do_no else in_no END as ref_no,
CASE WHEN forex_rate IS NULL or forex_rate = 0 then 1 else forex_rate end as forex_rate,
CASE WHEN trx_type IN ('DO', 'CDO','DOL') then CASE WHEN in_date IS NULL then do_date else in_date END else in_date END as date
FROM st_head
WHERE trx_type IN('DO','CDO','DOL','INV','CS','CN','DN','POS','INC','CNC')
)h on t.trnx_ref=h.ref_no and t.trx_type=h.trx_type join
st_mast m on t.st_code = m.st_code
WHERE (h.date >='20090101' and h.date <'20100101')
GROUP BY t.st_code, m.st_desc
ORDER BY t.st_code[/code]
Is this query still able to minimize its speed?sorry i still learning how to minimize everything...
[code]cached plan size 140B
complie cpu 213
complie memory 2448
complie time 213
estimated subtree cost 531.334[/code]

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-15 : 00:01:51

can't provide table and sample data...because inside all bad data

and no ssms tools installed in the server

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources