performance tuning - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

performance tuning

Author

Topic

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-13 : 08:53:02

Hi experts,
I've run sql profiler with %processor time counter .it showed a large value of 75
so please give me steps to tune the long running query with high cpu utilisation.

thanks in advance

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-03-13 : 19:09:03

Post your query.

mk_garg

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-03-14 : 04:14:01

Hi Garg, please find the query

SELECT COMPANY_ID,COMPANY_NAME,COMPANY_ID BRANCH_ID,COMPANY_NAME BRANCH_NAME,UNIQ_ID,DOCUMENT_TYPE,TRANS_ID, DOCUMENT_NO,DOCUMENT_DATE,SNO,POSTING_DATE,ACCOUNT_ID,ACCOUNT_NAME,ACCOUNT_CODE, DEBIT_AMOUNT,CREDIT_AMOUNT,NARRATION,REMARKS FROM VRPT_FIN_GL_ACCOUNTSDATA WHERE POSTING_DATE Between CONVERT(CHAR(10),'03/01/2006',120) and CONVERT(CHAR(10),'03/14/2007',120) And COMPANY_ID In ('03E347F0-410C-4AD2-8151-5EB88EAB0FB9')

UNION ALL

SELECT COMPANY_ID,COMPANY_NAME,COMPANY_ID BRANCH_ID,COMPANY_NAME BRANCH_NAME,UNIQ_ID,DOCUMENT_TYPE,TRANS_ID, DOCUMENT_NO,DOCUMENT_DATE,SNO,POSTING_DATE,ACCOUNT_ID,ACCOUNT_NAME,ACCOUNT_CODE, SUM(DEBIT_AMOUNT),SUM(CREDIT_AMOUNT),NARRATION,REMARKS FROM VRPT_FIN_GL_ACCOUNTSOPEN WHERE POSTING_DATE1<'2006/3/1' AND COMPANY_ID In ('03E347F0-410C-4AD2-8151-5EB88EAB0FB9') GROUP BY COMPANY_ID,COMPANY_NAME,UNIQ_ID,DOCUMENT_TYPE,TRANS_ID,DOCUMENT_NO,DOCUMENT_DATE,SNO,POSTING_DATE,ACCOUNT_ID,ACCOUNT_NAME,ACCOUNT_CODE,NARRATION,REMARKS

thank you very much

Vinod

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:20:53

Put an index on POSTING_DATE column.
Put an index on COMPANY_ID column.

SELECT		COMPANY_ID,
		COMPANY_NAME,
		COMPANY_ID AS BRANCH_ID,
		COMPANY_NAME AS BRANCH_NAME,
		UNIQ_ID,
		DOCUMENT_TYPE,
		TRANS_ID,
		DOCUMENT_NO,
		DOCUMENT_DATE,
		SNO,
		POSTING_DATE,
		ACCOUNT_ID,
		ACCOUNT_NAME,
		ACCOUNT_CODE,
		DEBIT_AMOUNT,
		CREDIT_AMOUNT,
		NARRATION,
		REMARKS
FROM		VRPT_FIN_GL_ACCOUNTSDATA
WHERE		POSTING_DATE >= '20060301'
		AND POSTING_DATE < '20070315'
		AND COMPANY_ID = '03E347F0-410C-4AD2-8151-5EB88EAB0FB9'

UNION ALL

SELECT		COMPANY_ID,
		COMPANY_NAME,
		COMPANY_ID AS BRANCH_ID,
		COMPANY_NAME AS BRANCH_NAME,
		UNIQ_ID,
		DOCUMENT_TYPE,
		TRANS_ID,
		DOCUMENT_NO,
		DOCUMENT_DATE,
		SNO,
		POSTING_DATE,
		ACCOUNT_ID,
		ACCOUNT_NAME,
		ACCOUNT_CODE,
		SUM(DEBIT_AMOUNT),
		SUM(CREDIT_AMOUNT),
		NARRATION,
		REMARKS
FROM		VRPT_FIN_GL_ACCOUNTSOPEN
WHERE		POSTING_DATE1 < '20060301'
		AND COMPANY_ID = '03E347F0-410C-4AD2-8151-5EB88EAB0FB9'
GROUP BY	COMPANY_ID,
		COMPANY_NAME,
		UNIQ_ID,
		DOCUMENT_TYPE,
		TRANS_ID,
		DOCUMENT_NO,
		DOCUMENT_DATE,
		SNO,
		POSTING_DATE,
		ACCOUNT_ID,
		ACCOUNT_NAME,
		ACCOUNT_CODE,
		NARRATION,
		REMARKS

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 05:28:20

Also if those all fields in the GROUP BY are there just because you want it in the SELECT statement, then check this article for proper use of GROUP BY:

[url]http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 05:31:35

Dear Peso, already company_id is a primary key. so already clustered index is there by default

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:32:56

Seems to me that first query returns last 12 months of data,
and second query returns all previous data.

I will try to not think of the size (and number of records) for the returned resultset.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:34:28

quote:
Originally posted by rajyalakshmi

Dear Peso, already company_id is a primary key. so already clustered index is there by default

Great!
Since I don't have information about the DDL, I suggested it becuase it can speed up your query.
But you already have a index on the column, so all is well for that index.
How about the other index?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 05:35:57

Also it seems that Company_ID has UNIQUEIDENTIFIER as a data type and it doesn't seem to be good choice for index.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:37:13

Put clustering on the POSTING_DATE instead.
You will benefit more from that.

Peter Larsson
Helsingborg, Sweden

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 06:26:49

but VRPT_FIN_GL_ACCOUNTSOPEN is a view.so need to create index on view.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-14 : 06:29:02

1. Eliminate the need for the CONVERT. What are you converting a date to TEXT? Store + USE dates in dateformat! CONVERT reduces ability to use indices. In this case the CONVERT seems to add no value, and may be causing (performance) harm.

2. You forgot to say that the definitions above are "partitioned views"....as seems to be the case from the execution plan you emailed to me.

3. Please post all communications WITHIN the forums. eMails to forum members should be used for SOCIAL reasons.

4. Split the query into 2 parts to see which side is "really" the costly part!

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 07:10:08

after eliminating convert the query execution is reduced to 1.30 min from 5 minutes.so performance is increased.please suggest me more to bring the time to atleast 10 sec

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 07:11:40

Do you read all answers given to you here?

Peter Larsson
Helsingborg, Sweden

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 07:20:13

couldnot create index on VRPT_FIN_GL_ACCOUNTSOPEN view POSTING_DATE,DOCUMENT_DATE have no values in view .The view is

create VIEW VRPT_FIN_GL_ACCOUNTSOPEN AS

SELECT 'OpenBal' UNIQ_ID, VFLED.BRANCH_ID, VIEWB.NAME BRANCH_NAME,VIEWC.ORG_ID COMPANY_ID,VIEWC.NAME COMPANY_NAME,
'OPBAL' DOCUMENT_TYPE, 'OPENTRANSID' TRANS_ID, 'Opening Balance'DOCUMENT_NO,0 SNO, '' POSTING_DATE,
'' DOCUMENT_DATE,
VFLED.ACCOUNT_ID, ACMST.ACCOUNT_CODE,ACMST.ACCOUNT_NAME,
VFLED.DEBIT_AMOUNT, VFLED.CREDIT_AMOUNT, VFLED.BALANCE,
'' INSTRUMENT_NO,'' INSTRUMENT_DATE,
'Brought Forward Balance' NARRATION, 'Brought Forward Balance' REMARKS, VFLED.POSTING_DATE1
FROM VRPT_FIN_LEDGERSDATA VFLED
INNER JOIN VFINANCEACCOUNTS ACMST ON ACMST.ACCOUNT_ID = VFLED.ACCOUNT_ID
LEFT OUTER JOIN VREP_FIN_HEADERSDATA VHDAT ON VHDAT.TRANS_ID=VFLED.TRANS_ID
INNER JOIN vOrganization VIEWB ON VIEWB.ORG_ID = VFLED.BRANCH_ID
INNER JOIN vOrganization VIEWC ON VIEWB.Top_Parent = VIEWC.Org_Id

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 07:21:57

yes i could read 14 replies in the sequence here

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-14 : 07:25:50

Peso,what do u think I missed? or am i going in in different track?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 07:45:09

How about (clustered) index on POSTING_DATE columns?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-03-14 : 07:45:12

Looks to me like VRPT_FIN_GL_ACCOUNTSDATA is a horizontal partition view, and therefore you need to make sure that you have met all the requirements for a query on a horizontal partition view - otherwise SQL Server will query all the tables and that will, obviously, take a long time. For SQL 2000 the requirements are:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Requirements%20for%20Horizontal%20Partitions

Kristen

rajyalakshmi
Starting Member

16 Posts

Posted - 2007-03-15 : 05:47:19

Kristen didnot get u.what could be my next step????????i am boring u?????????????

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-15 : 06:28:44

"Hi Andrew, why u stopped ur suggestions.Please give me solutions for this long running query.I removed the conert function .without indexing can u suggest some steps. "

1. Please NOTE comment below
"3. Please post all communications WITHIN the forums. eMails to forum members should be used for SOCIAL reasons.

2. With regard to your specific comment, the last time I looked at my paycheck I didn't see anything from you on the credit side. What info I have provided to date has been VOLUNTEERED FREELY. I have an employer at this end who is looking for work to be done - since he pays me, he calls the tune!

3. PESTER POWER doesn't work from my 4yr old son...it certainly won't work for you. Take the info we (all) have given as a push towards a solution. We do NOT setout to solve all your problems. You must invest some time and effort yourself. We have made suggestions, some of which you have ignored....and yet you demand more. I think you misunderstand the way this community works - we help you to help yourself IF we have some spare time AND IF we are so inclined AND IF the problem seems interesting AND IF you look to be making an effort to help yourself. Break ANY of these rules/conventions and your return will fast diminish.

4. Sometimes we cannot solve your problems - sometimes you either have to live with them or else go PAY somebody LOCALLY to come in and provide expert advice. Remember even FREE advice has a cost - it MAY not solve your problem!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources