Post reply

Are the posted questions getting worse?

  • February 24, 2010 at 11:58 am

    #1124466

    jcrawf02 (2/24/2010)

    Alvin Ramard (2/24/2010)

    Just be careful about loving the one ewe're with. 😀

    wow, I concede....whatever it is we're competing for.

    I wasn't competing. I was just rollin' with the flow. 😎


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • February 24, 2010 at 1:14 pm

    #1124547

    Paul White (2/24/2010)

    RBarryYoung (2/23/2010)

    Give it a go, Paul. The solution that we (independently) came up with is pretty darn efficient, so I would be particularly interested to see if a CLR solution come overcome the "call per row" penalty.

    As for the old Hammer saw (:-P), I actually would defend SQL as an appropriate and even preferred solution for this problem (barring really significant performance differences).

    Hey Barry,

    Can you guess what this:

    ...is a (one million row) execution plan for? 😛

    Paul

    I'll guess: build a tally table?

  • February 24, 2010 at 1:20 pm

    #1124551

    Paul White (2/24/2010)

    WayneS (2/24/2010)

    Something that you're about to write an article on?

    Oh highly amusing, Wayne! :laugh:

    But Paul.... I'm serious!

    BTW, I know you've told me before how you get the execution plans to display in a post, but I just can't get it to work for me. Would you care to explain it again? I noticed that on your post, it doesn't show as an attachment, yet inside the img tags is a link to an attachment? And it is just uploaded as a .sqlplan file? What else am I missing here?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 24, 2010 at 3:10 pm

    #1124629

    Paul White (2/24/2010)

    RBarryYoung (2/23/2010)

    Give it a go, Paul. The solution that we (independently) came up with is pretty darn efficient, so I would be particularly interested to see if a CLR solution come overcome the "call per row" penalty.

    As for the old Hammer saw (:-P), I actually would defend SQL as an appropriate and even preferred solution for this problem (barring really significant performance differences).

    Hey Barry,

    Can you guess what this:

    ...is a (one million row) execution plan for? 😛

    Paul

    Ummmm, ... A debt recovery plan for the average U.S. citizen?

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

  • February 24, 2010 at 3:17 pm

    #1124637

    Look at that, Barry needs 23 points for 9000 - big milestone.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 24, 2010 at 4:34 pm

    #1124673

    CirquedeSQLeil (2/24/2010)

    Look at that, Barry needs 23 points for 9000 - big milestone.

    Heh, I hadn't even noticed...

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

  • February 24, 2010 at 4:36 pm

    #1124674

    Paul White (2/24/2010)

    RBarryYoung (2/23/2010)

    Give it a go, Paul. The solution that we (independently) came up with is pretty darn efficient, so I would be particularly interested to see if a CLR solution come overcome the "call per row" penalty.

    As for the old Hammer saw (:-P), I actually would defend SQL as an appropriate and even preferred solution for this problem (barring really significant performance differences).

    Hey Barry,

    Can you guess what this:

    ...is a (one million row) execution plan for? 😛

    Paul

    So how does its run time compare with the T-SQL-only query?

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

  • February 24, 2010 at 4:38 pm

    #1124676

    RBarryYoung (2/24/2010)

    Paul White (2/24/2010)

    RBarryYoung (2/23/2010)

    Give it a go, Paul. The solution that we (independently) came up with is pretty darn efficient, so I would be particularly interested to see if a CLR solution come overcome the "call per row" penalty.

    As for the old Hammer saw (:-P), I actually would defend SQL as an appropriate and even preferred solution for this problem (barring really significant performance differences).

    Hey Barry,

    Can you guess what this:

    ...is a (one million row) execution plan for? 😛

    Paul

    So how does its run time compare with the T-SQL-only query?

    Or are you still trying to figure out where all of the extra CPU time for the CLR Aggregate, that the QP didn't predict, is coming from? :-D:-D (just guessing...)

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

  • February 24, 2010 at 8:53 pm

    #1124728

    WayneS (2/24/2010)

    Paul White (2/24/2010)

    WayneS (2/24/2010)

    Something that you're about to write an article on?

    Oh highly amusing, Wayne! :laugh:

    But Paul.... I'm serious!

    I really need to finish the others in my queue first, but I take your point. I seem to have an embarrassment of riches in terms of ideas for articles just recently - it just takes time to get something together that I'm happy with.

    WayneS (2/24/2010)

    BTW, I know you've told me before how you get the execution plans to display in a post, but I just can't get it to work for me. Would you care to explain it again? I noticed that on your post, it doesn't show as an attachment, yet inside the img tags is a link to an attachment? And it is just uploaded as a .sqlplan file? What else am I missing here?

    My original post had it as an attachment. When writing a post, add the .sqlplan as an attachment. After you hit 'upload', the pop-up window contains a link to the attachment if you look carefully. Right-click on it, copy the link address, and put it in IMG tags. Once you've managed it once, it'll seem obvious.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 24, 2010 at 9:08 pm

    #1124734

    RBarryYoung (2/24/2010)

    Paul White (2/24/2010)

    So how does its run time compare with the T-SQL-only query?

    It depends 😉

    I just put a really obvious (and inefficient) algorithm together for the UDA so that I had something to compare to your awesome T-SQL solution.

    On my laptop, the T-SQL solution is faster up to 2,500 rows of concurrent sessions data, though both are extremely fast, of course so the difference is a few milliseconds. Over 2,500 rows the CLR UDA pulls ahead.

    Measuring elapsed time only, the CLR solution is generally 10% to 25% faster. On my hardware, the advantage peaks at around 500,000 rows. For millions of rows, the CLR solution is back to around 10% faster.

    For everyone who was wondering, the posted plan is from my CLR user-defined aggregate solution to Ben Itzik-Gan's Concurrent Sessions problem, which Barry and a couple of others found a clever new solution for.

    The T-SQL solution requires two indexes, whereas the UDA just requires one of those indexes. When Microsoft implement ordering guarantees for UDA input, I'll be able to remove the Segment, Sequence Project, and Top operators from the plan shown. I don't suppose this would make a huge difference, but every little helps.

    I still think the UDA is the natural solution here. Consider the effort that has gone into finding an efficient T-SQL solution: the average programmer would stand no chance. On the other hand, an amateur CLR implementation requires just a few tens of lines of very simple code, runs faster in many cases, and produces a beautiful plan. I will start by comparing the UDA to the T-SQL solutions that went before...:-)

    I'm going to spend a little time now to see if there are any obvious optimizations to be made to the CLR code, within my very limited .NET skill set of course.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 24, 2010 at 9:10 pm

    #1124735

    RBarryYoung (2/24/2010)

    Or are you still trying to figure out where all of the extra CPU time for the CLR Aggregate, that the QP didn't predict, is coming from? :-D:-D (just guessing...)

    You mistake me for a rank amateur, I think. 😛

    There is relatively little overhead in passing rows to a UDA, the strongly-typed pre-compiled native code stubs generated connect directly to the query plan.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 25, 2010 at 5:17 am

    #1124873

    Barry,

    Made a few improvements and it's now 30%+ faster than T-SQL from 5,000 rows upward. Below that there's no repeatable difference between the two. I sent you the test rig, UDA code, and results summary by email.

    The whole plan now looks like this:

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 25, 2010 at 9:13 am

    #1125053

    Is it just me or is it getting a bit thechnical in here? 😎


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • February 25, 2010 at 9:27 am

    #1125057

    Just wondering, anybody in here use Skype to talk to people that do not use Skype?

    Would you recommend it?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • February 25, 2010 at 9:39 am

    #1125069

    Alvin,

    My organization uses Skype to Phone from international locations. It works okay as long as all that is going on is talking. If there is some type of support session going on the Skype to Phone gets worse. My understanding is that it is really based on the bandwidth available to the Skype user.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 15 posts - 12,061 through 12,075 (of 66,712 total)

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