How do I make table filters run faster? In a 15M row table it takes 75
to 90 seconds to pull up 5k rows based on an index. The key is
case-sensitive. It doens't matter what the index is set to, it's always
slow. With DBISAM it takes about 1 second or less.

filter: ProductId='ABCD'

It is a compound index but that shouldn't matter since ProductId is the
first field of the index. TIA

Dave

Dave Harrison wrote:
> How do I make table filters run faster? In a 15M row table it takes 75
> to 90 seconds to pull up 5k rows based on an index. The key is
> case-sensitive. It doens't matter what the index is set to, it's always
> slow. With DBISAM it takes about 1 second or less.
>
> filter: ProductId='ABCD'
>
> It is a compound index but that shouldn't matter since ProductId is the
> first field of the index. TIA
>
> Dave

I discovered the SQL queries are also slow so I'm repairing the table.
It will have to run overnight and I'll check it in the morning.

Dave

Dave Harrison wrote:
> Dave Harrison wrote:
>
>> How do I make table filters run faster? In a 15M row table it takes 75
>> to 90 seconds to pull up 5k rows based on an index. The key is
>> case-sensitive. It doens't matter what the index is set to, it's
>> always slow. With DBISAM it takes about 1 second or less.
>>
>> filter: ProductId='ABCD'
>>
>> It is a compound index but that shouldn't matter since ProductId is
>> the first field of the index. TIA
>>
>> Dave
>
>
> I discovered the SQL queries are also slow so I'm repairing the table.
> It will have to run overnight and I'll check it in the morning.
>
> Dave

After the repair, the filter and queries are still slow. Simple queries
to another large table is also slow (over 1 minute for index search).
I'm sure all this worked pretty fast a couple of months ago with v1.04.

The same query in DBISAM 4 takes 1.4 seconds compared to 90 seconds for
ElevateDb. Of course executing the EDB query a second time will take a
modest 4.5 seconds because it is still in the cache. Disconnecting the
session and repeating the query takes 90 seconds again. Am I missing
something obvious here?

Here is the query plan:

================================================================================
SQL Query (Executed by ElevateDB 1.05 Build 2)

Note: The SQL  shown here is generated by ElevateDB and may not be
exactly the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.

================================================================================

SELECT ALL
"ProductHistory"."RecordID" AS "RecordID",
"ProductHistory"."Rcd_Id" AS "Rcd_Id",
"ProductHistory"."Product_Symbol" AS "Product_Symbol",
"ProductHistory"."Product_Date" AS "Product_Date",
"ProductHistory"."Period" AS "Period",
"ProductHistory"."Category" AS "Category",
"ProductHistory"."Quantity" AS "Quantity",
"ProductHistory"."Num1" AS "Num1",
"ProductHistory"."Num3" AS "Num3",
"ProductHistory"."Num4" AS "Num4",
"ProductHistory"."Num2" AS "Num2",
"ProductHistory"."Log_Date" AS "Log_Date"
FROM "ProductHistory"
WHERE "Product_symbol" = 'AVD-301'

Source Tables
-------------

ProductHistory: 16389154 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index PrimaryKey

Filtering
---------

The following filter condition was applied to the Query table:

"Product_symbol" = 'AVD-301' [Index scan: 11355 keys, 802816 bytes
estimated cost]

================================================================================
20726 row(s) returned in 98.516 secs
================================================================================

Dave

Dave,

<< After the repair, the filter and queries are still slow. Simple queries
to another large table is also slow (over 1 minute for index search). I'm
sure all this worked pretty fast a couple of months ago with v1.04. >>

I doubt it, the issue is the reposition of the row pointer to the first
matching row based upon the ordering index.  It may take some time if you
have a lot of rows in the table, the number of rows selected is fairly small
in relation to the size of the table, and the first matching row is towards
the end of the table according to the ordering index.  The workaround for
this is to simply set RequestSensitive to False.  That will generate the
rows fairly quickly.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Dave,
>
> << After the repair, the filter and queries are still slow. Simple queries
> to another large table is also slow (over 1 minute for index search). I'm
> sure all this worked pretty fast a couple of months ago with v1.04. >>
>
> I doubt it, the issue is the reposition of the row pointer to the first
> matching row based upon the ordering index.  It may take some time if you
> have a lot of rows in the table, the number of rows selected is fairly small
> in relation to the size of the table, and the first matching row is towards
> the end of the table according to the ordering index.  The workaround for
> this is to simply set RequestSensitive to False.  That will generate the
> rows fairly quickly.
>

Ok, setting RequestSensitive to False has speeded up the queries because
they are now static. Can I assume that if the table is encrypted, then
the temporary files created by the static queries also remain encrypted?

But setting FilterOptions.foCaseInsensitive = True has no effect on
table filters and they still take over 90 seconds to execute. Don't
table filters make use of indexes when they're available? If not, then
it looks like I'll have to change from using tables to queries because
waiting 90 seconds for a filter to complete (compared to 1 second) isn't
going to sit well with users.

Dave

Dave,

<< Ok, setting RequestSensitive to False has speeded up the queries because
they are now static. Can I assume that if the table is encrypted, then the
temporary files created by the static queries also remain encrypted? >>

Yes.

<< But setting FilterOptions.foCaseInsensitive = True has no effect on table
filters and they still take over 90 seconds to execute. Don't table filters
make use of indexes when they're available? >>

It's not an issue of the filter using the index.  The actual filtering
process probably only takes a few milliseconds.  The issue is re-positioning
the current row pointer on the first row that matches the filter condition.
Filters maintain their row numbers in a bitmap that corresponds to the
physical position of the row in the table, and this does not correspond to
the active index order.  Subsequently, after executing the filter, EDB has
to position itself on the first matching row in the active index order by
literally navigating from the first row until it finds a row that is part of
the filtered set.  This can take a long time if the first matching row is
the 1 millionth out of 1.6 million rows.  DBISAM got around this with the
TDBISAMEngine.FilterIndexThreshhold property, which used an alternative
method when the number of rows satisfying the filter was low compared to the
total row count, but it was problematic, had quite a few bugs initially, and
doesn't fit well with EDB.  I have to come up with a better alternative for
EDB.

<< If not, then it looks like I'll have to change from using tables to
queries because waiting 90 seconds for a filter to complete (compared to 1
second) isn't going to sit well with users. >>

I'll see what I can do about fixing this, but for now filters are equivalent
to a query that generates a sensitive result set, so you'll always see the
issue with a filter.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Ok, thanks for the explanation. It looks like I'll be switching to
static queries for my large EDB tables later this week. Filters can be
60x-100x slower than queries (for the reasons you mentioned) and unless
I use ranges with the filters, I don't think using TEDBTables are going
to be a viable option for filtering large tables. Oh well, it's back to
SQL for me.

Dave

Dave,

<< Ok, thanks for the explanation. It looks like I'll be switching to static
queries for my large EDB tables later this week. Filters can be 60x-100x
slower than queries (for the reasons you mentioned) and unless I use ranges
with the filters, I don't think using TEDBTables are going to be a viable
option for filtering large tables. Oh well, it's back to
SQL for me. >>

As I said, I'll be looking into a solution for this after 1.06 is out, so
there may be a solution by the end of the year.  I would have ported the
DBISAM functionality to EDB, but it really was problematic and not an ideal
solution.

--
Tim Young
Elevate Software
www.elevatesoft.com