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 2000 Forums
 Transact-SQL (2000)
 monster query taking too long

Author  Topic 

jrogers
Starting Member

34 Posts
Posted - 2006-07-11 : 15:51:33
Hi everyone

I have a query which is taking over 20 minutes to run on SQL server..I am sure it can be optomised, but I am at the limit of my ability as it is.. If I post it here will someone be kind enough to help (about 300 lines)

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-07-11 : 16:03:26
You can certainly post it. Make sure to put code tags around it to retain the formatting.

The fact that your query is 300 lines makes me wonder what your database design looks like.

Tara Kizer
aka tduggan
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-11 : 16:13:40
thanks tkizer


select
	sum(case when vegacash >0 then vegacash else 0 end) PositiveVega,
	sum(case when vegacash <0 then vegacash else 0 end) NegativeVega,
	sum(case when GammaCash >0 then GammaCash else 0 end) PositiveGamma,
	sum(case when GammaCash <0 then GammaCash else 0 end) NegativeGamma,
	sum(case when thetaCash >0 then thetaCash else 0 end) PositiveTheta,
	sum(case when thetaCash <0 then thetaCash else 0 end) NegativeTheta
From (
	Select 
	 	
		sum(case
				when ins.tiInstType in (10,19) then (hip.iquantity * EqOpdaMV.Vega * EqOpdaMV.noShs * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * CvdaMv.vega * inp.dbcurrencyMult * inp.dbperDerivMult)
				when ins.tiInstType = 17 then (hip.iquantity * CbasdaMv.vega * inp.dbcurrencyMult * inp.dbperDerivMult)
			end
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else SpotRate.FxRate 
		end) as VegaCash,
		sum(case
				when ins.tiInstType in (10,19) then (hip.iquantity * EqOpdaMV.Gamma * EqOpdaMV.noShs * EqOpdaMV.px * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * CvdaMv.Gamma * CvdaMV.cratio * cvdaMV.px)
				when ins.tiInstType = 17 then (hip.iquantity * CbasdaMv.Gamma * CbasdaMV.cratio * cbasdaMV.px)
			end
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else SpotRate.FxRate 
		end) as GammaCash,
		sum(case
				when ins.tiInstType in (10,19) then (hip.iquantity * EqOpdaMV.Theta * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * CvdaMv.Theta * inp.dbcurrencyMult)
				when ins.tiInstType = 17 then (hip.iquantity * CbasdaMv.Theta * inp.dbcurrencyMult)
			end
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else SpotRate.FxRate 
		end) as ThetaCash,
		hip.dtInstpos dtRun,
		case 
			when bk.vc40name = 'EVENT CATALYST P' then 'Event Catalyst'
			when bk.vc40name = 'EVENT CATALYST' then 'Event Catalyst'
			when bk.vc40name = 'MACRO HEDGE' then 'Event Catalyst' 
			when bk.vc40Name = 'VOL ARB' then 'Vol Arb'
			when bk.vc40Name = 'EVENT DERIV' then 'Event Catalyst' 
			when bk.vc40Name = 'LONG_SHORT' then 'LS - Pairs'
			when bk.vc40Name = 'RESEARCH' then 'LS - Pairs'
			when bk.vc40Name = 'SH BOOK' then 'LS - Pairs'
			when bk.vc40Name = 'CONVERTIBLES' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name <> 'CA_EVENT' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name = 'CA_EVENT' then 'Event Catalyst'
			when bk.vc40Name = 'CAPITAL STRUCTURE' then 'Capital Structure'
			when bk.vc40Name = 'CAPITAL STRUCTURE P' then 'Capital Structure'
			Else bk.vc40Name 
		end as Book
		
	FROM Strategy st inner join 
		book bk on st.idbook = bk.idbook 
	Inner join
		Position pos on pos.idStrategy = st.idStrategy and
		pos.idfund = 7
	inner Join 
		HistInstPos hip on hip.idPosition = pos.idPosition and  
		hip.tiIndelibleReport = 0 and  
		hip.dtInstPos between '01 Apr 2006' and '01 Jul 2006'
	inner Join 
		inststatic ins on pos.idinststatic = ins.idinststatic
	inner Join 
		InstType itp on ins.tiInstType = itp.idInstType
	inner join 
		InstPricing inP on ins.IdInstStatic = inP.idInstStatic and 
		inp.idCodeType = 2	
	inner Join 
		Currency ccy on ins.idBaseCurrency = ccy.idCurrency   
	Left Join (   
		select        
			had.idInstStatic idInstStatic,        
			ino.dbSharesPerOption noShs,       
			sum (case had.idcalcField when 352 then had.dbValue else 0 end ) Gamma,        
			sum (case had.idcalcField when 356 then had.dbValue else 0 end ) Vega,
			Sum (case had.idcalcField when 353 then had.dbValue else 0 end ) Theta,        
			homu.dblongprice px,
			homu.dtmark  AS dtGreek 
		from HistAnalytic had inner join        
			InstOption ino on had.idInstStatic = ino.idInstStatic and
			datediff(dd,ino.dtexpiry,had.dtDate) <0   
		inner Join        
			HistOfficialMark homu on ino.idInstUnderlying = homu.idInstStatic      
		where 
			homu.dtmark between '01 Apr 2006' and '01 Jul 2006' and
			had.dtDate between '01 Apr 2006' and '01 Jul 2006' and  
			datediff(dd,had.dtDate,homu.dtmark)=0    
		Group by        
			had.idInstStatic,        
			ino.dbSharesPerOption, 
			homu.dtmark,
			homu.dblongprice) EqOpdaMV on pos.idInstStatic = EqOpdaMv.IdInstStatic and
			datediff(dd,EqOpdaMV.dtGreek,hip.dtInstPos)= 0
	left join  (   
		select         
			had.idInstStatic idInstStatic,         
			CSI.dbOrdinaryConversionRatio cratio,
			cb.dbFaceValue Denom,    
			sum (case had.idcalcField when 352 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Gamma,        
			sum (case had.idcalcField when 356 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Vega, 
			sum (case had.idcalcField when 353 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Theta,  
			had.dtDate dtGreek, 
			case 
				when homu.dblongprice is null then 0 
				else homu.dblongprice /
					case 
						when ins.idbaseCurrency = insu.idBaseCurrency then 1
						else fx.fxrate/fxu.fxrate
					end
			end px     
		from HistAnalytic had inner join         
			ConvScheduleItem csi on had.idInstStatic = csi.idInstStatic and          
			had.dtDate between '01 Apr 2006' and '01 Jul 2006'     
		inner Join         
			Convertible cb on csi.idInstStatic = cb.idInstStatic
		inner join
			InstStatic ins on cb.idinststatic = ins.idinststatic
		inner join
			InstStatic insu on cb.idInstUnderlying = insu.idinststatic  
		left Join         
			HistOfficialMark homu on cb.idInstUnderlying = homu.idInstStatic and         
			homu.dtmark between '01 Apr 2006' and '01 Jul 2006' and
			datediff(dd,homu.dtmark,had.dtDate) = 0 
		Left Join (   
			select         
				case             
					when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
					else 1/((hfx.dbBid+hfx.dbAsk)/2)         
				end fxRate,         
				case             
					when fx.idBaseCcy = 259 then fx.idOtherCcy             
					Else fx.idbaseCcy         
				end idCCy,
				hfx.dtmark dtFx
			from  fxrate fx inner join             
				HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and  
				hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'          
			where         
				fx.idbaseCcy = 259 or fx.idotherCcy = 259) fx on ins.idBaseCurrency = fx.idCCy and
				datediff(dd,fx.dtfx,had.dtDate)=0
		Left Join (   
			select         
				case             
					when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
					else 1/((hfx.dbBid+hfx.dbAsk)/2)         
				end fxRate,         
				case             
					when fx.idBaseCcy = 259 then fx.idOtherCcy             
					Else fx.idbaseCcy         
				end idCCy,
				hfx.dtmark dtFx
			from  fxrate fx inner join             
				HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
				hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'     
			where         
				fx.idbaseCcy = 259 or fx.idotherCcy = 259) fxu on insu.idBaseCurrency = fxu.idCCy and
				datediff(dd,fxu.dtfx,had.dtDate)=0
		Group by         
			had.idInstStatic,         
			CSI.dbOrdinaryConversionRatio,
			had.dtDate,         
			case 
				when homu.dblongprice is null then 0 
				else homu.dblongprice /
					case 
						when ins.idbaseCurrency = insu.idBaseCurrency then 1
						else fx.fxrate/fxu.fxrate
					end
			end,         
			cb.dbFaceValue) CVdaMV on pos.idInstStatic = CVdaMV.IdInstStatic and
			datediff(dd,CVdaMV.dtGreek,hip.dtInstPos)=0
	left join  (   
		select         
			had.idInstStatic idInstStatic,         
			CSI.dbOrdinaryConversionRatio cratio,         
			cb.dbFaceValue Denom,           
			sum (case had.idcalcField when 352 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Gamma,        
			sum (case had.idcalcField when 356 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Vega, 
			sum (case had.idcalcField when 353 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Theta,         
			homu.dtmark dtGreek,
			case 
				when homu.dblongprice is null then 0 
				else homu.dblongprice /
					case 
						when ins.idbaseCurrency = insu.idBaseCurrency then 1
						else fx.fxrate/fxu.fxrate
					end
			end px      
		from HistAnalytic had inner join         
			ConvertibleAssetSwap cas on had.idInstStatic = cas.idInstStatic  and
			had.dtDate between '01 Apr 2006' and '01 Jul 2006'
		inner Join         
			ConvScheduleItem csi on cas.idConvertible = csi.idInstStatic     
		inner Join         
			Convertible cb on csi.idInstStatic = cb.idInstStatic    
		inner join
			InstStatic ins on cb.idinststatic = ins.idinststatic
		inner join
			InstStatic insu on cb.idInstUnderlying = insu.idinststatic   
		inner Join         
			HistOfficialMark homu on cb.idInstUnderlying = homu.idInstStatic and
			homu.dtmark between '01 Apr 2006' and '01 Jul 2006' and
			datediff(dd,homu.dtmark,had.dtDate) = 0
		Left Join (   
			select         
				case             
					when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
					else 1/((hfx.dbBid+hfx.dbAsk)/2)         
				end fxRate,         
				case             
					when fx.idBaseCcy = 259 then fx.idOtherCcy             
					Else fx.idbaseCcy         
				end idCCy,
				hfx.dtmark dtFx
			from  fxrate fx inner join             
				HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and  
				hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'          
			where         
				fx.idbaseCcy = 259 or fx.idotherCcy = 259) fx on ins.idBaseCurrency = fx.idCCy and
				datediff(dd,fx.dtfx,had.dtDate)=0
		Left Join (   
			select         
				case             
					when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
					else 1/((hfx.dbBid+hfx.dbAsk)/2)         
				end fxRate,         
				case             
					when fx.idBaseCcy = 259 then fx.idOtherCcy             
					Else fx.idbaseCcy         
				end idCCy,
				hfx.dtmark dtFx
			from  fxrate fx inner join             
				HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
				hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'     
			where         
				fx.idbaseCcy = 259 or fx.idotherCcy = 259) fxu on insu.idBaseCurrency = fxu.idCCy and
				datediff(dd,fxu.dtfx,had.dtDate)=0     
		Group by         
			had.idInstStatic,         
			CSI.dbOrdinaryConversionRatio, 
			homu.dtmark,       
			case 
				when homu.dblongprice is null then 0 
				else homu.dblongprice /
					case 
						when ins.idbaseCurrency = insu.idBaseCurrency then 1
						else fx.fxrate/fxu.fxrate
					end
			end,                 
			cb.dbFaceValue) CbasdaMv on pos.idINstStatic = CbasdaMv.IdInstStatic and
			datediff(dd,CbasdaMV.dtGreek,hip.dtInstPos)=0
	Left Join (   
		select         
			case             
				when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
				else 1/((hfx.dbBid+hfx.dbAsk)/2)         
			end fxRate,         
			case             
				when fx.idBaseCcy = 259 then fx.idOtherCcy             
				Else fx.idbaseCcy         
			end idCCy,
			hfx.dtmark dtfx   
		from  fxrate fx inner join             
			HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
			hfx.dtmark between '01 Apr 2006' and '1 Jul 2006'    
		where         
			fx.idbaseCcy = 259 or fx.idotherCcy = 259) SpotRate on ins.idBaseCurrency = SpotRate.idCCy and
			datediff(dd,spotrate.dtfx,hip.dtInstPos)=0


	group by 
		st.vc40Name,
		hip.dtInstpos,
		case 
			when bk.vc40name = 'EVENT CATALYST P' then 'Event Catalyst'
			when bk.vc40name = 'EVENT CATALYST' then 'Event Catalyst'
			when bk.vc40name = 'MACRO HEDGE' then 'Event Catalyst' 
			when bk.vc40Name = 'VOL ARB' then 'Vol Arb'
			when bk.vc40Name = 'EVENT DERIV' then 'Event Catalyst' 
			when bk.vc40Name = 'LONG_SHORT' then 'LS - Pairs'
			when bk.vc40Name = 'RESEARCH' then 'LS - Pairs'
			when bk.vc40Name = 'SH BOOK' then 'LS - Pairs'
			when bk.vc40Name = 'CONVERTIBLES' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name <> 'CA_EVENT' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name = 'CA_EVENT' then 'Event Catalyst'
			when bk.vc40Name = 'CAPITAL STRUCTURE' then 'Capital Structure'
			when bk.vc40Name = 'CAPITAL STRUCTURE P' then 'Capital Structure'
			Else bk.vc40Name 
		end) temp
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-11 : 18:08:30
I have tried creating temp tables instead of having the select statements after the left joins, this speeds the query up dramatically. But I need the query to be in one select statement, any ideas?
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-11 : 19:07:48
the first query never returned any values as it took over 40 minutes t0 run so I aborted.. I have now change query so that I am creating temp tables then referencing them (query takes 1min 10 secs to run), but what is the difference between explicitly creating a temp table, and using a subquery.. I am using the same joins on the temp tables as I would on the sub query..

new code posted below


-- Create Options Temp Table
select        
	had.idInstStatic idInstStatic,        
	ino.dbSharesPerOption noShs,       
	sum (case had.idcalcField when 352 then had.dbValue else 0 end ) Gamma,        
	sum (case had.idcalcField when 356 then had.dbValue else 0 end ) Vega,
	Sum (case had.idcalcField when 353 then had.dbValue else 0 end ) Theta,        
	homu.dblongprice px,
	homu.dtmark  AS dtGreek 
INTO #EqOpdaMv
from HistAnalytic had inner join        
	InstOption ino on had.idInstStatic = ino.idInstStatic and
	datediff(dd,ino.dtexpiry,had.dtDate) <0   
inner Join        
	HistOfficialMark homu on ino.idInstUnderlying = homu.idInstStatic      
where 
	homu.dtmark between '01 Apr 2006' and '01 Jul 2006' and
	had.dtDate between '01 Apr 2006' and '01 Jul 2006' and  
	datediff(dd,had.dtDate,homu.dtmark)=0    
Group by        
	had.idInstStatic,        
	ino.dbSharesPerOption, 
	homu.dtmark,
	homu.dblongprice

-- Create Convertible Bond Temp Table
Select         
	had.idInstStatic idInstStatic,         
	CSI.dbOrdinaryConversionRatio cratio,
	cb.dbFaceValue Denom,    
	sum (case had.idcalcField when 352 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Gamma,        
	sum (case had.idcalcField when 356 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Vega, 
	sum (case had.idcalcField when 353 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Theta,  
	had.dtDate dtGreek, 
	case 
		when homu.dblongprice is null then 0 
		else homu.dblongprice /
			case 
				when ins.idbaseCurrency = insu.idBaseCurrency then 1
				else fx.fxrate/fxu.fxrate
			end
	end px 
INTO #CVdaMV    
from HistAnalytic had inner join         
	ConvScheduleItem csi on had.idInstStatic = csi.idInstStatic and          
	had.dtDate between '01 Apr 2006' and '01 Jul 2006'     
inner Join         
	Convertible cb on csi.idInstStatic = cb.idInstStatic
inner join
	InstStatic ins on cb.idinststatic = ins.idinststatic
inner join
	InstStatic insu on cb.idInstUnderlying = insu.idinststatic  
left Join         
	HistOfficialMark homu on cb.idInstUnderlying = homu.idInstStatic and         
	homu.dtmark between '01 Apr 2006' and '01 Jul 2006'
Left Join (   
	select         
		case             
			when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
			else 1/((hfx.dbBid+hfx.dbAsk)/2)         
		end fxRate,         
		case             
			when fx.idBaseCcy = 259 then fx.idOtherCcy             
			Else fx.idbaseCcy         
		end idCCy,
		hfx.dtmark dtFx
	from  fxrate fx inner join             
		HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and  
		hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'          
	where         
		fx.idbaseCcy = 259 or fx.idotherCcy = 259) fx on ins.idBaseCurrency = fx.idCCy and
		datediff(dd,fx.dtfx,had.dtDate)=0
Left Join (   
	select         
		case             
			when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
			else 1/((hfx.dbBid+hfx.dbAsk)/2)         
		end fxRate,         
		case             
			when fx.idBaseCcy = 259 then fx.idOtherCcy             
			Else fx.idbaseCcy         
		end idCCy,
		hfx.dtmark dtFx
	from  fxrate fx inner join             
		HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
		hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'     
	where         
		fx.idbaseCcy = 259 or fx.idotherCcy = 259) fxu on insu.idBaseCurrency = fxu.idCCy and
		datediff(dd,fxu.dtfx,had.dtDate)=0
where
	datediff(dd,homu.dtmark,had.dtDate) = 0 
Group by         
	had.idInstStatic,         
	CSI.dbOrdinaryConversionRatio,
	had.dtDate,         
	case 
		when homu.dblongprice is null then 0 
		else homu.dblongprice /
			case 
				when ins.idbaseCurrency = insu.idBaseCurrency then 1
				else fx.fxrate/fxu.fxrate
			end
	end,         
	cb.dbFaceValue

-- Create Convertible Asset Swap Temp Table
select         
	had.idInstStatic idInstStatic,         
	CSI.dbOrdinaryConversionRatio cratio,         
	cb.dbFaceValue Denom,           
	sum (case had.idcalcField when 352 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Gamma,        
	sum (case had.idcalcField when 356 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Vega, 
	sum (case had.idcalcField when 353 then (CASE WHEN had.dbValue is null then 0 else had.dbValue end) else 0 end ) Theta,         
	homu.dtmark dtGreek,
	case 
		when homu.dblongprice is null then 0 
		else homu.dblongprice /
			case 
				when ins.idbaseCurrency = insu.idBaseCurrency then 1
				else fx.fxrate/fxu.fxrate
			end
	end px   
INTO #CbasDaMV   
from HistAnalytic had inner join         
	ConvertibleAssetSwap cas on had.idInstStatic = cas.idInstStatic  and
	had.dtDate between '01 Apr 2006' and '01 Jul 2006'
inner Join         
	ConvScheduleItem csi on cas.idConvertible = csi.idInstStatic     
inner Join         
	Convertible cb on csi.idInstStatic = cb.idInstStatic    
inner join
	InstStatic ins on cb.idinststatic = ins.idinststatic
inner join
	InstStatic insu on cb.idInstUnderlying = insu.idinststatic   
inner Join         
	HistOfficialMark homu on cb.idInstUnderlying = homu.idInstStatic and
	homu.dtmark between '01 Apr 2006' and '01 Jul 2006' and
	datediff(dd,homu.dtmark,had.dtDate) = 0
Left Join (   
	select         
		case             
			when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
			else 1/((hfx.dbBid+hfx.dbAsk)/2)         
		end fxRate,         
		case             
			when fx.idBaseCcy = 259 then fx.idOtherCcy             
			Else fx.idbaseCcy         
		end idCCy,
		hfx.dtmark dtFx
	from  fxrate fx inner join             
		HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and  
		hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'          
	where         
		fx.idbaseCcy = 259 or fx.idotherCcy = 259) fx on ins.idBaseCurrency = fx.idCCy and
		datediff(dd,fx.dtfx,had.dtDate)=0
Left Join (   
	select         
		case             
			when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
			else 1/((hfx.dbBid+hfx.dbAsk)/2)         
		end fxRate,         
		case             
			when fx.idBaseCcy = 259 then fx.idOtherCcy             
			Else fx.idbaseCcy         
		end idCCy,
		hfx.dtmark dtFx
	from  fxrate fx inner join             
		HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
		hfx.dtmark between '01 Apr 2006' and '01 Jul 2006'     
	where         
		fx.idbaseCcy = 259 or fx.idotherCcy = 259) fxu on insu.idBaseCurrency = fxu.idCCy and
		datediff(dd,fxu.dtfx,had.dtDate)=0     
Group by         
	had.idInstStatic,         
	CSI.dbOrdinaryConversionRatio, 
	homu.dtmark,       
	case 
		when homu.dblongprice is null then 0 
		else homu.dblongprice /
			case 
				when ins.idbaseCurrency = insu.idBaseCurrency then 1
				else fx.fxrate/fxu.fxrate
			end
	end,                 
	cb.dbFaceValue

-- Create temp FX Table
select         
	case             
		when fx.idbaseCcy = 259 then (hfx.dbBid+hfx.dbAsk)/2             
		else 1/((hfx.dbBid+hfx.dbAsk)/2)         
	end fxRate,         
	case             
		when fx.idBaseCcy = 259 then fx.idOtherCcy             
		Else fx.idbaseCcy         
	end idCCy,
	hfx.dtmark dtfx 
INTO #SpotRate  
from  fxrate fx inner join             
	HistFxRate Hfx on fx.idFXRate = Hfx.idFXRate and             
	hfx.dtmark between '01 Apr 2006' and '1 Jul 2006'    
where         
	fx.idbaseCcy = 259 or fx.idotherCcy = 259

-- Run Main Query
select
	sum(case when vegacash >0 then vegacash else 0 end) PositiveVega,
	sum(case when vegacash <0 then vegacash else 0 end) NegativeVega,
	sum(case when GammaCash >0 then GammaCash else 0 end) PositiveGamma,
	sum(case when GammaCash <0 then GammaCash else 0 end) NegativeGamma,
	sum(case when thetaCash >0 then thetaCash else 0 end) PositiveTheta,
	sum(case when thetaCash <0 then thetaCash else 0 end) NegativeTheta,
	temp.book
From (
	Select 
	 	
		sum((case
				when ins.tiInstType in (10,19) then (hip.iquantity * #EqOpdaMV.Vega * #EqOpdaMV.noShs * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * #CvdaMv.vega * inp.dbcurrencyMult * inp.dbperDerivMult)
				When ins.tiInstType = 17 then (hip.iquantity * #CbasdaMv.vega * inp.dbcurrencyMult * inp.dbperDerivMult)
			end)
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else #SpotRate.FxRate 
		end) as VegaCash,
		sum(case
				when ins.tiInstType in (10,19) then (hip.iquantity * #EqOpdaMV.Gamma * #EqOpdaMV.noShs * #EqOpdaMV.px * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * #CvdaMv.Gamma * #CvdaMV.cratio * #cvdaMV.px)
				when ins.tiInstType = 17 then (hip.iquantity * #CbasdaMv.Gamma * #CbasdaMV.cratio * #cbasdaMV.px)
			end
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else #SpotRate.FxRate 
		end) as GammaCash,
		sum(case
				when ins.tiInstType in (10,19) then (hip.iquantity * #EqOpdaMV.Theta * inp.dbcurrencyMult)
				when ins.tiInstType = 2 then (hip.iquantity * #CvdaMv.Theta * inp.dbcurrencyMult)
				when ins.tiInstType = 17 then (hip.iquantity * #CbasdaMv.Theta * inp.dbcurrencyMult * #cbasdamv.denom)
			end
		/ 
		case    
			when ccy.ch3isoCode = 'EUR' then 1    
			Else #SpotRate.FxRate 
		end) as ThetaCash,
		hip.dtInstpos dtRun,
		st.vc40name,
		case 
			when bk.vc40name = 'EVENT CATALYST P' then 'Event Catalyst'
			when bk.vc40name = 'EVENT CATALYST' then 'Event Catalyst'
			when bk.vc40name = 'MACRO HEDGE' then 'Event Catalyst' 
			when bk.vc40Name = 'VOL ARB' then 'Vol Arb'
			when bk.vc40Name = 'EVENT DERIV' then 'Event Catalyst' 
			when bk.vc40Name = 'LONG_SHORT' then 'LS - Pairs'
			when bk.vc40Name = 'RESEARCH' then 'LS - Pairs'
			when bk.vc40Name = 'SH BOOK' then 'LS - Pairs'
			when bk.vc40Name = 'CONVERTIBLES' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name <> 'CA_EVENT' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name = 'CA_EVENT' then 'Event Catalyst'
			when bk.vc40Name = 'CAPITAL STRUCTURE' then 'Capital Structure'
			when bk.vc40Name = 'CAPITAL STRUCTURE P' then 'Capital Structure'
			Else bk.vc40Name 
		end as Book
		
	FROM Strategy st inner join 
		book bk on st.idbook = bk.idbook 
	Inner join
		Position pos on pos.idStrategy = st.idStrategy and
		pos.idfund = 7
	inner Join 
		HistInstPos hip on hip.idPosition = pos.idPosition and  
		hip.tiIndelibleReport = 0 and  
		hip.iquantity <>0 and
		hip.dtInstPos between '01 Apr 2006' and '01 Jul 2006' and
		datename(dw, hip.dtInstPos) not in ('Saturday', 'Sunday')
	inner Join 
		inststatic ins on pos.idinststatic = ins.idinststatic
	inner Join 
		InstType itp on ins.tiInstType = itp.idInstType
	inner join 
		InstPricing inP on ins.IdInstStatic = inP.idInstStatic and 
		inp.idCodeType = 2	
	inner Join 
		Currency ccy on ins.idBaseCurrency = ccy.idCurrency   
	Left Join #EqOpdaMV on pos.idInstStatic = #EqOpdaMv.IdInstStatic and
			datediff(dd,#EqOpdaMV.dtGreek,hip.dtInstPos)= 0
	left join  #CVdaMV on pos.idInstStatic = #CVdaMV.IdInstStatic and
			datediff(dd,#CVdaMV.dtGreek,hip.dtInstPos)=0
	left join #CbasdaMv on pos.idInstStatic = #CbasdaMv.IdInstStatic and
			datediff(dd,#CbasdaMV.dtGreek,hip.dtInstPos)=0
	Left Join #SpotRate on ins.idBaseCurrency = #SpotRate.idCCy and
			datediff(dd,#spotrate.dtfx,hip.dtInstPos)=0

	group by 
		st.vc40Name,
		hip.dtInstpos,
		case 
			when bk.vc40name = 'EVENT CATALYST P' then 'Event Catalyst'
			when bk.vc40name = 'EVENT CATALYST' then 'Event Catalyst'
			when bk.vc40name = 'MACRO HEDGE' then 'Event Catalyst' 
			when bk.vc40Name = 'VOL ARB' then 'Vol Arb'
			when bk.vc40Name = 'EVENT DERIV' then 'Event Catalyst' 
			when bk.vc40Name = 'LONG_SHORT' then 'LS - Pairs'
			when bk.vc40Name = 'RESEARCH' then 'LS - Pairs'
			when bk.vc40Name = 'SH BOOK' then 'LS - Pairs'
			when bk.vc40Name = 'CONVERTIBLES' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name <> 'CA_EVENT' then 'Convertibles'
			when bk.vc40Name = 'CA_BOOK' and st.vc40Name = 'CA_EVENT' then 'Event Catalyst'
			when bk.vc40Name = 'CAPITAL STRUCTURE' then 'Capital Structure'
			when bk.vc40Name = 'CAPITAL STRUCTURE P' then 'Capital Structure'
			Else bk.vc40Name 
		end) temp
group by temp.book
	
drop table #spotrate
drop table #CBASdaMV
drop table #CVdaMV
drop table #EQopDaMV
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-07-11 : 19:46:56
quote:
Originally posted by jrogers

I have tried creating temp tables instead of having the select statements after the left joins, this speeds the query up dramatically. But I need the query to be in one select statement, any ideas?


It doesn't make sense to say it has to be in one select statement.

If using temp tables performs better, do it.


CODO ERGO SUM
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2006-07-11 : 19:57:45
I agree they should be the same, but life isn't always like that! You might find that the size of your query means the optimiser does not investigate as many plans before giving up. In effect it says "if I look at any more I'll take longer to work out how to do it than just do it". That's the only reason I can see.

A couple of things to check:
- Do your where clauses significantly reduce the volume of data being processed? If so, make sure an index is being used. Is there a better way that does not use an expression, or uses a pre-calculated value?
- pretty much the same applies for your group by.
- Where you have expressions such as bk.vc40name = 'EVENT CATALYST P' then 'Event Catalyst' can you keep the encoded values for the grouping and push the final translation up into the select list?
- you keep translating null into 0. Can you make this a non-nullable field with a default of 0 instead? Could save an expression or two.

Unfortunately it looks like your database is not a very good fit for the questions you need to ask of it. That's not to say it's not correct for somebody's viewpoint of course. If you can find out more about it I recon a bit of rework could make your life easier.

Finally - check the query plan!
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-07-11 : 20:36:45
Quick tip: all of those DateDiff's in your criteria disallow any use of an index on any of those date columns. You have a lot of those. Are there times stored with your dates?

- Jeff
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-12 : 04:58:17
Thanks for all your answers..

Michael Valentine Jones - I entirely agree with you apart from the fact that I am actually going to be using this sql from MS Access/Excel and ADO, so I think I need one select query, But that is not for this forum. I just wanted to check that my sql was correct

LoZtInSpace - I agree if they were pre calculated values my life woud be a lot easier, unfortunatly not.
- Not sure how I could alter my group by statements. I can see that having calculated fields in a group by statement would slow down the query. any suggestions here would be great
-does not seem to enhance performance
-Unfortunatly it is not my database.. also the null fields will exsist, as for certain dates, there will be no data. But I need to investigate whether that matters in this instance
-On the subject of query plan.. I really dont know what it is telling me. My expertiese are in Excel/Access/vba and not sql server, so when I am presented with a query plan I am not exactly sure what it is telling me, or indeed if it is giving me any clues as to how to improve the query - any help on this subject may help, Are there any good websites?

jsmith8858- I did not realise that.. thats probably going to be a major factor here.. unfortunalty the date fields in each table has a time attached, as it is recording when each record was added (this is done in a batch job elsewhere - not under my control - ) I will see if I can get this changed or an additional field added with just date. but failing this, is there any other way apart from datediff, where I can link two tables together by date fields?
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2006-07-12 : 05:16:12
Hi, the fact that you're going to be accessing this from outside of sql server should not prevent you from using temp tables etc. You could put all of this into a stored proc and call it from there. In fact that's what I would suggest that you do, with the sub queries broken out into temp tables.

-------
Moo. :)
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2006-07-12 : 05:51:11
1. can you post the query and let us figure out it's slowness?
2. what sort of volumes are in the underlying tables?
3. qualifying all tables with dbo will help cache.
4. can you run create statements for the temp tables and put indices on them before filling them with data....should help the subsequent "select's"
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-12 : 06:11:03
Mr Mist. this is not my database so I cant create any stored procedures, that would seem to be the best solution - but unfortunatly not an option. I Will investigate whether I can create temp tables from ADO. This may be my solution

AndrewMurphy. - query is posted.. version 1 is slow.. it ran for over 40 minutes.. version 2 with the temptables ran in 1min 10 secs.. which is a very acceptable time. My problem is that I (may) need one SQL statement..
-tables are fairly large and growing on a daily basis.. upwards of 3million records in 2 of the tables and around 300k for a few others..
- I didnt realise this.. thanks I'll try this.
- I agree this would help, and I definitly would if I was going down the stored procedure route. If I can create tables via excel/access using ADO, then this would be a definit possibility.

I am just stumped as to why the temp table method is faster than the subquery method. is there anyway I can force the query to follow a certain method rather than have the query analyser decide?

oh, and thank you very much for your responses
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2006-07-12 : 06:22:39
quote:

I am just stumped as to why the temp table method is faster than the subquery method. is there anyway I can force the query to follow a certain method rather than have the query analyser decide?


As someone mentioned, it's possible that the length of the query and the number of possible routes is causing the optimizer to give up before trying all the possibilities, thereby running your query with a less than optimal plan. Hence why seperating the things out helps.

You could specify your own join and index hints, but you'd need to know which ones to specify. Maybe if you can find out the differences in the plans from the single query and temp table versions you could specify the relevant hints.

-------
Moo. :)
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2006-07-12 : 10:19:58
ooooops!!
""1. can you post the query PLAN and let us figure out it's slowness?"
Go to Top of Page

jrogers
Starting Member

34 Posts
Posted - 2006-07-12 : 10:43:51
Sorry to be an idiot, but I am not great on SQL server.. how do I save the plan. I have 2 problems.. actual plan only is shown after query is executed - original plan failed to execute after 40mins so I stopped it.. I can get an estimated execution plan.. but how do I save this?
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2006-07-13 : 08:09:42
1. text form can be posted direct here.
2. graphic form (I think) needs to be parked on an internet facing address....and linked to from here.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2006-07-13 : 08:32:44
One more thing....

In your Temp table solution, rather than using Select...Into, firstly create all temp tables you want by using Create Table statement & then use Insert into ...Select statement to insert data to reduce locks on tempdb.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-07-13 : 10:02:30
Here's what makes no sense to me:

Typically, on a INNER JOIN, you expect that you will have matching values in the columns you are joining on. This is often enforced with relationships that ensure that this is the case.

However, you have many joins between two tables involving Date columns, using the DateDiff(d,...)=0 expression in your WHERE clause to ignore the time part ... this to me makes no sense ... Are times really stored in these columns? If they are, then by definition these joins will not work accurately and will often produce 1:M results, or even M:M causing a huge mess. It just doesn't add up. I would suggest really checking out this data to see if indeed times are stored with these dates, and then to see if there are multiple entries per day in any of these rows. Because if there are, the entire thing will be a mess.

i.e., if you have:

Table1: 1/1/2003, 1/2/2003
Table2: 1/1/2003, 1/2/2003

and you join them, you get two rows back.

But if you have:

Table1: 1/1/2003 11:00AM, 1/1/2003 12:00AM, 1/2/2003 3:15 PM
Table2: 1/1/2003 8:00AM, 1/1/2003 5:12AM, 1/2/2003 9:00PM

(note the entries in red, which has a different time but for the same date as the previous one)

And you join them on a DateDiff(dd,,)=0 expression, then you will get back a not 3 rows (or maybe even you want two rows, since there are only two days there), but rather 6 due to a cross join! Resulting in not only bad performance but bad data.

So, I would really investigate this.

The joins, as written, imply to me that either :

a) times are not really stored, so you should just join on the date's themselves which will give you much better performance

or

b) times are stored, and if so, there should be a constraint of some type enforcing that only 1 entry per day per PK is allowed, otherwise you will get back bad data. I doubt this constriant is in place.... And if this is the case, then why bother storing times?

I do have an article here

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

explaining how easy it is to simply store your dates and times in separate columns, if indeed times are stored and are important to these entries.



- Jeff
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site