I am testing EDB and was a bit suprised by the speed of this simple query:

select * from addresses where name like 'Be%';

Time in DBISAM 3.30 = 0.125 sec
Time in EDB 1.08 = 0.79 sec.

Same result with this query:

select * from addresses where name = 'Muller';

Table was migrated from DBISAM, Index on Name. About 5400 records in the table. Am I
missing anything?

Regards Felix

Felix wrote:
> I am testing EDB and was a bit suprised by the speed of this simple query:
>
> select * from addresses where name like 'Be%';
>
> Time in DBISAM 3.30 = 0.125 sec
> Time in EDB 1.08 = 0.79 sec.
>
> Same result with this query:
>
> select * from addresses where name = 'Muller';
>
> Table was migrated from DBISAM, Index on Name. About 5400 records in the table. Am I
> missing anything?
>
> Regards Felix
>

Felix,
    I've found that DBISAM makes better use of the OS cache so if you
run the DBISAM query twice, the second query will run much faster than
the first. Not so with EDB. So you could be experiencing the Windows
caching effect.

Dave

<<  I've found that DBISAM makes better use of the OS cache so if you
run the DBISAM query twice, the second query will run much faster than
the first. Not so with EDB. So you could be experiencing the Windows
caching effect. >>

That's true, then the difference is even bigger. But also the first execution is much slower.

Felix

Dave,

<< I've found that DBISAM makes better use of the OS cache so if you run the
DBISAM query twice, the second query will run much faster than the first.
Not so with EDB. So you could be experiencing the Windows caching effect. >>

There is absolutely no difference at all between the way DBISAM and EDB deal
with the OS file system cache.  The difference in times is most likely
entirely down to the index key comparisons on the VARCHAR/CHAR columns, just
like you experienced with your tests.

--
Tim Young
Elevate Software
www.elevatesoft.com

Felix,

<< Same result with this query:

select * from addresses where name = 'Muller';

Table was migrated from DBISAM, Index on Name. About 5400 records in the
table. Am I missing anything? >>

There is a slightly larger overhead in EDB for VARCHAR/CHAR column
comparisons.  How large is the Name column in characters ?   The larger the
column, the greater the overhead.

--
Tim Young
Elevate Software
www.elevatesoft.com

Felix,

<< I am testing EDB and was a bit suprised by the speed of this simple
query: >>

BTW, are you setting RequestSensitive to True for the TEDBQuery component
when you run that query, or is it set to False ?

--
Tim Young
Elevate Software
www.elevatesoft.com

<<
There is a slightly larger overhead in EDB for VARCHAR/CHAR column
comparisons.  How large is the Name column in characters ?   The larger the
column, the greater the overhead.
>>

The Name column has a length of 40.

Regards Felix

Felix,

<< The Name column has a length of 40. >>

Is there a lot of blank space ?  If so, then that is most likely the main
culprit.  EDB has to adhere to the SQL standard of only comparing non-blank
space, so there is some additional overhead there also for CHAR columns.

Could you possibly send me the table via email so that I can test the
differences here and see exactly what the reason is ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

<<
Is there a lot of blank space ?  If so, then that is most likely the main
culprit.  EDB has to adhere to the SQL standard of only comparing non-blank
space, so there is some additional overhead there also for CHAR columns.

Could you possibly send me the table via email so that I can test the
differences here and see exactly what the reason is ?
>>

Sure, both DBISAM and EDB tables are sent.

Thanks. Felix

Tim Young [Elevate Software] wrote:
> Dave,
>
> << I've found that DBISAM makes better use of the OS cache so if you run the
> DBISAM query twice, the second query will run much faster than the first.
> Not so with EDB. So you could be experiencing the Windows caching effect. >>
>
> There is absolutely no difference at all between the way DBISAM and EDB deal
> with the OS file system cache.  The difference in times is most likely
> entirely down to the index key comparisons on the VARCHAR/CHAR columns, just
> like you experienced with your tests.
>
Tim,
    Then why is the second time the DBISAM query is executed so much
faster than the second time the EDB query is executed? In other words,
the DBISAM query speed improves dramatically the second time it is run
compared to a small improvement with EDB?

Dave