Performance issue with query

Question

Performance issue with query

yatish.patil

Ten Centuries

Points: 1268
More actions
March 1, 2010 at 11:54 pm

#211753

Hello All,
I am facing issue for Slow performance of one SP, I tried different ways to improve performance like Index, update statistics , rebuild index , code optimization etc. but still this SP takes more than 2 min to complete the execution. I hope some can help me on this issue.
IN this SP I am showing last 6 months history for data month by month, the history table is having more than 10 L records out of which I need to get only data for valid last 6 months
data added before 1 year can also be part of current month data in case whole process is not completed.
when i looked at the execution plan i found the attached execution plan taking n more time, the whole SP takes more than 2 min time and this code as well takes more 50 % in whole execution plan.
I have attached the Execution plan which takes more time.
Can anyone help me on this issue?
Thank you in advance.

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

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

arr.nagaraj SSCertifiable Points: 6518 More actions · Answer 1

Sure I will help you.

1) Please post the query in text format. Though I managed to look at your query from the Query plan, please post in text format as well.

2) Provide the function definition in a text format.

3) Also post approximate table sizes involved.

My initial reactions,

> Functions at times can kill the performance by functioning like a cursor. Try a solution by avoiding it ( if possible..perhaps temp table )

> CTE perform poorly as they don't reuse the result and I have found them effective only with recursion. Try a solution without CTE.

Anyways, these are my initial reactions. Provide the info requested to help better.

Regards,
Raj

http://Strictlysql.blogspot.com

yatish.patil Ten Centuries Points: 1268 More actions · Answer 2

Hello,

I have modified the query to remove the CTE, Please find code below

SELECTOH1.TeamHistoryId,

OH1.TeamId,

OH1.Classid,

OH1.TeamIsLost ,

OH1.closedate,

OH1.TeamChangeDate,

(CASE OP.[action]

WHEN 'D' THEN 0

ELSE op.price

END

) AS price

INTO #teamsHistory

FROM fnGetTeamWithMultiClassSupportForHistoryChanges(@login,@StudentsID,@IsNodeMultiClass) OH

JOIN TeamHistory OH1 ON OH.TeamId = OH1.TeamId

JOIN TeamHistoryLog OHL ON OH1.TeamHistoryId = OHL.TeamHistoryId

JOIN TeamFeeHistory OP ON OHL.TeamFeeHistoryId = OP.TeamFeeHistoryId

JOIN Fees P on op.Feeid = p.Feeid AND P.Feeclassid = @Feeclassid

OPTION(Maxdop 1);

Thank you for Your help

yatish

LutzM SSC Guru Points: 107049 More actions · Answer 3

Yatish,

is there any specific reason for reposting the same question that can be found here ??

Not only that you split answers across multiple threads, you'll also have several people working independent on the same subject.

Please focus on one thread for one subject!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ramukar LivingForSQLServer SSCommitted Points: 1749 More actions · Answer 4

hey here is my comments

if your key tables are huge and query returns huge number of records

or too many non unique values in keys, u really cant control Scans. all the time, we really shouldnt expect Lacs of NCI scan & CI loopup. sql may go scan directly.

personally i got some improvement by keeping history tables slim, (nolock) and having index key in Join/Where columns having more unique values..and other activitites like keeping some history tables NCI in different drives or adressing other key performance issues in table may help to reduce IO/CPU in long run..