quote:

---

Originally posted by sodeep

How often you recompile your SP?

---

USE [IMCWharehouse]
GO
/****** Object:  StoredProcedure [dbo].[SEC_AccountHoldingsAsOf]    Script Date: 05/01/2008 15:49:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[SEC_AccountHoldingsAsOf]
@posdate as datetime

as
select
	ad.bank,ad.account_number,ad.account_short_name,
	p.security_id,isnull(b.NAME,isnull(tw.name,ms.name)) as SecurityDescription,
	p.security_price,mc.classcode,
		sum(case
		when p.security_price = 9999999.99900 then 0
		when mc.classcode between '000'and'299' then (p.quantity * p.security_price) / 100
		when mc.classcode between '950'and'999' then (p.security_price * p.quantity) * -1
		when mc.classcode between '300' and '324' or mc.classcode between '330' and '399'then p.acm_ctf_mtf_price * p.quantity
		when p.security_price = '0' then p.cost
	else
		(p.security_price * p.quantity)
	end) as MV,p.position_date,p.quantity,p.cost,p.acm_ctf_mtf_price
	from positions p
	left join BloombergEquityData b on p.security_id = b.ID_CUSIP 
	left join account_detail ad on p.account_id = ad.account_id
	left join metavanteclasscodes mc on p.security_id = mc.cusip
	left join MacgregorSecurityMaster ms on p.security_id = ms.cusip
	left join TobaSecurityWarehouse tw on p.security_id = tw.cusip
	where p.position_date = @posdate and
	((ad.investment_authority_id in ('2','3') and ad.bank in ('98','7R')) or ad.bank = '9W')
	group by	ad.account_number,ad.account_short_name,p.security_id,
				p.security_price,p.position_date,ad.bank,mc.classcode,p.quantity,p.cost,p.acm_ctf_mtf_price,isnull(b.NAME,isnull(tw.name,ms.name))
	order by ad.account_number,p.security_id

quote:

---

Originally posted by tkizer

Because you are impacting the fragmentation and/or statistics significantly apparently. Thus SQL is having to read more non-contiguous pages and/or choosing a bad execution plan.

I'd be very concerned if my processes run within 60 seconds. Anything that takes more than 5-10 seconds is looked into and optimized if possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---

quote:

---

Originally posted by sodeep

I mean how often you put with recompile option in your SP so you its recompiled fresh in cache.

---

quote:

---

Originally posted by tkizer

Are these indexed?:

positions.security_id
positions.account_id
positions.position_date
BloombergEquityData.ID_CUSIP
account_detail.account_id
account_detail.account_number
metavanteclasscodes.cusip
MacgregorSecurityMaster.cusip
TobaSecurityWarehouse.cusip



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

---