Dynamic Sorting

[CJM]

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

 

[David Portas]

http://www.aspfaq.com/show.asp?id=2501

 

[Aaron Bertrand [SQL Server MVP]]

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

 

[Rakesh]

In SP

ORDER BY
CASE @Param
WHEN 1 THEN col1
WHEN 2 THEN col2

 

[Tim Slattery]

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.

 

[CJM]

Thanks David, Aaron & Rakesh...

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

Chris

 

[Chris Hohmann]

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

 

[Bob Barrows [MVP]]

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

 

[CJM]

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

 

[Chris Hohmann]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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

 

[CJM]

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]

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