1

I am investigating WCF Data Services for a new software client, and all of our data access logic is currently encapsulated within stored procedures. The new software client needs to be able to display very large data sets in a paged fashion, for two reasons:

  1. To improve over-the-wire performance
  2. To reduce SQL Server load, as it will only need to construct page-sized results.

The OData skip/take syntax will do, but on the server, the query will ultimately be executed against a stored procedure (it is not viable to bypass these complex stored procedures, and go straight to tables).

My concern is that the stored procedure will be executed without result-set restriction (no ORDER BY/TOP or even any 'WHERE' clauses), then a subsequent query will filter the data, using whatever SQL skip/take mechanism is available. A lot of the work the stored procedure did will be wasted because it will fall outside of the required page size.

My question is: In SQL Server, when a stored procedure is queried (instead of a direct table), is it just as efficient to reorder and restrict the full result, as it is to pass restriction criteria into the stored procedure?

I would hope that a stored procedure's constituent instructions are executed in somewhat of a deferred manner (a la Linq), so that when further 'WHERE', 'TOP', and 'ORDER BY' clauses are added, the resulting work that is done on the database is efficiently tuned.

Thanks!

bobs
  • 21,844
  • 12
  • 67
  • 78
Adam
  • 4,159
  • 4
  • 32
  • 53
  • If you can wait that long, Denali will have built in paging function, like MySQL, well almost, but still will allow you to do paging at a statement level without having to write complicated code to do so. – Ryk Sep 09 '11 at 01:01
  • While it's possible that our product wont be ready until Denali releases, expecting all of our customers to upgrade their SQL version really isnt an option. I would love a world where developers drove customer requirements! – Adam Sep 09 '11 at 01:24
  • @Adan Refere to this link http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging – Manvinder Jun 21 '12 at 08:56
  • @Adam http://www.4guysfromrolla.com/webtech/042606-1.shtml – Manvinder Jun 21 '12 at 08:57

0 Answers0