More RBAR and "Tuning" UPDATEs

Question

More RBAR and "Tuning" UPDATEs

Viewing 15 posts - 61 through 75 (of 77 total)

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

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

Correct in some areas, Thomas... you can try the code I posted to verify...

The source data is queried once for each correlated subquery meaning that, with the correct index, 3 correlated subqueries do 3 Index Seeks. The target table is scanned once.

The source data is queried only once for the Derived table an it's a scan... the target table get's an index seek.

The correlated subquery method with all 3 columns in the presence of an index, takes about 1,470 milliseconds to execute on a million rows in the source table.

The derived table method with all 3 columns in the presence of an index, takes about 850 milliseconds to execute on a million rows in the source table.

I don't think I'd worry about splitting the tables since both handle a million rows of aggregates in about a second.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jeff Moden (3/16/2008)
Thanks, Matt... that'll teach me... I normally do a lot more research and "due diligence" before I post an article. I got in a hurry this time and didn't walk all the bases to see where they were before I got up to bat... it showed, too. I got tagged at 3 of the bases, spiked at home, and got in an argument with at least 2 people in the crowd. 😀

So it was more like a Hockey game than Baseball. (I don't think I know the emoticon for "toothless grin").

[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]

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 3

Jeff Moden (3/14/2008)
ALZDBA (3/14/2008)
I appreciate the feedback, Johan...
The problem is that most of the Devs nowadays are really GUI programmers that learned just enough SQL to be able to do necessarily RBAR Inserts/Updates/Deletes. They never really studied SQL so they really don't know enough to do anything about performance especially in any kind of multi-row or batch code. Even those that do know, GUI programming is really a "we need this NOW" environment and the Developers are rarely given the time to think about performance, never mind the basics. They frequently need to program by exception... if it works and no one squawks about it's performance once it's in production, think of all the time they saved... and the DBA just doesn't have time to be a 1 person code reviewing machine. Trust me, not my idea of the right way to do it but more often the truth than not.

And LINQ will also have its impact with regard to this behaviour :ermm:

It took me some time to work through all replies and consequences, but once again I love this article and its grown technical chain reaction :w00t:

btw: I've noticed that when you receive at website timeout for the reply, hitting the "back" button may also recouver your typed content, so it may be worth the effort.

I just copy all the text before hitting the submit button 😉

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

ALZDBA (3/17/2008)
btw: I've noticed that when you receive at website timeout for the reply, hitting the "back" button may also recouver your typed content, so it may be worth the effort.
I just copy all the text before hitting the submit button 😉

This technique has always worked for me also, but I have been using mostly Opera and Firefox. (yet another reason to support standards!) 🙂

[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]

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

rbarryyoung (3/17/2008)
Jeff Moden (3/16/2008)
Thanks, Matt... that'll teach me... I normally do a lot more research and "due diligence" before I post an article. I got in a hurry this time and didn't walk all the bases to see where they were before I got up to bat... it showed, too. I got tagged at 3 of the bases, spiked at home, and got in an argument with at least 2 people in the crowd. 😀
So it was more like a Hockey game than Baseball. (I don't think I know the emoticon for "toothless grin").

Hey - at least you haven't booed Santa (yet).... Stop by Philly some time when I'm up there visiting the family - I'll teach you THAT trick....:)

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

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

By the way, Barry - don't give up ranting...You're good at it! I will also be checking that link you mentioned previously (as to the summarized standards ).

We can always use another in the "volley for serve" dialogs we have here. Pile on!

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

heh... and I have a special batch of porkchops on the next one 😉

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

The article was good. I liked it even though it has some probs as pointed by some of my 'co-readers'. For me it started the base of RBAR.

Thanks Jeff 😛

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 9

Anirban Paul (3/18/2008)
The article was good. I liked it even though it has some probs as pointed by some of my 'co-readers'. For me it started the base of RBAR.
Thanks Jeff 😛

Thanks, Anirban... I do appreciate the feedback.

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

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

rbarryyoung (3/15/2008)
Anyway, for those who may actually be interested in what parts of Transact-SQL are standard and what may not be, here is a good readable summary site (yes, I do admit that the official Standards can be hard to read):
http://troels.arvin.dk/db/rdbms/

The best part of that is now I have a least a copy of the final draft of the ISO standards and they didn't cost anything. Thanks for that link, Barry.

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 11

Hmmm

I vaguely remember the furor about VB "dot Nyet" invalidating all code written in previous VB versions...

Walking up to Frankenstein's castle with torches and pitchforks ...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

J (3/19/2008)
Hmmm
I vaguely remember the furor about VB "dot Nyet" invalidating all code written in previous VB versions...
Walking up to Frankenstein's castle with torches and pitchforks ...

Yes, I was the guy in the front with the pitchfork and the Rocket Launcher.

[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]

Manie Verster SSCertifiable Points: 7152 More actions · Answer 13

Hi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

Manie Verster (3/20/2008)
Hi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:

Heh... depends... will meatloaf hold up in a slingshot? 😉

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

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

Jeff Moden (3/20/2008)
Manie Verster (3/20/2008)
Hi Jeff, and I thought you were an American. Americans only eats meatloaf or not?:hehe:
Heh... depends... will meatloaf hold up in a slingshot? 😉

My Great-Aunt Wilma's would....Course - she wasn't much of a cook....:)

How well it holds up in a slingshot is inversely proportional to how well it holds up with mashed potatoes and gravy....:hehe:

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