continuing a previous SELECT TOP X query
Suppose I have a query with a "complex" filtering condition
SELECT
<fields>
FROM
<tables>
WHERE
<complex condition on non-indexed fields>
ORDER BY
<indexed fields>
Now further suppose the database is very large (as in hundredthousands of records)
And this query is part of a webservice (which is what I'm writing) used from a third party website (which I'm not).
In order to limit the time the DB is busy processing the request, and because I don't want to return potentially thousands of records at once, I want to retrieve a set number of records at a time which would be presented on the website one page at a time with previous/next buttons.
I know I can restrict the sql query with say "TOP 10", but what would be a good way to do the next page (11 to 20) and the page after (21 to 30) etc. in such a way that the DB doesn't have to restart the entire query over from the start. or in other words, what's the "optimal way" to continue a previous (set of) TOP X
Re: continuing a previous SELECT TOP X query
Are you using SQL 2012? If so, can you use the new fetch/offset order by parameters?
http://sql-articles.com/articles/gen...l-server-2012/
Re: continuing a previous SELECT TOP X query
We haven't settled on a DB manager yet, ideally we would want to be supporting several. I'm not sure we can "require" MSSQL 2012, some of our smaller customers might have budget issues with that. A more generic solution would be prefered.
I'm not sure I fully understand the Offset / Fetch next approach. Or rather, how the DB manages to optimize this efficiently. The issue here is the "complex condition".
if I do an offset X, fetch next Y. How does this prevent the DB from starting the query all over again, and rerunning the query to find the first X records. It is very possible that it may need to process thousands and thousands of records to obtain the first top X.
If what the DB does is "fetch top X+Y, and don't return first X in the resultset" then this probably won't work efficiently enough.
Re: continuing a previous SELECT TOP X query
Quote:
Originally Posted by
OReubens
if I do an offset X, fetch next Y. How does this prevent the DB from starting the query all over again, and rerunning the query to find the first X records. It is very possible that it may need to process thousands and thousands of records to obtain the first top X.
If what the DB does is "fetch top X+Y, and don't return first X in the resultset" then this probably won't work efficiently enough.
I don't know how it works internally. Could you take some performance metrics and see if the results return consistently? If the implementation internally is correct, I would expect the timings of the last set of records to be similar to the first set.
Re: continuing a previous SELECT TOP X query
I don't have MSSQL 2012 installed, so can't do any tests atm, nor can I easily convert the current database to MSSQL. This whole thing is currently ISAM based, and I'm looking at doing the same functionality with the SQL/ODBC layer, so we can transitionaway from ISAM and into relational. With ISAM, this whole thing is easy enough, we simply return the record position for each record, and for a next/previous, we call the webservice with either the record position of the first record in the current "page" (for a previous) or the last record (for a next).
Ideally we want this to work with any db that has a sufficiently compatible SQL syntax/features. We have customers on MSSQL already, but for the smaller customers, MSSQL really isn't an option because of the pricing.
Re: continuing a previous SELECT TOP X query
Re: continuing a previous SELECT TOP X query
Quote:
Originally Posted by
VictorN
Yep. MS now has several low cost SQL options.
Re: continuing a previous SELECT TOP X query
not really useful if it needs to be multiuser now is it :)
of did MS change the express to be simultaneously multiuser now ?
Re: continuing a previous SELECT TOP X query
I use SQL server beginning with the 2008 Express version. I never heard about any multiuser problem with it. :confused:
FYI:
http://social.msdn.microsoft.com/For...5-785831d319ec
http://www.sqlmag.com/article/databa...capable--49736
Re: continuing a previous SELECT TOP X query
Quote:
Originally Posted by
VictorN
From
http://www.microsoft.com/en-us/sqlse...s/express.aspx
Quote:
SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications
