<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks Warren, in the end I got it sorted fairly easily just using the
(mostly) sqeuential ID that was in the table anyway. It was something I
should have thought of regardless of my inexperience with DBs.<br>
<br>
Cheers,<br>
Phil.<br>
<br>
Warren Slater wrote:
<blockquote
cite="midNHBBJFGKGLLOPAEFMJIAOEJMCCAA.wslater@paradise.net.nz"
type="cite">
<pre wrap="">Hi
You could try just reading the keys into an array and then just use the
array to generate a range of keys to read the records required. This may
work better if the problem is reading moving all the data across the network
rather than the time it takes interbase to read the data from the disk.
ie
pseudo code
sql = 'select key from datafile'
open
first
while not eof do
key[i]:=key
next
end
close
sql = 'select * from datafile where key >= '+key[101]+ ' and key <=
'+key[110]
open
etc to read records 101-110
Might be worth a try.
Warren
-----Original Message-----
From: <a class="moz-txt-link-abbreviated" href="mailto:delphi-bounces@ns3.123.co.nz">delphi-bounces@ns3.123.co.nz</a>
[<a class="moz-txt-link-freetext" href="mailto:delphi-bounces@ns3.123.co.nz">mailto:delphi-bounces@ns3.123.co.nz</a>]On Behalf Of Neven MacEwan
Sent: Friday, 8 April 2005 12:44 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Getting number of records in an Interbase Table
Phil
</pre>
<blockquote type="cite">
<pre wrap="">OK, I understand now, thanks. I do have an ID in sequential order but
there are some gaps in it. I think I can still make it work though.
</pre>
</blockquote>
<pre wrap=""><!---->
Starting from either end will always work even if your
id has gaps, If you have a sequence (even with gaps) then you could also
do an jump in and scroll to get an absolute page
ie you want the 200th page of 30 items
if select count(*) from table where id > (200 * 30)
returns 5950 then
execute select * from table where id > (200 * 30)
and discard the first 50 items, a lot faster
than scrolling thru 6000
HTH
Neven
</pre>
<blockquote type="cite">
<pre wrap="">Cheers,
Phil.
Neven MacEwan wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Phil
</pre>
<blockquote type="cite">
<pre wrap="">OK, so I can't do it client side. Does this mean a stored procedure
is able to wizz through the records much faster?
</pre>
</blockquote>
<pre wrap="">
If you use a fetch from a key, ie you only navigate from
either end and don't try to jump into the middle then you
dont have to do a linear search
ie you have a table of 1,000,000 records displayed in recid order
(recid is 1 to 1,000,000)
EXEC GetPage NULL, 'forward' // First page
SQL = 'SELECT * FROM Table ORDER BY recid'
next page
EXEC GetPage 30, 'forward' // second page
SQL = 'SELECT * FROM Table WHERE recid > 30 ORDER BY recid'
last page
EXEC GetPage Null, 'backward' // last page
SQL = 'SELECT * FROM Table ORDER BY recid DESC'
second to last page
EXEC GetPage 999970, 'backward' // last page
SQL = 'SELECT * FROM Table WHERE recid < 999,970 ORDER BY recid DESC'
In this simplistic example to could do an absolute fetch
into the middle of the table (as the key is a sequence)
but if your unique candidate key was CustNo, Date, TranType, recid
then you couldn't, so the display order is important
HTH
Neven
</pre>
<blockquote type="cite">
<pre wrap="">When you say "candidate key" do you mean a record number, or are you
refering to an indexed field? Sorry for my "greenness" - this kind of
DB stuff just hasn't come across my path before.
Phil.
Neven MacEwan wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Phil
How are you ordering your result set? Because what the only
way it will work efficiently (without scrolling to the nth record)
is if you can pass a candidate key of your display sort order
to the SP in psuedo code (@ indicated a parameter to the sp)
You need the candidate key to determine the last records
anyway
if @direction = forward
if CandidateKey is NULL // fetch first 30 records
Select col from table1
else // fetch 30 record from this point
Select col from table1 where candidatekey > @candidatekey
if @direction = fromend
if CandidateKey is NULL // fetch last 30 records
Select col from table1 order by candidatekey desc
else // fetch 30 record from this point
Select col from table1 where candidatekey < @candidatekey
order by candidatekey desc
HTH
Neven
Phil Middlemiss wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Further to my last barrage of questions:
I can see how this procedure would fetch a certain number of
records, but how do I get it to start at record X?
Phil.
Todd Martin wrote:
</pre>
<blockquote type="cite">
<pre wrap="">You can return a specified number of rows from Interbase using a
stored procedure.
create procedure GetNRecords(iMaxRecords INTEGER)
returns (<field_list>)
as
DECLARE VARIABLE iCount INTEGER;
BEGIN
iCount = 0;
FOR SELECT <field_list>
FROM <tablename(s)>
INTO :<field_list>
DO
BEGIN
iCount = iCount + 1;
IF (iCount <= iMaxRecords) THEN
SUSPEND;
ELSE
EXIT;
END
END
----- Original Message ----- From: "Neven MacEwan" <a class="moz-txt-link-rfc2396E" href="mailto:neven@mwk.co.nz"><neven@mwk.co.nz></a>
To: "NZ Borland Developers Group - Delphi List"
<a class="moz-txt-link-rfc2396E" href="mailto:delphi@ns3.123.co.nz"><delphi@ns3.123.co.nz></a>
Sent: Friday, April 08, 2005 10:53 AM
Subject: Re: [DUG] Getting number of records in an Interbase Table
</pre>
<blockquote type="cite">
<pre wrap="">Phil
This is the age old problem of treating SQL as an ISAM
I'd split it into 2 queries, SELECT COUNT(*) FROM Table, then
SELECT Columns FROM TABLE
At least this way you are forwarned as to how many records you are
going to get
As to "I can jump to a specific record", This cannot be done, you
can only scroll to the Nth record, keep this in mind
Does Interbase Support a 'TOP N' Query?
Neven
Phil Middlemiss wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I'm using an Interbase IBQuery that selects all records in a
table (lots of records) and I want to know how many records
there are before I start navigating so I can jump to a specific
record.
If I ask IBQuery.RecordCount I always get "1" returned, but if I
go IBQuery.Last and ask for IBQuery.RecNo then it takes too long
since it loads the whole table.
Is there a quicker way to work out how many records there are in
the result set?
Phil.
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
<pre wrap="">--
Neven MacEwan (B.E. E&E)
Ph. 09 620 1356 Mob. 027 4749 062
New Address Details
===================
MWK Computer Systems
1 Taumata Rd
Sandringham
Auckland
Ph 620 1356
Fx 620 1336
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
<pre wrap="">_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
<pre wrap="">_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
</blockquote>
<pre wrap="">_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
</blockquote>
<pre wrap="">_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
<pre wrap=""><!---->
--
Neven MacEwan (B.E. E&E)
Ph. 09 620 1356 Mob. 027 4749 062
New Address Details
===================
MWK Computer Systems
1 Taumata Rd
Sandringham
Auckland
Ph 620 1356
Fx 620 1336
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 7/04/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 7/04/2005
_______________________________________________
Delphi mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Delphi@ns3.123.co.nz">Delphi@ns3.123.co.nz</a>
<a class="moz-txt-link-freetext" href="http://ns3.123.co.nz/mailman/listinfo/delphi">http://ns3.123.co.nz/mailman/listinfo/delphi</a>
</pre>
</blockquote>
</body>
</html>