Question for All DBAs (What can I learn to enahnce my career?)

Question

Question for All DBAs (What can I learn to enahnce my career?)

Viewing 2 posts - 16 through 16 (of 16 total)

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

MadAdmin (11/10/2015)
Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014
Yes.
Because the DB was then able to successfully serve up thousands of rows to the application, causing the app server to come to a standstill as it was doing loop de loopy stuff to thousands of objects in memory.
Before the optimization, the slow code was timing out and everyone was happy except the one person.
So yes.

Did they ever get around to fixing the app?

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

MadAdmin SSChampion Points: 11261 More actions · Answer 2

Jeff Moden (11/17/2015)
MadAdmin (11/10/2015)
Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014
Yes.
Because the DB was then able to successfully serve up thousands of rows to the application, causing the app server to come to a standstill as it was doing loop de loopy stuff to thousands of objects in memory.
Before the optimization, the slow code was timing out and everyone was happy except the one person.
So yes.
Did they ever get around to fixing the app?

No.

In my eyes the fix was simple.

But there was nested logic on how to call the DB based on previous Calls embedded in ASPX, according to the Devs, so they were scared to make the change, and business made a call not to fix it in favour of new features. /faceDesk

After delving into the problem and tracing, what happened is that when you searched on a page(search on any parameter you feel like), it would return an initial data set with 9 of the 10 columns.

Then ...... here is the exciting bit that made me do cartwheels and handstands.

They used the first 2 column values from the initial result set as parameters for another proc to populate a 10th column RBAR before presenting the 10 columns onto the Page.

In trying to understand the issue, it seems like they didn't know how to concatenate multiple rows into a delimited column with SQL (hint for XML path).

So they did that in the application and added the concatenated value into the 10th column.

So when you queried 'SMITH', it would return 10000 rows.

What this means is that the application called the DB 10001 times.

1 for the initial data set and 10000 times when it built the 10th column RBAR FTW!!!

This also means that the application had to build 10000 new values, so the app server was running low on memory, and the CPU went through a dramatic 100% plateau for minutes in some cases after the DB had succeeded in overwhelming the app server with data

It took all my energy in making this functionality not timeout on the DB as the page was timing out for many due to SQL timeout.

After the solution, for most users the page was fine.

Except when a certain person was working on the system and they had to do vague searches.

So now it was the application timing out due to SQL giving all it can in little to no time.

This allowed the app server to do all the bad things it was blocked from doing initially, but it was now an App Timeout.

General mayhem and anarchy and actually ......................instumental in my development.

PS. I just read through this Novel I wrote and it seems incoherent so I do apologise to the reader.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]