slow running query. any ideas? execution plan doesn''t yield much info.
-
this is sort of a hot one right now. I'm hoping another set of eyes might see something I'm not seeing. What kinds of other info can I offer? How about table counts for significants:
select count(*) from dbo.fnPositionS(null)
4911
select count(*) from tbMark
484658
select count(*) from tbDataset
1351
select count(*) from tbInstTranche
2215
select count(*) from tbInst
2099
declare @InstCompanyName varchar(255)
set @InstCompanyName = 'YAGEO'
select
Strategy = S.BizStrategyName,
Ticker = I.SevernTicker + ITT.EnumName,
Quantity = sum(P.Quantity),
Price = convert(decimal(20,2), M.Price),
Currency = right(C.InstCurrencyName, 3),
FXRate = FX.Price,
MarketValue = sum(dbo.fnPosMarketValue(P.Quantity, M.Price, I.PriceFactor, I.InstTypeEnumID, FX.Price, C.IsQuotedInverse))
from
dbo.fnPositionS(null) P
join tbInstTranche IT on P.InstTrancheID = IT.InstTrancheID
join tbInst I on IT.InstID = I.InstID
join tbEnum ITT on IT.InstTrancheTypeEnumID = ITT.EnumID
join tbBizStrategy S on P.BizStrategyID = S.BizStrategyID
left join tbMark M on I.InstID = M.InstID -- and M.MarkSourceEnumID = dbo.fnEnumS('MarkSource', 'EOD')
left join tbInstCurrency C on M.InstCurrencyID = C.InstCurrencyID
left join tbMark FX on C.InstCurrencyID = FX.InstID and FX.InstCurrencyID = 10
left join tbDataset D on D.DatasetID = M.DatasetID
join tbInstCompany IC on I.InstCompanyID = IC.InstCompanyID
where
I.InstTypeEnumID != dbo.fnEnumS('InstType', 'cy')
and IC.InstCompanyName = @InstCompanyName
and DatasetTypeEnumID = dbo.fnEnumS('DatasetType','MEOD')
group by
S.BizStrategyName,
I.SevernTicker + ITT.EnumName,
M.Price,
C.InstCurrencyName,
C.IsQuotedInverse,
FX.Price
having
sum(Quantity) != 0
order by
S.BizStrategyName,
I.SevernTicker + ITT.EnumName
-
Can you post the definition of:
1. dbo.fnEnumS
2. dbo.fnPosMarketValue
and
does the Quantity value can be negative ?
* Noel -
both are used in too many other stored code objects without consequence. They're quick and inconsequential. fnEnumS is a lookup translator and fnPosMarketValue just does some simple math.
create function fnEnumS (@EnumTypeName dtString, @EnumName dtString)
returns int
as
begin
declare @EnumID int
select @EnumID = E.EnumID
from tbEnum E
join tbEnumType ET on ET.EnumTypeID = E.EnumTypeID and ET.EnumTypeName = @EnumTypeName
where
E.EnumName = @EnumName
return (@EnumID)
end
create function fnPosMarketValue(
@Quantity dtQuantity,
@Price float,
@PriceFactor float,
@InstTypeEnumID int,
@FXRate float,
@IsQuotedInverse bit) returns float
as
begin
return @Quantity * @PriceFactor * isnull(case when @IsQuotedInverse = 1 then @FXRate else 1 / @FXRate end, 1) * @Price
end
-
>> They're quick and inconsequential <<
Functions are never inconsequential They force row by row processing in many cases, instead of set based and therefore should be used sparingly (meaning small sets or single call )
can you try:
declare @InstCompanyName varchar(255)
set @InstCompanyName = 'YAGEO'
select
Strategy = S.BizStrategyName,
Ticker = I.SevernTicker + ITT.EnumName,
Quantity = sum(P.Quantity),
Price = convert(decimal(20,2), M.Price),
Currency = right(C.InstCurrencyName, 3),
FXRate = FX.Price,
MarketValue = sum(P.Quantity * I.PriceFactor * isnull(case when C.IsQuotedInverse = 1 then FX.Price else 1 / FX.Price end, 1) * M.Price)
from
dbo.fnPositionS(null) P
join tbInstTranche IT on P.InstTrancheID = IT.InstTrancheID
join tbInst I on IT.InstID = I.InstID and
join tbEnum ITT on IT.InstTrancheTypeEnumID = ITT.EnumID
join tbBizStrategy S on P.BizStrategyID = S.BizStrategyID
left join tbMark M on I.InstID = M.InstID -- and M.MarkSourceEnumID = dbo.fnEnumS('MarkSource', 'EOD')
left join tbInstCurrency C on M.InstCurrencyID = C.InstCurrencyID
left join tbMark FX on C.InstCurrencyID = FX.InstID and FX.InstCurrencyID = 10
left join tbDataset D on D.DatasetID = M.DatasetID
join tbInstCompany IC on I.InstCompanyID = IC.InstCompanyID
where
I.InstTypeEnumID != dbo.fnEnumS('InstType', 'cy')
and IC.InstCompanyName = @InstCompanyName
and DatasetTypeEnumID = dbo.fnEnumS('DatasetType','MEOD')
-- If Quantity cannot be < 0 add this:
-- and Quantity > 0
group by
S.BizStrategyName,
I.SevernTicker + ITT.EnumName,
M.Price,
C.InstCurrencyName,
C.IsQuotedInverse,
FX.Price
having
sum(Quantity) != 0
order by
S.BizStrategyName,
I.SevernTicker + ITT.EnumName
check to see if you get index seeks on I.InstTypeEnumID and and DatasetTypeEnumID . If you do then don't need to do anything about the enum function if you don't then you will have to replace the function with a join
hth
* Noel -
get this... Index Wizard came through! 2 nonclustereds did the trick. one for Inst, one for Mark.
CREATE NONCLUSTERED INDEX [tbMark28] ON [dbo].[tbMark] ([InstID] ASC, [InstCurrencyID] ASC, [DatasetID] ASC, [Price] ASC )
CREATE NONCLUSTERED INDEX [tbInst31] ON [dbo].[tbInst] ([InstID] ASC, [InstCompanyID] ASC, [SevernTicker] ASC, [InstTypeEnumID] ASC, [PriceFactor] ASC )
there's a first time for everything, eh?
thanks for the help Noel.
-
Glad you work it out. I should have started asking for the indexes utilization in the first place
* Noel -
The row-based , set-based thing just confuses matters.
Indexes (in SQL Server) are on the column values, when you have a criteria/predicate in a SELECT that has a function on it then SQL server has no choice but to do a full index scan and then run the function on all the rows and then retrieve the results for comparison; unless you have another criteria/predicate on another column on the same table that does not have a function on it, in which case it will use the index on the other column to retrieve the rows and run the function on that subset of rows only. This is of course provided that the other column is selective enough that the CBO decides to use the index instead of table scan (so adding a index on a column like gender won't help much).
Index scan although better than a table scan can still be very slow, what you really want in a typical OLTP system would be index seeks.
To achieve that, you can create a computed column with the function as the formula. For example ColumnA will be Function(ColumnA). And then create a index on the new computed column. Next in your SELECT statement(s), use the computed column instead of the original column.
But this only works for deterministic columns and there are other implications when insert/update. A search on Creating Indexes on Computed Columns in SQL Server Books Online will give you the full details.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply