I have a dynanmic query that populates a data table on a web page, and I need to know the total number of records for the query so that I can use paging on the webpage (so many results per page). I *think* the only way I can do this is by using a preselect in the query statement e.g.
hQuery:query-prepare("preselect each " + hTempTable:name).
hQuery:query-open.
assign iTot = hQuery:num-results.
1) Is this the only way, or is there a better/more efficient method?
2) Preselect sounds like it may be bad for performance....for example on a table with a few thousand records this will be OK, but if I happen to query a table with say 1m records am I right in thinking the preselect will read through all the records just to get the total?
Thanks,
Chris
Yes, the preselect will create a results list before making the data available, so it appears slower.
One method you may want to consider is using a seperate table to store record counts. Update it daily in batch mode, when the system is quiet and use this data when 'paging' on the web (making sure the user understands there may be more than stated).
1. query:num-results return number of records db server returned to client. It is not the same to number of records in database table.
2. sql-89 select count(*) from tablename is fastest way to get number of records. But anyway it takes almost the same time as for each/preselect each.
3. It is strange to page 1mln records? User should press 100000 times page down?
IMHO 1000 records is limit for browsing and paging, very seldom 5000-10000. If db contains more data you have to swintch a look to filter options instead of thinking how to get fast number of records. It is not possible in progress.
Maxim.
Thanks for the advice, it works as I thought.
TBH, 1m records was just there as a large number at one end of the spectrum. I wouldn't consider any sort of report/display that contained that amount of data 
.
Chris
PRESELECT completely resolves a query including it's result list before allowing the next statement to run. This can be helpful when the code's doing things which'll change the result list on the fly and you don't want that. It's also slower than the standard FOR EACH statement.
If batching of records is the ultimate goal, IIRC there's a way to do it with prodatasets, and I think there's examples here on the communities site. I don't know where you'd look for it though.