SQL Paging
-
hi, I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query? I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough. Am I right?
-
hi, I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query? I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough. Am I right?
In my opinion you're right on track. However, things could get complicated if the amount of rows in the result set is changing (because the amount of rows changes, criteria changes etc). So a straightforward solution would be using paging nevertheless. If the connection between the application and the database isn't slow and the database isn't over utilized, this shouldn't cause too much wait time. Of course if you have tables that you know are always small, fetching all one time and then paging locally would be an easy thing to do.
The need to optimize rises from a bad design.My articles[^]
-
hi, I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query? I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough. Am I right?
At some point you are presenting too much data to a user. If you are looking at a 25 row limit per page then probably the limit is well below 500 total rows. At that point you know that the user knows what they are looking for. They are not just randomly scanning records. So make them tell you what they are looking for. Use that to create a query that restricts the total rows returned. When creating servers I usually have a configurable maximum and user queries are run with a count(...) first and if the return is more than a maximum then I return an error. The GUI screens are responsible for providing sufficient input specific to one query to allow the user to provide enough data to get below the limit.
-
hi, I'm using an asp.net application with some sql SELECT statements, I want to ask you when is it worthwhile to execute statements with SQL query? I guess that if I use the paging to get 10-rows pages from a 25-rows table, it's not profitable, and I have to do the paging at the server side code. But when my table has 10000 rows, it becomes worthwhile enough. Am I right?
I you are returning 10k rows to a web browser you are sacked on the spot! One of the issues we have is that the users are always after data dumps to analyse in excel, the continious whine of
just give me a data dumpbecomes really irritating. Now that we have volume policies it is easier to limit these. As others have said, you need to filter the results to minimise the volume returned. I never use paging although I do use a top 1000 in procs with a potential of high volume and then inform the user they have exceeded the volume policy. By the time you chuck 1000 records into a grid with local filtering and sorting I see no requirement for paging.Never underestimate the power of human stupidity RAH
-
I you are returning 10k rows to a web browser you are sacked on the spot! One of the issues we have is that the users are always after data dumps to analyse in excel, the continious whine of
just give me a data dumpbecomes really irritating. Now that we have volume policies it is easier to limit these. As others have said, you need to filter the results to minimise the volume returned. I never use paging although I do use a top 1000 in procs with a potential of high volume and then inform the user they have exceeded the volume policy. By the time you chuck 1000 records into a grid with local filtering and sorting I see no requirement for paging.Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
One of the issues we have is that the users are always after data dumps to analyse in excel
Does that not imply that something is missing from the application? I'm guessing because I don't know the business requirements, but if they are constantly extracting data into another tool to analyse, that suggests that they need some sort of information they can't get from the source application.