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
 Site Related Forums
 The Yak Corral
 Keep the spirit up, or something...

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-15 : 03:02:35
Difference between set-based and non-setbased programming?

"And so my fellow DBAs, ask not what your rows can do for you - ask what you can do for your columns."


E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2009-04-15 : 05:02:24
what the hell??

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-15 : 05:05:26
Most programmers are used to think row-based.
To transit to database programmers they need to start thinkning columnwise.

So I used the inaugural speech by JFK to write a line which can help programmers keep in touch with set-based thinking.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2009-04-15 : 05:28:36
ahhhh ok that makes some sense.

having problems again, are we?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-15 : 05:33:55
Well, you know some of them.

Let's just say I am tired of banging my head against the wall argumenting for set-based solutions in a CRM system having roughly a million customers and 75 million receipts.
The current situation is like CURSOR is the only acceptable, maintainable and sustainable solution.

Not long ago I tuned a query down from 9 hours and 56 minutes down to 28 seconds with proper set-based solution.
The answer was that "the management team doesn't understand the code you wrote, so we'll keep the CURSORS."

Do I need to say the CURSORs are nested too? Three levels deep...

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-04-15 : 05:41:09
And the moral of the story (according to Homer) is:

"never try"


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-04-15 : 05:47:02
quote:
Originally posted by Peso
The other day I tuned a query down from 9 hours and 56 minutes down to 28 seconds with proper set-based solution.
The answer was that "the management team doesn't understand the code you wrote, so we'll keep the CURSORS."


I know exactly how you feel. Had a similar situation recently while load testing our payroll system -- the loop was retained in the end with only minor enhancements because they thought my solution couldn't be maintained by the development team. Sigh.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-15 : 05:48:27
I. Need. A. New. Job.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2009-04-15 : 11:07:11
quote:
Originally posted by Peso


Not long ago I tuned a query down from 9 hours and 56 minutes down to 28 seconds with proper set-based solution.
The answer was that "the management team doesn't understand the code you wrote, so we'll keep the CURSORS."


I recently tweaked a cursor proc to use a set based solution, as well as appropriate indexes. Processing time went from 1+ hours to 1.5 minutes. I was asked "How did you do that? Let me see the code". I showed them but will they change their style going forward.? I doubt it, but that's OK. It keeps my paychecks coming in......

Terry

-- Procrastinate now!
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2009-04-15 : 11:49:45
Fortunately I don't have to deal with developers like that, but anytime I meet someone who defends cursors I basically give them my reply here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118835

If they still don't get it I'll actually make them use tweezers.
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts
Posted - 2009-04-15 : 13:20:45
Book-marked and WILL be in my posts going forward. Thanks Rob.

Terry

-- Procrastinate now!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2009-04-15 : 13:52:56
The sugar analogy is good, but I like to use beer:

With a cursor, you go to the store, buy a beer, bring it home, drink it, go to the store, buy a beer, bring it home, drink it, and so on.

With set based, you go to the store, buy a case of beer, bring it home, get one from the case, drink it, get another from the case, drink it, and so on.


CODO ERGO SUM
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2009-04-15 : 14:00:36
It's still a cursor though. Client-side, but still a cursor.

Now, if you get a KEG of beer and drink it all straight down, THAT'S a set-based operation.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2009-04-16 : 05:14:14
quote:

Now, if you get a KEG of beer and drink it all straight down, THAT'S a set-based operation.

LOL -- doesn't sound like a transaction with any chance of rollback.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts
Posted - 2009-04-16 : 14:28:24
Right now I am building interfaces for our production system to a canned scheduling system.

1. Our side clears and populates 3 SQL tables with contact and billing information from our contact manager and accounting tables.
test file 2000 records takes 1.1 seconds incuding the call.

2. Their system makes ODBC calls from their front end to SQL tables then imports data across 3 other SQL tables with the same layout(if I update their tables directly we lose all tech support and updates)

test file import same 2000 records 19 minutes 14 seconds.

Still cant get them to see how stupid this is.



Jim
Users <> Logic
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2009-04-16 : 14:32:55
Get them to use IN LINE SQL, because it is modern and the way to do it right!
I will bet your 1.1 seconds uses ancient Stored Procedure methods.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2009-04-16 : 15:39:44
Does anyone remember this gem:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

I think I gave up reading it about 3 paragraphs in, because:



And there's a larger version of it here (the image, not the article):

http://www.plognark.com/Art/Sketches/Blogsketches/2008/thestupiditburns.jpg
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2009-04-23 : 09:47:12
quote:
Originally posted by Peso
Let's just say I am tired of banging my head against the wall argumenting for set-based solutions in a CRM system having roughly a million customers and 75 million receipts.
The current situation is like CURSOR is the only acceptable, maintainable and sustainable solution.
I came in a little late here but I gotta say that for me this has never been more true. Remember the eav-model-thingy I'm working with (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118113)...? "Cursor-hell" isn't even remotely close to cover the problem I have right now...I sooo wanna tell you about the funky solution to create id's and the trigger madness and of course the deadlocking but I'm not. I'm just gonna leave it aand dig myself a really big ditch which I'm gonna lay down in and then I'll just die. What a great day it is indeed.

- Lumbago
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 09:53:35
Oh yes, now I remember.
Are you done with acceptance tests?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2009-04-23 : 10:07:38
Well..."someone" decided not to care all that much about performance tests so we just started using it full-blown in production. We didn't even perform all the regular test-scenarios. But what the hell...it's just customers and the future of the company, and that not really a big deal is it? *sigh*

- Lumbago
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-23 : 10:11:23
As long as it can't fall back on you, it's ok.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site