I have a EDB table of around 500,000 records.  The primary key is an autoinc field.  SetRange is painfully slow.  However, FindKey or Locate is very fast implying that it is able to use index
field.   Does SetRange use index field?

Besides, I encounter a strange problem.  When I use DevExpress' LookupComboBox, it takes a few seconds before popping up the list.  The larger the table, the longer the time.  I wonder if
this problem is related to the SetRange problem.  

PK

<< I have a EDB table of around 500,000 records.  The primary key is an
autoinc field.  SetRange is painfully slow.  However, FindKey or Locate is
very fast implying that it is able to use index field.   Does SetRange use
index field? >>

How many rows are in the range ?  Could you send me the table and code that
you're using ?

<<  Besides, I encounter a strange problem.  When I use DevExpress'
LookupComboBox, it takes a few seconds before popping up the list.  The
larger the table, the longer the time.  I wonder if this problem is related
to the SetRange problem.  >>

I doubt that it uses a SetRange for that.  It probably uses a Lookup() or
Locate() call, which may be un-optimized if there isn't a matching index for
the lookup in terms of case-sensitivity, etc.  Again, if you want to send me
the code that you're using along with the table, I can take a look and tell
you what is going on.

--
Tim Young
Elevate Software
www.elevatesoft.com

<<How many rows are in the range ?  Could you send me the table and code that
you're using ?

There is only one row in the range.  The code I use is:

  Table.SetRange([id], [id]);

where id is an Integer.

The table has 2 fields only: doc_id and doc_xml.  It is indexed on doc_id.   doc_xml is a CLOB field and is around 1.5GB.   To save the upload file size, I've trim it to null.  It is faster now but
you can still notice a short delay when doing the SetRange.   I've attached the program and the database.

<<I doubt that it uses a SetRange for that.  It probably uses a Lookup() or
Locate() call, which may be un-optimized if there isn't a matching index for
the lookup in terms of case-sensitivity, etc.  Again, if you want to send me
the code that you're using along with the table, I can take a look and tell
you what is going on.

See attached file.

Tim,

I've done some timing.  When there are 500K records, the SetRange is about 0.6 sec in my PC (Core2Duo 1.66GHz).  When ther are 1 million records, the time surges to 2 sec.  I traced the
code.  The SetCursorRange in TEDBTable.ApplyRange is very fast.   The problem seems to lie with the First function that follows the SetCursorRange.  I further trace it and found that
TDataSet.First calls InternalFirst followed by GetNextRecord.  InternalFirst (which is TEDBDataSet.InternalFirst) executes very fast but the GetNextRecord is very slow.  It looks like
GetNextRecord is doing some sequential searches.   Hope this information help.

PK

<< See attached file. >>

Please do not post attachments in any other newsgroup than the Binaries newsgroup.

<< I've done some timing.  When there are 500K records, the SetRange is
about 0.6 sec in my PC (Core2Duo 1.66GHz).  When ther are 1 million records,
the time surges to 2 sec.  I traced the code.  The SetCursorRange in
TEDBTable.ApplyRange is very fast.   The problem seems to lie with the First
function that follows the SetCursorRange.  I further trace it and found that
TDataSet.First calls InternalFirst followed by GetNextRecord.  InternalFirst
(which is TEDBDataSet.InternalFirst) executes very fast but the
GetNextRecord is very slow.  It looks like GetNextRecord is doing some
sequential searches.   Hope this information help. >>

I'll see what I can find out.  The First calls should be respecting the
range and not require any additional navigation on the index.

--
Tim Young
Elevate Software
www.elevatesoft.com

<<I'll see what I can find out.  The First calls should be respecting the
range and not require any additional navigation on the index.

I checked First and Last methods under Range.  They are slow too.   This is probably related to the SetRange problem as ApplyRange calls First after
calling SetCursorRange.

PK

<< I checked First and Last methods under Range.  They are slow too.   This
is probably related to the SetRange problem as ApplyRange calls First after
calling SetCursorRange. >>

The next and prior navigations aren't taking into account the range, hence
the problem.  A fix will be in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

<<The next and prior navigations aren't taking into account the range, hence
the problem.  A fix will be in the next build.

Tim,

Thanks.

PK

Tim Young [Elevate Software] wrote:

> << I checked First and Last methods under Range.  They are slow too.   This
> is probably related to the SetRange problem as ApplyRange calls First after
> calling SetCursorRange. >>
>
> The next and prior navigations aren't taking into account the range, hence
> the problem.  A fix will be in the next build.
>

Tim,
    I'm glad to hear that thre range speed is getting fixed. I noticed
the same slow ranges on my large tables and I thought I was going to
have to give up on ElevateDb because of that. I guess I should have
complained more. <vbg>

Dave