Discovering the Performance of a Query

Question

Post reply

Discovering the Performance of a Query

Rofty

SSCrazy

Points: 2585
More actions
August 13, 2010 at 1:10 am

#97249

Hey Guys
Basic Background:
LineUp Table contains a transaction, which is split up into n-amount of records into the Holding table. Basic Allocation to Investments of stocks Bought and Sold.
Tables Sizes
tblHolding - 571236 records
tblLineUp- 51858 records
Indexes:
Basic Clustered Indexes
Holding1 | LineUp1
Holding2 | LineUp1
Holding3 | LineUp1
Holding4 | LineUp1
I have UD function that I use to calculate volume allocated to an Investment from a specific Group of LineUps (Leg) by joining the two table and specifying the parameters in the where clause:
SELECT @Volume SUM(Vol)
FROM ADMNS.tblHolding
INNER JOIN EXECS.tblLineUp ON tblLineUp.LineUpkey = tblHolding.LineUpKey
WHERE LegKey = @LegKey AND HoldingTypeKey = 2
AND (@InvestmentKey IS NULL OR InvestmentKey = @InvestmentKey)
RETURN ISNULL(Volume,0)
This function is used in a procedure where we step through about 6000+ LineUp records and use it to calculate what has been allocated and what still needs to be allocated.
Problem is that this process can take up to 50min to run. I realize that there are several other factor that could play a role in this processing time. But I have narrowed it down to specific functions of which the above one is part of them.
I need to know how I see where in this function's T-SQL I can make improvements for performance or i need to make adjustments to my Database Structure
Please see attached a execution plan for this function.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hunterwood SSCrazy Points: 2074 More actions · Answer 1

Hi,

acording to the plan there is an index missing to solve the query efficient. You could try to add this index:

CREATE INDEX IX_HTK_IK_LUK ON tblHolding

(HoldingTypeKey, InvestmentKey, LineUpKey) INCLUDE (Vol)

/Markus

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Can you post the whole procedure?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rofty SSCrazy Points: 2585 More actions · Answer 3

It a really big procedure.

Problem with indexes (as far as I found out) is that you have to plan them carefully. They can sometimes be useless or even harmful to performance.

I discover this by taking a Index Recommendation from the Engine Tuner Adviser. Index didn't make any contribution to performance. So i took the index, altered the order of the indexed columns, added Included Columns, fiddled with fill factors ... and BAM! ... performance increase 13x.

So indexing seem to and art, just need the right brush.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

roelofsleroux (8/13/2010)
It a really big procedure...

Can you post the part of the procedure which utilises the function?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The reason I'm asking is this:

roelofsleroux (8/13/2010)
This function is used in a procedure where we step through about 6000+ LineUp records and use it to calculate what has been allocated and what still needs to be allocated.

I want to see how you are "stepping through", because it's most likely this which is causing the procedure to run so slowly.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden