Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: sorting data, ADO, SQL Server etc...

    Ok, it is a given that sorting operations are sometimes expensive in SQL Server using the ORDER BY clause because the relational model is not really well suited to sort data blah blah blah.

    So the canned DBA response is "this is a presentation issue, handle it in the presentation layer".

    OK, great. Problem solved. So I am digging around in the ADO.Net object model trying to give a junior developer some guidance and i am trying to figure out what is the best way in ADO.Net to order what we used to call a recordset where the sorting is not done at the SQL Server, but in IIS.

    I imagine there is some overhead in this operation as well. Is it really going to save me some overall performance or am I just shifting the burden and I will not likely see any appreciable difference?
    Last edited by Thrasymachus; 02-21-08 at 11:22.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    <whispers>Actually, I usually do the sorting in the back end. I just find it easier. Please don't tell blindman.</whisper>

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    About how many rows are you sorting? If it is under say 1000, i would expect SQL Server to be able to handle that better.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I hold my breath, jump up and down, and throw myself to the ground in a real fit about developers returning large resultsets to the applications, but this is some report so I guess they need it all at once.

    It looks like the results can range from 0 to the single digit thousands.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    With the largest data set they can come up with, what is the difference in time and/or CPU and disk I/O for returning the unordered and ordered result sets to the client? In other words, how much work are you lifting from the DB server?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    since my job responsibilities just expanded to writing application code, I will have to play around with the differences, but what I am hearing is that there is most likely a cut off based on the size of the recordset (and probably the number of colums being sorted on) where it makes more sense to do it one way or the other.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    I find it extremely odd that anyone would think doing the sort outside of the database could possibly give better results. Do you have anything to back that up?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the sort can be described so that SQL Server can do the sort for you, it will run at least ten times faster on the SQL Server than it can on the IIS server, and the median increase is about 32 times faster.

    There are some sorts that depend on data or code that only exists on the IIS server, and those cases are obviously exceptions to this rule.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by LoztInSpace
    I find it extremely odd that anyone would think doing the sort outside of the database could possibly give better results. Do you have anything to back that up?
    My assumption on this actually has always been that the "best practice" of sorting in the front end is to distribute the load (i.e. most of the systems I have worked on have n clients\ servers pointing at a single\ smaller number of database(s)). I also believe, but do not know, that some FEs (such as Crystal) are pretty adept at sorting and so passing an ordered dataset is not a big problem.

    It is a good point though - it is one of those Rules that I don't know the justification for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •