0

My ultimate need is to present some reports (the results of SQL Server stored procedures) on Web API pages.

The challenge is that the stored procedures that return this data are very slow - sometimes taking several minutes to run. No user in their right (or left) mind will find this seemingly interminable delay acceptable, thus I'm wondering if there's a way to get just for the first page (say 40 or 50 rows of data) to present to them as a "pacifier" while the rest are rendered "behind the scenes" on subsequent pages.

I know that the rendering of data in this way (a page at a time) is common, but is it actually possible to "slice up" a stored procedure in this way to speed it up, that is without changing the stored procedure itself - just from C# (or Javascript) from within the Web API app?

The only other viable option (and whether its viable is up for debate) I can think of is to store pre-run reports that have been saved to .xlsx (or .csv) files to the server, where they can be accessed from the Web API app and converted/massaged for display on the pages. This would require sending files from a client utility to the server machine (!) and probably using Excel Interop on the server (!).

The last refuge of this scoundrel would be to just show the user a .gif of a dancing bear juggling kittens or something while the stored procedure chugged away.

UPDATE

Both answers were good, and being a backrunner (supporter of the underdog) from way back, I chose the cat with the fewer points.

What I think I will do, inspired by both of those cats, is to send a "message" to my Web API app when each Excel report is created, to store that data (via calling the same SP) in a table on the server. The report data will then be available to the user when they browse to a provided link. By the time they actually do that (normally at least several minutes later), the table will have been populated with data from the SP, and the subsequent rendering of the page should be several seconds at most rather than minutes.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

2 Answers2

2

Probably I would schedule a job that runs in the background and store the result of the SP in a table and would be access from the web api as a sort of Cache.

You can schedule the job in sql server or using Hangfire

Martino Bordin
  • 1,412
  • 1
  • 14
  • 29
1

Not knowing specifically what you're using or how you're rendering the results of your stored proc. I would actually recommend pre-running large reports on a regular schedule which would free up resources during regular hours as well as allowing users to "skip" the wait. The client however should probably not have direct access to the server machine hosting the DB, setting up an FTP server with the necessary security protocols would probably be the easiest way of automating file transfers between the Server and the client. (SSIS have very quick and simple drag and drop interface for setting up jobs that need to connect to a ftp)

You could also create a duplicate stored proc that only returns a small subset of the results, call that and render it while the main proc is still running (you will probably need to set up some kind of a timer that will keep checking if the main proc has been completed yet or not)

C.Chen
  • 69
  • 5