Trying to prevent multiple database calls with a very large call

Question

So we run a downline report. That gathers everyone in the downline of the person who is logged in. Some people of clients run this with no problem as it returns less than 100 records.

Some people of clients however returns 4,000 - 6,000 rows which comes out to be about 8 MB worth of information. I actually had to up my buffer limit on my development machine to handle the large request.

What are some of the best ways to store this large piece of data and help prevent it from being run multiple times consecutively?

Can it be stored in a cookie? Session is out of the question as this would eat up way to much memory on the server.

I'm open to pretty much anything at this point, trying to better streamline the old process into a much quicker efficient one.

Right now what is done, is it loads the entire recordset, it loops through the recordset building out the data into return_value cells.

Would this be better to turn into a jquery/ajax call?

The only main requirements are:

classic asp jquery/javascript T-SQL

email as a file attachment? ;) – Lee Louviere May 31 '12 at 14:56 — Lee Louviere, May 31 '12 at 14:56

score 1 · Accepted Answer · answered May 31 '12 at 18:37

1

Why not change the report to be paged? Phase 1: run the entire query, but the page only displays the right set of rows based on selected page. Now your response buffer problem is fixed. Phase 2: move the paging into the query using Row_Number(), now your database usage problem is fixed. Phase 3: offer the user an option of "display to screen" (using above) or "export to csv" where you can most likely export all the data, since csv is nice and compact.

answered May 31 '12 at 18:37

Brian White

1,265
1
10
16

Do you know of any good examples out there I can look at off the top of your head? I'll try searching on google for a good one for now. – James Wilson May 31 '12 at 22:34
I had an ASP.Net MVC example here: http://stackoverflow.com/questions/10121735/asp-net-mvc3-webgrid-custom-server-side-sorting/10759799#10759799 – Brian White Jun 22 '12 at 03:29

score 0 · Answer 2 · edited May 23 '17 at 12:27

Using a cookie seems unwise, given the responses to the question What is the maximum size of a web browser's cookie's key?.

I would suggest using ASP to create a file on the Web server and writing the data to that file. When the user requests the report, you can then determine if "enough time" has passed for it to be worth running the report again, or if the cached version is sufficient. User's login details could presumably be used for naming the file, or the Session.SessionID, or you could store something new in the user's session. Advantage of using their login would be that your cache of the report can exist longer than a user's session.

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

Taking Brian's Answer further, query page count, which would be records returned / items per page rounded up. Then join the results of every page query on client side. Pages start at a offset provided through the query. Now you have the full amount on the client without overflowing your buffer. And it can be tailored to an interface and user option (display x per page).

edited May 23 '17 at 12:29

Community

1
1

answered Jun 13 '12 at 21:52

Lee Louviere

5,162
30
54

Trying to prevent multiple database calls with a very large call

3 Answers3