Hi,

I will make my question fast and clear:
This query is running faster in with DBISAM3 more than EDB 1.05!

Note:  I am using the same query, data, pc, and everything the same.

Here is the query and the execution plan.

SELECT TB_Items.YearSysNo,
      TB_Items.ItemSysNo,
      TB_Items.ItemNo,
      TB_Items.ItemName_A,
      TB_Items.ItemName_E,
      TB_Items.ItemPartNo,
      TB_Items.ItemBrandName,
      TB_Items.ItemColor,
      TB_Items.ItemSize,
      TB_Items.BranchSysNo,
      TB_Items.StoreSysNo,
      TB_Items.UnitSysNo,
      TB_Items.SalesPrice,
      TB_Items.PurchasePrice,
      TB_Items.AveragePurchasePrice,
      TB_Items.ItemProfit,
      TB_Items.ProfitPercentage,
      TB_Items.ItemDiscountAmount,
      TB_Items.ItemDiscountPercentage,
      TB_Items.OpeningQuantity,
      TB_Items.ItemQuantity,
      TB_Items.PrivateSalesPolicy,
      TB_Items.DiscountMethod,
      TB_Items.ItemLocation,
      TB_Items.ItemLocation2,
      TB_Items.DateLastSold,
      TB_Items.DateLastPurchase,
      TB_Items.VendorLastPurchase,
      TB_Items.TransportationCost,
      TB_Items.UnitBarcodeNo,
      TB_Items.Notes

FROM  TB_Items

WHERE TB_Items.BranchSysNo = 1
AND TB_Items.YearSysNo   = 0

ORDER BY ItemNo
Asc


NOW THE EXCUTION PLAN:

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

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
"TB_Items"."YearSysNo" AS "YearSysNo",
"TB_Items"."ItemSysNo" AS "ItemSysNo",
"TB_Items"."ItemNo" AS "ItemNo",
"TB_Items"."ItemName_A" AS "ItemName_A",
"TB_Items"."ItemName_E" AS "ItemName_E",
"TB_Items"."ItemPartNo" AS "ItemPartNo",
"TB_Items"."ItemBrandName" AS "ItemBrandName",
"TB_Items"."ItemColor" AS "ItemColor",
"TB_Items"."ItemSize" AS "ItemSize",
"TB_Items"."BranchSysNo" AS "BranchSysNo",
"TB_Items"."StoreSysNo" AS "StoreSysNo",
"TB_Items"."UnitSysNo" AS "UnitSysNo",
"TB_Items"."SalesPrice" AS "SalesPrice",
"TB_Items"."PurchasePrice" AS "PurchasePrice",
"TB_Items"."AveragePurchasePrice" AS "AveragePurchasePrice",
"TB_Items"."ItemProfit" AS "ItemProfit",
"TB_Items"."ProfitPercentage" AS "ProfitPercentage",
"TB_Items"."ItemDiscountAmount" AS "ItemDiscountAmount",
"TB_Items"."ItemDiscountPercentage" AS "ItemDiscountPercentage",
"TB_Items"."OpeningQuantity" AS "OpeningQuantity",
"TB_Items"."ItemQuantity" AS "ItemQuantity",
"TB_Items"."PrivateSalesPolicy" AS "PrivateSalesPolicy",
"TB_Items"."DiscountMethod" AS "DiscountMethod",
"TB_Items"."ItemLocation" AS "ItemLocation",
"TB_Items"."ItemLocation2" AS "ItemLocation2",
"TB_Items"."DateLastSold" AS "DateLastSold",
"TB_Items"."DateLastPurchase" AS "DateLastPurchase",
"TB_Items"."VendorLastPurchase" AS "VendorLastPurchase",
"TB_Items"."TransportationCost" AS "TransportationCost",
"TB_Items"."UnitBarcodeNo" AS "UnitBarcodeNo",
"TB_Items"."Notes" AS "Notes"
FROM "TB_Items"
WHERE "TB_Items"."BranchSysNo" = 1 AND "TB_Items"."YearSysNo" = 0
ORDER BY "TB_Items"."ItemNo" ASC

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

TB_Items: 71523 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 ItemNo

Filtering
---------

The following filter condition was applied to the Query table:

"TB_Items"."BranchSysNo" = 1 [Index scan: 7947 keys, 128000 bytes estimated
cost] AND "TB_Items"."YearSysNo" = 0 [Index scan: 53361 keys, 768000 bytes estimated
cost]

================================================================================
7947 row(s) returned in 0.219 secs
================================================================================

I've forget to mention one point and I am sure it's not the problem.  DBISAM3 doesn't have the "ExecutionTime" property.  Therefore I used my own
simple method in both versions.  Here is my code:

StartTiming;
EDBQuery.Open;
StopTiming;

Abdulaziz,

<< I will make my question fast and clear:
This query is running faster in with DBISAM3 more than EDB 1.05!  >>

What is the speed for DBISAM 3 ?  Faster is a relative term, so are we
talking a few milliseconds ?  EDB uses estimates for I/O costs, so it may
simply be due to a small difference in the relative vs. actual I/O costs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<What is the speed for DBISAM 3 ?  Faster is a relative term, so are we
talking a few milliseconds ?  EDB uses estimates for I/O costs, so it may
simply be due to a small difference in the relative vs. actual I/O costs.>>

EDB build 1.05  takes 0.80 seconds
DBISAM3 takes 0.15 seconds.

I am running the test in single user mode in a local machine.  The point here is that I was expecting better performance with EDB over DBISAM3.  
The performance could change when it comes to multi-user or client/server environment.  But I have to hear from somebody.

Abdulaziz,

<< EDB build 1.05  takes 0.80 seconds
DBISAM3 takes 0.15 seconds.

I am running the test in single user mode in a local machine.  The point
here is that I was expecting better performance with EDB over DBISAM3.  The
performance could change when it comes to multi-user or client/server
environment.  But I have to hear from somebody. >>

If you want to send me the DBISAM 3 tables, I can take a look here and see
what I can find.   More than likely the issue is related to positioning the
cursor on the first row in the result set, which can take a little bit of
time when dealing with sensitive result sets and ORDER BY clauses.

--
Tim Young
Elevate Software
www.elevatesoft.com

Abdulaziz


What are you doing to smooth out the effects of Windows caching, and have you tried raising the amount of memory ElevateDB grabs for its caches. I seem to recall that V3 used a lot of memory by comparison with V4 (and I think ElevateDB).

Roy Lambert

Roy,

<<What are you doing to smooth out the effects of Windows caching, and have you tried raising the amount of memory ElevateDB grabs for its
caches. I seem to recall that V3 used a lot of memory by comparison with V4 (and I think ElevateDB).>>

Yes, I played with buffers and could not achieve anything with this query.

Tim,

<<If you want to send me the DBISAM 3 tables, I can take a look here and see
what I can find.   More than likely the issue is related to positioning the
cursor on the first row in the result set, which can take a little bit of
time when dealing with sensitive result sets and ORDER BY clauses.>>

Could you provide me with the email?

Tim,

<<If you want to send me the DBISAM 3 tables, I can take a look here and see
what I can find.   More than likely the issue is related to positioning the
cursor on the first row in the result set, which can take a little bit of
time when dealing with sensitive result sets and ORDER BY clauses.>>

Sorry I found it:

support@elevatesoft.com

Roy Lambert wrote:
> Abdulaziz
>
>
> What are you doing to smooth out the effects of Windows caching, and have you tried raising the amount of memory ElevateDB grabs for its caches. I seem to recall that V3 used a lot of memory by comparison with V4 (and I think ElevateDB).
>
> Roy Lambert
>

Roy,
   Have you gotten any speed improvement by changing the buffers on
your tables? For me the speed stays the same. I'm waiting for the ENT
version to see if that solves the problem.

Dave