Performance issue with query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Performance issue with query

Performance issue with query

Rate Topic

Display Mode

Topic Options

Author

Message

yatish.patil

Posted Monday, March 01, 2010 11:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 16, 2012 8:49 AM
Points: 42, Visits: 237

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.

Post Attachments

EXEC Plan.sqlplan (23 views, 321.68 KB)

Post #874889

arr.nagaraj

Posted Tuesday, March 02, 2010 12:35 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191

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

Strictlysql.blogspot.com

Post #874897

yatish.patil

Posted Tuesday, March 02, 2010 3:03 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 16, 2012 8:49 AM
Points: 42, Visits: 237

Hello,

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

SELECT OH1.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

Post #874958

LutzM

Posted Tuesday, March 02, 2010 3:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #874986

Ramkumar (LivingForSQLServer)

Posted Tuesday, March 02, 2010 4:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 08, 2013 8:24 AM
Points: 153, Visits: 563

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..

Post #875001

« Prev Topic | Next Topic »

Permissions