Any Advice on Tuning

Question

Any Advice on Tuning

Mike Levan

SSChampion

Points: 14201
More actions
July 15, 2008 at 11:58 am

#193910

Proc attached as word doc, pls check.

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I have to ask, why is this a cursor? Why not a set-based upsert?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mike Levan SSChampion Points: 14201 More actions · Answer 2

these are very old procs written by some one else years back, trying to make it faster as they are in the daily jobs.

I was assuming this can be dont with simple insert,select statements

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Any Advice on Tuning

Yeah. Get rid of the cursor. I can't see anything that makes the cursor necessary.

Some information on what it's supposed to do, structures of tables involved and indexes would be of great help. As has been said before, the more info you give us about a problem, the more likely that you'll get useful and relevant help.

SELECT

UserID

INTO

#tmpUSER_IDs

FROM

LS_PORTAL.SBMGroupPortal.dbo.Users

UPDATE

LS_PORTAL.SBMGroupPortal.dbo.Users

SET

SolomonBuyerCode = ''

FROM

LS_PORTAL.SBMGroupPortal.dbo.Users u

INNER JOIN #tmpUSER_IDs ON #tmpUSER_IDs.UserID = u.UserID

IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#tmpUSER_IDs'))

DROP TABLE #tmpUSER_IDs

What is the point of this? You create a temp table with all the rows from LS_PORTAL.SBMGroupPortal.dbo.Users, you then update the rows in the same table that have a match in the temp table (which will be all of them), then you drop the temp table.

Unless I'm missing something very obvious, that can be simplified to just

UPDATE

LS_PORTAL.SBMGroupPortal.dbo.Users

SET

SolomonBuyerCode = ''

WHERE (LOWER(SUBSTRING(UserName,1,10)) = LOWER(RTRIM(@Buyer)

Is your database case sensitive?

WHERE (RTRIM(SolomonBuyerCode) = @Buyer)

SQL ignores trailing spaces when doing a comparison, so all the RTRIM is doing is potentially preventing an index seek.

UPDATE LS_PORTAL.SBMGroupPortal.DBO.Users SET SolomonBuyerCode = @Buyer WHERE ((FirstName + ' ' + LastName) = @BuyerName)

SET @rowsUpdated = @rowsUpdated + 1

SELECT @errorcode = @@ERROR, @rowc = @@rowcount

You do realise that the only thing you're checking the error number of is the SET statement?

@@Error returns the error code of the last statement (which in this case is SET @rowsUpdated = @rowsUpdated + 1)

I don't think that's quite what is intended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 4

This Solomon name on each table looks very familiar. Did I do something else with this database a while back?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GSquared (7/15/2008)
This Solomon name on each table looks very familiar. Did I do something else with this database a while back?

Probably. This isn't the first time Mike's posted a massive proc (and no other details) and asked for someone to optimise it.

http://www.sqlservercentral.com/Forums/FindPost524332.aspx

http://www.sqlservercentral.com/Forums/FindPost519376.aspx

http://www.sqlservercentral.com/Forums/FindPost531281.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Levan SSChampion Points: 14201 More actions · Answer 6

yeah u guys gace some ideas before and followed tha same here also, but still it takes like more than 5min.

thanks for your help guys

Mike Levan SSChampion Points: 14201 More actions · Answer 7

Probably. This isn't the first time Mike's posted a massive proc (and no other details) and asked for someone to optimise it.

you are right.

I was just posting full proc just to have full code for the members instead of bits of code so that they get complete idea of wht it is doing and the replies i got from u guys were also full satisfied. I thought i shud post in that way every time.

einman33 SSCrazy Points: 2813 More actions · Answer 8

Wow, I would never feel comfortable posting my internal schema object names to a public forum. Hoping you made the object names forum safe.

Might be of intrest to see what Statistics IO is saying.

Also, consider what can be done to get the link server LS_PORTAL out of the equation(if truly linked). Getting that dll involved always causes performance problems.

Tempdb yuck. See how much tempdb contention is going on when this proc is executed. Consider moving the temptables to subqueries and evaulate.

Like everyone is saying getting the cursor out of the mix will be a great help. If you have to use the cursor, make sure the cursor is fully opimized (non dynamic etc)

GSquared SSC Guru Points: 260824 More actions · Answer 9

GilaMonster (7/15/2008)
GSquared (7/15/2008)
This Solomon name on each table looks very familiar. Did I do something else with this database a while back?
Probably. This isn't the first time Mike's posted a massive proc (and no other details) and asked for someone to optimise it.
http://www.sqlservercentral.com/Forums/FindPost524332.aspx
http://www.sqlservercentral.com/Forums/FindPost519376.aspx
http://www.sqlservercentral.com/Forums/FindPost531281.aspx

Ah yes. I knew I was helping out on that last one, but had almost forgotten the second one. (Don't think I ever saw the first one.)

Mike, if this is the state of the database, based on what I'm seeing in this and the other two I've helped on, I seriously recommend hiring someone, perhaps a contractor, to go over these on your server, do a serious review of the system and its code, and rebuild as needed.

I've already spent a huge amount of time on your other two mega-cursors, and really can't spend more on yet another one of them. I don't know about anyone else, but it's really tough for me to do these without actually having some access to a dev copy of the database.

You will get better results, faster, by bringing someone in-house to do this. It will be worth it in the long run. There are people who do this for a living, and I'm sure you can find one to help you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon