Does sorting the Tablix in the RDL is better than using ORDER BY in stored procedures?

Question

Dear stackoverflow administrators and members:

Hello, a pleasant day to everyone.

I have been developing RDL (and it uses stored procedure, in SQL Server 2008 R2, to get data) for quite sometime in this company I am working for. Our previous database administrator is the one who checked my stored procedure and advises me to make some changes if my stored procedure needed enhancement. And a year later he resigned. :-(

And came this new database admin, now he wants me to revise all of my stored procedures specif. those RDL that uses a stored procedure having an ORDER BY clause. :-(

He said that sorting must be done in the report/RDL, because it will somehow unload some overhead in the database, which in turn, will make my report generate data faster.

My question now is, is he correct about this? Is there additional reading I could read to accompany what he'd advised? Or is it what he'd advised just a simple rule of thumb I didn't practiced since? (As he said, no ORDER BY clause, and no formatting (example on dates, time, etc.) in stored procedures, do all the formatting and sorting in the RDL. I am still halfway to go of my changes but I want to learn more about on what he's keep on instructing. Please advise.

Thank you and more power to all. :-)

Very truly Yours,

Mark Squall

Answer 1

As I've commented, the sorting issue comes down to sorting in the client-side or server-side. The linked question could probably provide more insightful discussion about each option, but in short, there is no clear cut answer to it, each has tradeoffs, and what you sacrifice ultimately depends on your particular use case.

As you mention "RDL", I suppose you're referring to SQL Server Reporting Services, right? If so, then the situation is different, as the "server" and the "client" are in fact two different programs running probably on the same machine (correct me if I'm wrong).

Basically, doing it in the server (ie ORDER BY), the DB engine effectively does more work, as your DBA says. Good thing is that it probably SQL has more tools to provide a faster response than client (indexes, statistics, presumably faster CPU, etc) and for operations like paging and TOP it's almost always a better choice to do in the DB, as the client needs to fetch much more data to provide the result.

Sorting client side has the advantage of distributing the sorting. When many clients use the DB together, having them doing the sorting could alleviate CPU stress on the server (this is most likely the reason behind your DBA's reasoning). Also it enables clients to efficiently re-sort the data on user action without going to the server again (unless paging is involved).

As for the formating you mention, here I tend to agree with the DBA. Most times, formating is better handled in the client rather served by the DB, because it's specific in the way it will be displayed in each place. Having the stored procedure format dates and such means coupling it to that particular report, which means that you can't easily re-utilize it for other purposes. Client side tools are usually better at displaying things for the end user, and can even take into account things like localization (decimal places, date format ordering, paddings, etc.), which, while possible to do in the server, normally involves more work and even duplicating the formating functionality.