I have just tried transferring some data from DBISAM v4 to EDB V1 Build 9.

When I run the following SQL on DBSys it takes 0.06 seconds

SELECT * FROM Database
WHERE UPPER(CatalogueNumber) LIKE UPPER('K27%')

If I run it in EDB it takes 2.7 seconds, and says that it is using the Primary index
not the CatalogueNumber index.

Why would this be?

Chris Holland

Correction it says:

The result set was ordered using the index PrimaryIndex not filtered using it.

Chris Holland


Chris Holland wrote:
> I have just tried transferring some data from DBISAM v4 to EDB V1 Build 9.
>
> When I run the following SQL on DBSys it takes 0.06 seconds
>
> SELECT * FROM Database
> WHERE UPPER(CatalogueNumber) LIKE UPPER('K27%')
>
> If I run it in EDB it takes 2.7 seconds, and says that it is using the
> Primary index
> not the CatalogueNumber index.
>
> Why would this be?
>
> Chris Holland

I am not sure if this has anything to do with it but I imported the table from DBISAM v4
and the CatalogueNumber index was marked as case insensitive.

The CatalogueNumber index in EDB has case insensitive unticked.

Chris Holland


Chris Holland wrote:
> Correction it says:
>
> The result set was ordered using the index PrimaryIndex not filtered
> using it.
>
> Chris Holland
>
>
> Chris Holland wrote:
>> I have just tried transferring some data from DBISAM v4 to EDB V1
>> Build 9.
>>
>> When I run the following SQL on DBSys it takes 0.06 seconds
>>
>> SELECT * FROM Database
>> WHERE UPPER(CatalogueNumber) LIKE UPPER('K27%')
>>
>> If I run it in EDB it takes 2.7 seconds, and says that it is using the
>> Primary index
>> not the CatalogueNumber index.
>>
>> Why would this be?
>>
>> Chris Holland

Here is the execution plan:

================================================================================
SQL Query (Executed by ElevateDB 1.00 Build 9)

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
"Database"."PIN" AS "PIN",
"Database"."ManufacturerCode" AS "ManufacturerCode",
"Database"."SortKey" AS "SortKey",
"Database"."ManufacturerGroup" AS "ManufacturerGroup",
"Database"."CatalogueNumber" AS "CatalogueNumber",
"Database"."BarCode" AS "BarCode",
"Database"."PartNumber" AS "PartNumber",
"Database"."Category" AS "Category",
"Database"."MainDescription" AS "MainDescription",
"Database"."SubDescription" AS "SubDescription",
"Database"."Colour" AS "Colour",
"Database"."Size" AS "Size",
"Database"."Option" AS "Option",
"Database"."TradePriceSplit" AS "TradePriceSplit",
"Database"."TradePriceCarton" AS "TradePriceCarton",
"Database"."QuantitySplit" AS "QuantitySplit",
"Database"."QuantityCarton" AS "QuantityCarton",
"Database"."SupplierDiscount" AS "SupplierDiscount",
"Database"."AgreedSupplierNetPrice" AS "AgreedSupplierNetPrice",
"Database"."BestSupplierNetPrice" AS "BestSupplierNetPrice",
"Database"."VatCode" AS "VatCode",
"Database"."UseSplitPrice" AS "UseSplitPrice",
"Database"."IgnoreZeroCost" AS "IgnoreZeroCost",
"Database"."LockDiscount" AS "LockDiscount",
"Database"."MeasureUnit" AS "MeasureUnit",
"Database"."FixNumber" AS "FixNumber",
"Database"."MaterialClass" AS "MaterialClass",
"Database"."LabourClassA" AS "LabourClassA",
"Database"."LabourClassB" AS "LabourClassB",
"Database"."LabourClassC" AS "LabourClassC",
"Database"."FitA1" AS "FitA1",
"Database"."FitA2" AS "FitA2",
"Database"."FitA3" AS "FitA3",
"Database"."FitB1" AS "FitB1",
"Database"."FitB2" AS "FitB2",
"Database"."FitB3" AS "FitB3",
"Database"."FitC1" AS "FitC1",
"Database"."FitC2" AS "FitC2",
"Database"."FitC3" AS "FitC3",
"Database"."UseFitTimeA" AS "UseFitTimeA",
"Database"."UseFitTimeB" AS "UseFitTimeB",
"Database"."UseFitTimeC" AS "UseFitTimeC",
"Database"."FitDescriptionRecno" AS "FitDescriptionRecno",
"Database"."LockFit" AS "LockFit",
"Database"."IgnoreZeroFit" AS "IgnoreZeroFit",
"Database"."PreferredSupplierCode" AS "PreferredSupplierCode",
"Database"."LastChangedDate" AS "LastChangedDate",
"Database"."SalesNominalCode" AS "SalesNominalCode",
"Database"."PurchaseNominalCode" AS "PurchaseNominalCode",
"Database"."SageDepartment" AS "SageDepartment"
FROM "Database"
WHERE UPPER("CatalogueNumber") LIKE UPPER('K27%')

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

Database: 336024 rows

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

The result set was live
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 Database table:

UPPER("CatalogueNumber") LIKE UPPER('K27%') [Row scan: 336024 rows, 177420672
bytes estimated cost]

================================================================================
13 row(s) returned in 3.141 secs
================================================================================

Chris Holland

Chris,

<< When I run the following SQL on DBSys it takes 0.06 seconds

SELECT * FROM Database
WHERE UPPER(CatalogueNumber) LIKE UPPER('K27%')

If I run it in EDB it takes 2.7 seconds, and says that it is using the
Primary index not the CatalogueNumber index. >>

Is the CatalogueNumber column using the ANSI collation in EDB ?  If so, then
use this instead:

SELECT * FROM Database
WHERE CatalogueNumber COLLATE "ANSI_CI" LIKE 'K27%'

http://www.elevatesoft.com/edb1migrate_operators.htm

If the column is using some other collation, just replace the "ANSI" part
with the collation name and leave the "CI" (case-insensitive) part.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thats got it down to 0.01 seconds

Thanks

Chris Holland


Tim Young [Elevate Software] wrote:
> Chris,
>
> << When I run the following SQL on DBSys it takes 0.06 seconds
>
>  SELECT * FROM Database
> WHERE UPPER(CatalogueNumber) LIKE UPPER('K27%')
>
>  If I run it in EDB it takes 2.7 seconds, and says that it is using the
> Primary index not the CatalogueNumber index. >>
>
> Is the CatalogueNumber column using the ANSI collation in EDB ?  If so, then
> use this instead:
>
> SELECT * FROM Database
> WHERE CatalogueNumber COLLATE "ANSI_CI" LIKE 'K27%'
>
> http://www.elevatesoft.com/edb1migrate_operators.htm
>
> If the column is using some other collation, just replace the "ANSI" part
> with the collation name and leave the "CI" (case-insensitive) part.
>

Chris Holland wrote:
> Thats got it down to 0.01 seconds

Nice, this shows that EDB is faster!