Dynamic Sorting

Discussion in 'ASP General' started by CJM, Sep 20, 2005.

  1. CJM

    CJM Guest

    How can I dynamically sort the results from a Stored Procedure? Or more
    importantly, what is the fastest and most efficient way?

    I know I can do the sorting within the recordset in ASP, but AFAIK this is
    not the most efficient method.

    Ideally, I'd like to pass a parameter to the SP to indicate sorting field
    order...

    How do you guys go about this?

    Thanks

    Chris

    --

    [remove the obvious bits]
     
    CJM, Sep 20, 2005
    #1
    1. Advertising

  2. CJM

    David Portas Guest

    David Portas, Sep 20, 2005
    #2
    1. Advertising

  3. I have some content to add, but for now,
    http://www.aspfaq.com/2501


    "CJM" <> wrote in message
    news:...
    > How can I dynamically sort the results from a Stored Procedure? Or more
    > importantly, what is the fastest and most efficient way?
    >
    > I know I can do the sorting within the recordset in ASP, but AFAIK this is
    > not the most efficient method.
    >
    > Ideally, I'd like to pass a parameter to the SP to indicate sorting field
    > order...
    >
    > How do you guys go about this?
    >
    > Thanks
    >
    > Chris
    >
    > --
    >
    > [remove the obvious bits]
    >
     
    Aaron Bertrand [SQL Server MVP], Sep 20, 2005
    #3
  4. CJM

    Rakesh Guest

    In SP

    ORDER BY
    CASE @Param
    WHEN 1 THEN col1
    WHEN 2 THEN col2
     
    Rakesh, Sep 20, 2005
    #4
  5. CJM

    Tim Slattery Guest

    Rakesh <> wrote:

    >In SP
    >
    >ORDER BY
    > CASE @Param
    > WHEN 1 THEN col1
    > WHEN 2 THEN col2
    > .
    > .
    > END


    Bear in mind that this depends on the DB system you're using. I think
    the above is valid syntax in Oracle. I know it won't work with Sybase.

    --
    Tim Slattery
    MS MVP(DTS)
     
    Tim Slattery, Sep 20, 2005
    #5
  6. CJM

    CJM Guest

    Thanks David, Aaron & Rakesh...

    Apologies to Aaron: How could I post this without checking your site
    first??! I'm not worthy...

    Chris
     
    CJM, Sep 20, 2005
    #6
  7. "CJM" <> wrote in message
    news:...
    > How can I dynamically sort the results from a Stored Procedure? Or more
    > importantly, what is the fastest and most efficient way?
    >
    > I know I can do the sorting within the recordset in ASP, but AFAIK this is
    > not the most efficient method.

    [snip]

    Actually, the recordset sort operation is pretty efficient. From the
    Recordset.Sort property documentation:

    http://msdn.microsoft.com/library/en-us/ado270/htm/mdprosortpropertyado.asp
    "...The sort operation is efficient because data is not physically
    rearranged, but is simply accessed in the order specified by the index."


    The code would look something like this:
    <%
    Dim cn, rs
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.CursorLocation = 3 'Clientside Cursor
    cn.Open "DSN-Less OLEDB Connection String"
    cn.MyStoredProcedure, rs
    rs.Sort = "Column1, Column2, etc..."
    Response.Write rs.GetString(2, , ":", "<br>")
    rs.Close : Set rs = Nothing
    cn.Close : Set cn = Nothing
    %>

    Notes:
    1. You need to use a client-side cursor to use the sort property.
    2. The recordset sort can be very efficient since the index it builds is
    based on the subset of data being return. Whereas the database sort uses an
    index based on all the data contained in the table.
    3. In the case of complex ORDER BY clause, the database may not even be able
    to take advantage of indexes, even if they do exist.

    HTH
    -Chris Hohmann
     
    Chris Hohmann, Sep 20, 2005
    #7
  8. Chris Hohmann wrote:
    > "CJM" <> wrote in message
    > news:...
    >> How can I dynamically sort the results from a Stored Procedure? Or
    >> more importantly, what is the fastest and most efficient way?
    >>
    >> I know I can do the sorting within the recordset in ASP, but AFAIK
    >> this is not the most efficient method.

    > [snip]
    >
    > Notes:
    > 1. You need to use a client-side cursor to use the sort property.
    > 2. The recordset sort can be very efficient since the index it builds
    > is based on the subset of data being return. Whereas the database
    > sort uses an index based on all the data contained in the table.
    > 3. In the case of complex ORDER BY clause, the database may not even
    > be able to take advantage of indexes, even if they do exist.
    >


    Just to add a little extra to Chris's good advice: I would recommend
    disconnecting the recordset if planning to do this. That way you can close
    the connection while processing your records. It's always a good thing to
    release your connection back to the pool as soon as possible. Granted, the
    use of GetString() is pretty quick, but if you are planning to use a
    recordset loop instead, it's a good idea to disconnect before doing it.

    Bob Barrpws
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Sep 20, 2005
    #8
  9. CJM

    CJM Guest

    Bob/Chris,

    I actually already do what you have suggested (though I don't currently
    disconnect as quickly as Bob would like - though I agree with the
    principle).

    However, I was under the impression that this approach was inefficient
    and/or slower (ie. being client-side)... But you seem to be indicating that
    it isn't (or isn't to any significant degree). Do you have and links to
    where I can read more about this?

    Give the two approaches specified (sort in recordset vs dynamically sort
    SP), does the client-side approach have any specific benefits over the other
    approach.

    The point is that I'm open to new ideas, but don't want to keep chopping and
    changing my approach when there is little benefit to doing so. If there is
    nothing betweent these approaches then I might as well continue as I am.

    Thanks

    Chris
     
    CJM, Sep 21, 2005
    #9
  10. "CJM" <> wrote in message
    news:%...
    > Bob/Chris,
    >
    > I actually already do what you have suggested (though I don't currently
    > disconnect as quickly as Bob would like - though I agree with the
    > principle).
    >
    > However, I was under the impression that this approach was inefficient
    > and/or slower (ie. being client-side)... But you seem to be indicating
    > that it isn't (or isn't to any significant degree). Do you have and links
    > to where I can read more about this?
    >
    > Give the two approaches specified (sort in recordset vs dynamically sort
    > SP), does the client-side approach have any specific benefits over the
    > other approach.
    >
    > The point is that I'm open to new ideas, but don't want to keep chopping
    > and changing my approach when there is little benefit to doing so. If
    > there is nothing betweent these approaches then I might as well continue
    > as I am.
    >
    > Thanks
    >
    > Chris


    Here's an excerpt from an SQL Server Magazine article entitled "ADO
    Performance Best Practices":

    Don't ask the server to sort unless necessary. In many cases, sorting
    reasonably sized Recordset objects is faster after they arrive at the
    client. Letting the ADO client sort the rows in the Recordset also gives the
    client application more flexibility to use alternative user-selected sort
    sequences.

    Link:
    http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/bestprac.asp


    So, my advice is to learn the server-side dynamic sort method, compare it to
    the performance of client-side sorting and then stick with client-side
    sorting once the testing confirms that it's faster.

    --
    May you be touched by His noodly appendage. RAmen.
    http://venganza.org
     
    Chris Hohmann, Sep 21, 2005
    #10
  11. > Give the two approaches specified (sort in recordset vs dynamically sort
    > SP), does the client-side approach have any specific benefits over the
    > other approach.


    Yes! Dynamic sorting in the SP will almost certainly lead to dynamic SQL
    and IF/ELSE structures, both of which can lead to recompiles since the query
    plan can either be bad, atypical, or non-existent. The statement in the
    article Chris referenced is bang on, don't add extra sorting in the database
    unless necessary. The database server has plenty of other things it can
    waste CPU cycles and memory allocation on, believe me.

    The web server, on the other hand, has very little processing to do (in
    general).

    While we're talking about the "client", there are two different "client"
    tiers we are talking about here, and I'm not sure which "client" you have
    assumed is less efficient. There is the "client" of the stored procedure
    itself, typically a web server or a web farm. Then there is the "client" of
    the web server or web farm, e.g. the end user's PC.

    Since you can also allow dynamic sorting of, say, an HTML table in
    client-side JavaScript, which can leverage yet a third machine which is,
    again generally, underutilized: the client's PC. Depending, of course, on
    the nature of the audience... if your web site is a Matlock reunion site you
    may not be able to get away with as much assumption on client PC power as,
    say, a Photoshop resource site.

    A
     
    Aaron Bertrand [SQL Server MVP], Sep 21, 2005
    #11
  12. CJM

    CJM Guest

    "Chris Hohmann" <> wrote in message
    news:...
    >
    > Here's an excerpt from an SQL Server Magazine article entitled "ADO
    > Performance Best Practices":
    >
    > Don't ask the server to sort unless necessary. In many cases, sorting
    > reasonably sized Recordset objects is faster after they arrive at the
    > client. Letting the ADO client sort the rows in the Recordset also gives
    > the client application more flexibility to use alternative user-selected
    > sort sequences.


    Point taken. Not sure what reasonably-sized means, but I'm sometimes dealing
    the hundreds of rows, and usually in the ten's of rows, I guess this
    certainly qualifies as reasonable.

    >
    > Link:
    > http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/bestprac.asp
    >


    I'm sure I've read this before - bookmarked now.

    >
    > So, my advice is to learn the server-side dynamic sort method, compare it
    > to the performance of client-side sorting and then stick with client-side
    > sorting once the testing confirms that it's faster.
    >


    There's confidence for you...lol

    Thanks

    Chris
     
    CJM, Sep 22, 2005
    #12
  13. CJM

    CJM Guest

    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:%...
    >> Give the two approaches specified (sort in recordset vs dynamically sort
    >> SP), does the client-side approach have any specific benefits over the
    >> other approach.

    >
    > Yes! Dynamic sorting in the SP will almost certainly lead to dynamic SQL
    > and IF/ELSE structures, both of which can lead to recompiles since the
    > query plan can either be bad, atypical, or non-existent. The statement in
    > the article Chris referenced is bang on, don't add extra sorting in the
    > database unless necessary. The database server has plenty of other things
    > it can waste CPU cycles and memory allocation on, believe me.
    >

    In this case, the web server and the DB server are the same machine (not
    ideal, but it's a satellite office).

    > The web server, on the other hand, has very little processing to do (in
    > general).
    >
    > While we're talking about the "client", there are two different "client"
    > tiers we are talking about here, and I'm not sure which "client" you have
    > assumed is less efficient. There is the "client" of the stored procedure
    > itself, typically a web server or a web farm. Then there is the "client"
    > of the web server or web farm, e.g. the end user's PC.
    >


    True. I think I was forgetting that.

    > Since you can also allow dynamic sorting of, say, an HTML table in
    > client-side JavaScript, which can leverage yet a third machine which is,
    > again generally, underutilized: the client's PC. Depending, of course, on
    > the nature of the audience... if your web site is a Matlock reunion site
    > you may not be able to get away with as much assumption on client PC power
    > as, say, a Photoshop resource site.
    >
    > A
    >
    >


    Thanks
     
    CJM, Sep 22, 2005
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jordo
    Replies:
    0
    Views:
    468
    Jordo
    Oct 31, 2003
  2. Replies:
    2
    Views:
    1,476
    James Kanze
    Jul 6, 2010
  3. Jason
    Replies:
    0
    Views:
    405
    Jason
    Oct 4, 2006
  4. Tom Kirchner

    sorting by multiple criterias (sub-sorting)

    Tom Kirchner, Oct 11, 2003, in forum: Perl Misc
    Replies:
    3
    Views:
    501
    Michael Budash
    Oct 11, 2003
  5. Íéêüëáïò Êïýñáò

    Sorting a set works, sorting a dictionary fails ?

    Íéêüëáïò Êïýñáò, Jun 10, 2013, in forum: Python
    Replies:
    12
    Views:
    168
    Ulrich Eckhardt
    Jun 10, 2013
Loading...

Share This Page