For a given where-construct against a given table I need to determine how many rows are
returned in total and then present the x'th to the x+n'th results
(offset,limit).
The where-construct can be very simple (ie : non at all) or involve
complex regexpressions-searches and logical connections.
Now there are two possible ways to perform this task:
A) select OID from table WHERECONSTRUCT; to get the number of rows
select FIELDS from table WHERECONSTRUCT offset x limit n order by o;
B) select FIELDS from table WHERECONSTRUCT order by o; to get the
number and then retrieve all results and choose the needed rows
Now it turns out, that if the where-construct is very simple, then
approach A) with its two selects is by factor 5 faster than approach
B). If the where-constructs get more complex then approach B) gets
faster by factor 2.
Is there any known help to decide between this two approaches on the
given WHERECONSTRUCT ? (I'm sure that it cant be predicted but maybe
there is some help)
Or is there any other way to solve my problem ?
thnx,
peter
--
mag. peter pilsl
IT-Consulting
tel: +43-699-1-3574035
fax: +43-699-4-3574035
pilsl@goldfisch.at
http://www.goldfisch.at