June 3, 2009 at 10:12 am
Popup from a trigger?
Sounds a bit Roy Rogers'ish to me :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
June 3, 2009 at 10:14 am
tosscrosby (6/3/2009)
Which volume are you referencing? Volume 1, question 53, pertains to maximum RAM for SQL 2000 EE 64bit.
That's the stumper . . .
j/k 😛 - volume 5, sorry.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 3, 2009 at 10:18 am
David Burrows (6/3/2009)
Popup from a trigger?Sounds a bit Roy Rogers'ish to me :w00t:
Watch out for the pop outs!
😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2009 at 10:19 am
Jack Corbett (6/3/2009)
--------------------------------------------------------------------------------
I'm going to have to agree with GSquared on this one. This definitely does not belong in a trigger
In fact, it belongs in the client-side validation before the request is ever sent to the server.
--------------------------------------------------------------------------------
Gail Shaw
What is this "Valley-day-shun" of which you speak? there is no mention of any such thing on The Net . . .
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 3, 2009 at 10:29 am
WayneS (6/3/2009)
SQL Server Magazine just published an article by Itzik Ben-Gan where under certain conditions, a cursor-based approach scales out better than a set based one. I'm curious as to others thoughts about this, specifically if there are other set-based methods that will perform better than what was used in the article.
Hmm, I cannot see the article...
[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]
June 3, 2009 at 10:43 am
RbarryYoung
The article link
http://www.sqlmag.com/Articles/ArticleID/101736/101736.html
but note:
This article is subscriber-only access
June 3, 2009 at 10:52 am
Steve Jones - Editor (6/3/2009)
I posted that one on Twitter. Wonder if anyone else has a better way to explain it.Popup from a trigger is a new one on me.
This sounds like a junior version of a question that we got on the local SIG mail list a couple of months ago:
Where I work, we were given a directive from the owner to expose our UI code to SQL Server so it can be accessed in a trigger. He doesn't want to rewrite everything in T-SQL. So...we found and have had some success with the system stored procedures sp_OACreate, sp_OAMethod, etc.
Our problem is how to return (multiple) data sets from the OLE object back to the trigger.
If we pass @@SPID from the trigger to the OLE object, the OLE object complains that the database connection handle is invalid and can't get any data.
If we grab a new database connection in the OLE object, then how do we get the results back to the trigger? Is there a way to pass results set(s) back to the trigger?
We were able to create a temp table in tempdb, and pass the temp table name back to the trigger, but is there a better way? If we resort to this, we will clutter tempdb with as many as 20 or so temp tables when our trigger fires.
No amount of discussion could seem to convince this fellow (or his boss?) that having a trigger engage in a side-conversation with the UI to exchange a few datasets was a bad idea. Assuming it was even possible, which is highly doubtful!
[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]
June 3, 2009 at 11:04 am
jcrawf02 (6/3/2009)
GSquared (6/3/2009)
Adam:I hate to sound so quantum (reference to today's cartoon from the front page), but I think the principle of publishing even when there are problems with it, is both right and wrong at the same time. Kind of like a lot of Wikipedia articles.
So, I'm observing the post, but can't tell. Is your cat dead or not?
I don't have a cat. I have two dogs, and one of them is still dopey from being sedated by the vet yesterday (teeth cleaning). Does that count?
- 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
June 3, 2009 at 12:01 pm
GSquared (6/3/2009)
I don't have a cat. I have two dogs, and one of them is still dopey from being sedated by the vet yesterday (teeth cleaning). Does that count?
The G^2 Canine Tartar Principle, got it. Much clearer, thanks! :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 3, 2009 at 12:05 pm
jcrawf02 (6/3/2009)
GSquared (6/3/2009)
I don't have a cat. I have two dogs, and one of them is still dopey from being sedated by the vet yesterday (teeth cleaning). Does that count?
The G^2 Canine Tartar Principle, got it. Much clearer, thanks! :hehe:
Glad I could help. 😀
- 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
June 3, 2009 at 2:08 pm
bitbucket (6/3/2009)
RbarryYoungThe article link
http://www.sqlmag.com/Articles/ArticleID/101736/101736.html
but note:
This article is subscriber-only access
My goodness, what a teaser of an "Executive Summary" and first three sentences of the article. It sounds as though he's setting up a running-total situation and claims that for sets of any real size (> 500 rows), a cursor solution will beat a set-based solution. I cannot believe that he means this in such a generalized way.
Executive Summary:
SQL Server’s optimizer handles set-based solutions to running aggregates with quadratic complexity (n2)with respect to partition size, whereas it handles cursor-based solutions with linear complexity. So in terms of performance, when dealing with small partition sizes—as many as a few hundred rows per partition—you’re better off with a set-based solution. However, when dealing with large partitions—more than 500 rows—you’re better off with a cursor-based solution.
===============================
Running aggregations are calculations that continue to accumulate measures over a sequence—typically temporal, and possibly within partitions. An example of obtaining a running aggregate is finding a running sum of quantity for each employee and month, from a table that holds a row for each employee and month with measures such as quantity and value. That is, for each employee and month, you return the sum of all quantities from the beginning of the employee’s activity until the current month. . . .
June 3, 2009 at 3:33 pm
john.arnott (6/3/2009)
bitbucket (6/3/2009)
RbarryYoungThe article link
http://www.sqlmag.com/Articles/ArticleID/101736/101736.html
but note:
This article is subscriber-only access
My goodness, what a teaser of an "Executive Summary" and first three sentences of the article. It sounds as though he's setting up a running-total situation and claims that for sets of any real size (> 500 rows), a cursor solution will beat a set-based solution. I cannot believe that he means this in such a generalized way.
Executive Summary:
SQL Server’s optimizer handles set-based solutions to running aggregates with quadratic complexity (n2)with respect to partition size, whereas it handles cursor-based solutions with linear complexity. So in terms of performance, when dealing with small partition sizes—as many as a few hundred rows per partition—you’re better off with a set-based solution. However, when dealing with large partitions—more than 500 rows—you’re better off with a cursor-based solution.
===============================
Running aggregations are calculations that continue to accumulate measures over a sequence—typically temporal, and possibly within partitions. An example of obtaining a running aggregate is finding a running sum of quantity for each employee and month, from a table that holds a row for each employee and month with measures such as quantity and value. That is, for each employee and month, you return the sum of all quantities from the beginning of the employee’s activity until the current month. . . .
I'll have to go back in this thread and see what the article is but, judging by the teaser you quoted, it was written by someone who doesn't have a full grip on the subject. First, it won't be an N2 complexity... it will be an ((N2+N)/2)+N, otherwise known as a "triangular join" with an additional "pass".
They are currect that a cursor would be linear... but that's terribly slow compared to the "Psuedo-Cursor" or "Quirky" update method. And, heh... Before anyone goes on a tear about that method, make sure you include properly written code that also generates at least a million rows of test data and the update actually fails to produce the desired results. Properly written, at this point, means don't do it on a partition, force parallism not to occur, force the usage of the clustered index, no joins allowed during the running aggregation, and force and exclusive tab lock.
I'm still working on the rewrite of the running total article and I've got a couple of surprises for folks.
--Jeff Moden
June 3, 2009 at 3:40 pm
WayneS (6/3/2009)
SQL Server Magazine just published an article by Itzik Ben-Gan where under certain conditions, a cursor-based approach scales out better than a set based one. I'm curious as to others thoughts about this, specifically if there are other set-based methods that will perform better than what was used in the article.
BWAA-HAA!!!! OH! THAT article! I can't tell if Itzik gets into the quirky update method from the tease or not. If he doesn't, then it's just amazing to me that no one but Phil, Robyn, and I have the hair to publish the way to do it and defend the method to the hilt.
I may have to spend the six bucks on a 1 month membership just so I can go see that article.
--Jeff Moden
June 3, 2009 at 3:53 pm
Jeff Moden (6/3/2009)
WayneS (6/3/2009)
SQL Server Magazine just published an article by Itzik Ben-Gan where under certain conditions, a cursor-based approach scales out better than a set based one. I'm curious as to others thoughts about this, specifically if there are other set-based methods that will perform better than what was used in the article.BWAA-HAA!!!! OH! THAT article! I can't tell if Itzik gets into the quirky update method from the tease or not. If he doesn't, then it's just amazing to me that no one but Phil, Robyn, and I have the hair to publish the way to do it and defend the method to the hilt.
I may have to spend the six bucks on a 1 month membership just so I can go see that article.
I cannot imagine that he does explain the quirky update method or, if he does mention it, that he endorses it. The summary is pretty clear that cursors are his choice.
Before popping $5 for the online access, I think I'll check the magazine section next time I'm at Borders. Not worth a special trip, but I'm there once a week anyway.
June 3, 2009 at 4:10 pm
john.arnott (6/3/2009)
Before popping $5 for the online access, I think I'll check the magazine section next time I'm at Borders. Not worth a special trip, but I'm there once a week anyway.
I forgot about THAT. There's one on the way home. I've just got to remembr to stop on the way. Thanks, John.
--Jeff Moden
Viewing 15 posts - 5,281 through 5,295 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply