SQL Query Results Displayed

[ekinike]

Hello,

I'm not a programmer, but have a general question.

I am currently using a web-based application which queries a SQL server and then displays thousands of records based on my search. This can be an extremely painful process. I realize I can ask the web app to display only 100 items at a time, but the query still needs to complete. Is there anyway to improve this process with SQL? I know it can be done with Oracle.

Thanks.

 

[NilsBevaart]

If you use the SELECT TOP 100 * FROM xxx
the query stops after the first 100 are found

Nils Bevaart

 

[ekinike]

Could I have a Next button that would pick up the search from 101 to 200?

 

[donutman]

Yes, but you need to have a sort column with unique data to know where to start picking up the next 100 rows.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jgoodman00]

If you are developing an ASP.NET web applicatoin, the datagrid control has built in paging capabilities which are pretty simple to implement.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[donutman]

I don't do ASP or web site programming very often (almost never), but isn't that a bad practice for a web app? I would think it unnecessarily consumes lots of resources.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jgoodman00]

Yes, I guess it comes down to a call between consuming resources versus the number of times you want to run the query. If the query takes a long time to execute it might be better to page through a single large recordset, but otherwise it may be better to rerun the query & only return small recordsets.

In .NET you can also cache pages (based on your chosen cache timeout) so the data is not requeried when multiple users hit the same page. The page is basically redisplayed until the cache timeout expires at which point it is requeried.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com