application running slow

Question

application running slow

bluepaperbags

SSCommitted

Points: 1738
More actions
April 15, 2008 at 12:28 am

#128611

A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?

Viewing 15 posts - 1 through 15 (of 21 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

First get the vendor to check what is the exact reason why the app is going slow. Is it the coding, cursor, locking etc.

Find out the reason and then solve it.

Keep us posted and we will help you.

"Keep Trying"

latheesh.jaya Mr or Mrs. 500 Points: 584 More actions · Answer 2

Check the execution plan for each SP.

Check the SQL profiler to know the period taken to execute the stored proceduress.

Please try to avoid cursors and try to implement set based queries.

bluepaperbags SSCommitted Points: 1738 More actions · Answer 3

Hey listen the vendor tested the application1 using the same database in his company's pc and it's working OK for him. That means it's not the application1.

We have lots of SP and applications running in our QA and PRD and they seem to be OK besides this evil application1. what could be wrong? Only experts (5+ years) pls.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Testing against a QA environment doesn't mean much if the QA environment doesn't match your set up. For all you know - their QA has 5 records in each table, and is likely not to have a bunch of other things running, etc..... Awfully hard to gauge anything that way.

Regardless - you didn't buy their QA environment, you bought something that needs to run in your environment. Not to be callous, but what do you care how well it runs in THEIR environment?

I would generate the execution plan AS IT HAPPENS ON YOUR PROD SERVER, send that to the vendor, and have them adjust the code or the environment as needed. (or recommend changes which you would have to implement).

Of course - you may find that they don't see eye to eye as to whose responsibility this is, since that is likely outlined somewhere in your licensing or support agreement.... But assuming they're responsible, then make them responsible.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bluepaperbags SSCommitted Points: 1738 More actions · Answer 5

bluepaperbags

SSCommitted

Points: 1738

April 15, 2008 at 3:52 pm

#802824

Thanks matt for your response

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Whoa... wait a minute... back the performance pony up! Let's review the facts...

1. escaleraroyal already stated that the vendor tested it in QA and it was slow...

2. The slow code has also already been traced to a proc that has a cursor in it that calls another proc.

I don't know about the rest of you folks, but I'd be on the vendor every day until they fixed the slow code that the vendor has already acknowledged as being slow in their own QA and has already been isolated and diagnosed. The cursor is part of the problem and I suspect there are other RBAR atrocities just waiting for a chance to grow up and be set-based code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (4/15/2008)
Whoa... wait a minute... back the performance pony up! Let's review the facts...
1. escaleraroyal already stated that the vendor tested it in QA and it was slow...
2. The slow code has also already been traced to a proc that has a cursor in it that calls another proc.
I don't know about the rest of you folks, but I'd be on the vendor every day until they fixed the slow code that the vendor has already acknowledged as being slow in their own QA and has already been isolated and diagnosed. The cursor is part of the problem and I suspect there are other RBAR atrocities just waiting for a chance to grow up and be set-based code. 😉

You're right - I just noticed there were two different descriptions....Original post mentioned "slow" and the second post mentioned "fine". I guess I kind of latched on to the secondary comment. If the vendor admitted to it being slow on their own QA, then absolutely - even more of a reason to get in their face.

quite honestly - I tell my vendors that I don't give a $%$%#$#@ how it plays on THEIR system, it has to play well on MY system. It's altogether too easy to trot out a pristine QA system with no activity against it and minimal rowcounts, and get astounding performance results; making it run well on a production system with other things actually competing for CPU time, RAM and disk access, altogether different issue.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bluepaperbags SSCommitted Points: 1738 More actions · Answer 8

update.

Vendor says the same applicationX runs fast in another company1 which has a higher volumne of data. He thinks it's our clustering server that is causing the issue as company1 does not use clustering server. I think I disagree with this cause our clustering server runs lots of stored procedures and SQL Jobs and other different applications and we do not have issues with them besides this applicationX.

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 9

Have you re-indexed, and / or updated the statistics? If not, it's worth a shot.

In any case, definitely push for a re-write with no cursors.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

escaleraroyal (4/16/2008)
update.
Vendor says the same applicationX runs fast in another company1 which has a higher volumne of data. He thinks it's our clustering server that is causing the issue as company1 does not use clustering server. I think I disagree with this cause our clustering server runs lots of stored procedures and SQL Jobs and other different applications and we do not have issues with them besides this applicationX.

Still, the only problem is that the vendor wrote crap code... break out your bat and make them fix it!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (4/16/2008)
escaleraroyal (4/16/2008)
update.
Vendor says the same applicationX runs fast in another company1 which has a higher volumne of data. He thinks it's our clustering server that is causing the issue as company1 does not use clustering server. I think I disagree with this cause our clustering server runs lots of stored procedures and SQL Jobs and other different applications and we do not have issues with them besides this applicationX.
Still, the only problem is that the vendor wrote crap code... break out your bat and make them fix it!

He could easily be describing Sharepoint here. You'll need an awfully big bat for that.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

It very well could be SharePoint... in which case, it wouldn't be a "fault"... it would be a "feature" 😛

Even if it is related to SharePoint, I suspect the problem is NOT with SharePoint itself... from the description, it's some 3rd party vendor trying to make SharePoint do something... heh... something very bad... RBAR... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Jeff Moden (4/16/2008)
It very well could be SharePoint... in which case, it wouldn't be a "fault"... it would be a "feature" 😛
Even if it is related to SharePoint, I suspect the problem is NOT with SharePoint itself... from the description, it's some 3rd party vendor trying to make SharePoint do something... heh... something very bad... RBAR... :hehe:

Actually, again, that describes Sharepoint itself. Have you ever looked at the stored procedures that ship with it? Or done a profile trace on it while it was in use? It's very scary & disturbing.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Do they look as bad as the sprocs found in SQL Server? I was amazed at how cruddy some of them are... sp_SpaceUsed is one of my favorites to pick on. They had the perfect opportunity to not only make it set-based, but with just a little imagination, they could have made it so it would work for all tables in the database in just about the same time that it works for just one. Instead, they chose RBAR on steroids.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions