Hi all ~

in the very simple case shown below I get an foNone instead of a full opt.
I have verified that I am on the right table and that the table is error-free.
I am using 4.25 VCL under D5/XP Home.

What could be the reason?

TIA,
Kai


...snip...

   _PatientsTable.Filtered := False;
   _PatientsTable.Filter   := filter;                          // filter in debug mode:
Record_Status = 'A'
   //
   case _PatientsTable.FilterOptimizeLevel of           //
_PatientsTable.IndexDefs.Items[ 20 ].Fields = 'Record_Status'
     foNone:
     begin
       if ( MessageDialog( 'This query is not optimized and may take a long time. Continue?',
            mtConfirmation, [ mbYes, mbNo ]) = mrNo ) then
         Exit;
     end;
     foPartial:
     begin
       if ( MessageDialog( 'This query is only partially optimized and may take some
time. Continue?',
            mtConfirmation, [ mbYes, mbNo ]) = mrNo ) then
         Exit;
     end;
   end;
   _PatientsTable.Filtered := True;

...snip...

Kai,

<< in the very simple case shown below I get an foNone instead of a full
opt. I have verified that I am on the right table and that the table is
error-free. I am using 4.25 VCL under D5/XP Home. >>

Try sticking the filter expression in a TDBISAMQuery in the form of:

SELECT * FROM MyTable
WHERE <FilterExpression>

and make sure that the GeneratePlan property is set to True.  If you could
then execute the query and post the resulting plan from the Plan property
here so that I can see what it is doing, I can then tell you what the cause
is.

--
Tim Young
Elevate Software
www.elevatesoft.com

Here you go, Tim:

As you will see from the reverse eng. statement for this table, there is an index on the
record_status field:

================================================================================
SQL statement (Executed with 4.25 Build 3)
================================================================================

select * from patients where record_status = 'A'

Tables Involved
---------------

patients (patients) table opened shared, has 144056 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

The expression:

record_status = 'A'

is UN-OPTIMIZED and will be applied to each candidate row in the patients table
(patients) as the result set is generated

================================================================================
>>>>> 144056 rows affected in 7.371 seconds
================================================================================



/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "C:\medipak\Patients"
(
  "Patient_Number" VARCHAR(9) NOT NULL,
  "Last_Name" VARCHAR(20) NOT NULL,
  "First_Name" VARCHAR(15) NOT NULL,
  "Salutation" CHAR(4) NOT NULL,
  "Initials" CHAR(4) NOT NULL CHARCASE UPPER,
  "Address" VARCHAR(20) NOT NULL,
  "City" VARCHAR(20) NOT NULL,
  "Province" CHAR(2) NOT NULL DEFAULT 'BC' CHARCASE UPPER,
  "Postal" CHAR(6),
  "Phone" VARCHAR(15),
  "Birth_Date" DATE NOT NULL,
  "Film_Number" CHAR(7) CHARCASE UPPER,
  "MSP_Number" CHAR(12),
  "MSP_DependantCount" SMALLINT,
  "SIN" VARCHAR(11),
  "PHN" CHAR(12),
  "Referring_Doctor1" CHAR(5),
  "Previous_Film" CHAR(7) CHARCASE UPPER,
  "Other_ID" VARCHAR(15),
  "Film_Location" VARCHAR(4),
  "Film_DoctorNumber" CHAR(5),
  "Film_DoctorLocation" CHAR(1),
  "Film_Date" DATE,
  "Previous_FilmLocation" VARCHAR(4),
  "Previous_FilmDoctorNumber" CHAR(5),
  "Previous_FilmDoctorLocation" CHAR(1),
  "Previous_FilmDate" DATE,
  "Notes" MEMO,
  "Last_Update" DATE NOT NULL DEFAULT Current_Date,
  "Record_Status" CHAR(1) NOT NULL DEFAULT 'A' CHARCASE UPPER,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE NOCASE INDEX IF NOT EXISTS "Record_Status" ON "C:\medipak\Patients" ("Record_Status");

Kai

When you define a case-insensitive index you must force UPPER in the SQL
statement (or filter).

> CREATE NOCASE INDEX IF NOT EXISTS "Record_Status" ON "C:\medipak\Patients"
> ("Record_Status");

Then your filter should be
UPPER(record_status) = UPPER('A')

and the SQL statement should be
where UPPER(record_status) = UPPER('A')

Then DBISAM will use the correct index to optimize the results.

Eduardo

Thanks Eduardo ~

time for me to read up on case-insensitive indices - something I formed an opinion on but
that I
obviously do not yet understand

Kai

Kai,

<< As you will see from the reverse eng. statement for this table, there is
an index on the record_status field: >>

Eduardo is correct.  See here for more information:

http://www.elevatesoft.com/dbisam4d5_filter_optimization.htm
http://www.elevatesoft.com/dbisam4d5_optimizations.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

PMFJI,
     I noticed a lot of Delphi databases you have to apply "Upper(Col)='ABC'" on a case
insensitive index, and I just don't get it.
MySQL indexes are case insenstive by default and we can use "Where col='ABC'" or
"col='AbC'" and it returns the same rows. So why can't the database engine be smart enough
to realize the index is case insensitive, and add the Upper() part for us behind the
scene. Otherwise why would someone want to perform a case sensitive search on a case
insensitive index which means going through the entire table? The Delphi databases seem to
default to unoptimized when searching on case insensitive indexes. The engine should be
making the user work less, not more. Just my 2 cents.

Sam

Sam,

The test is of the field and not the index, so col='AbC' should return a
different result set than col='ABC'. In fact the two results sets should
have no common rows because 'AbC' and 'ABC' are not equal.

However, it is possible to partially optimize both of these conditions
because both result sets are subsets of Upper(Col) = Upper('ABC'). Of
course I don't know how difficult this test for this optimization and
apply it in practice - that is a question for Tim. Its also questionable
whether adding this kind of optimization would be worthwhile since those
who require a case sensitive match would be better served by using a
case sensitive index anyway.

Sounds like what you would like to see is a compatibility option to
force all string comparisons to be evaluated as case-insensitive. It
would be a mistake in my opinion to change the way a SQL statement
parses based solely on existence of a case insensitive index since one
could inadvertently break existing code by just adding an index.

Sean


Sam Karl wrote:
> PMFJI,
>       I noticed a lot of Delphi databases you have to apply "Upper(Col)='ABC'" on a case
> insensitive index, and I just don't get it.
> MySQL indexes are case insenstive by default and we can use "Where col='ABC'" or
> "col='AbC'" and it returns the same rows. So why can't the database engine be smart enough
> to realize the index is case insensitive, and add the Upper() part for us behind the
> scene. Otherwise why would someone want to perform a case sensitive search on a case
> insensitive index which means going through the entire table? The Delphi databases seem to
> default to unoptimized when searching on case insensitive indexes. The engine should be
> making the user work less, not more. Just my 2 cents.
>
> Sam
>

Sean McCall <someone@somewhere.net> wrote in
news:607AB4BA-0D55-4063-B27B-39FF987C16C2@news.elevatesoft.com:

> Sounds like what you would like to see is a compatibility option to
> force all string comparisons to be evaluated as case-insensitive. It
> would be a mistake in my opinion to change the way a SQL statement
> parses based solely on existence of a case insensitive index since one
> could inadvertently break existing code by just adding an index.

Sean,

 I have to agree with Sam, as it took me quite a bit of swallowing to
get my head around this UPPER(xxx) = UPPER(yyy) after Informix and MSSQL
where I used WHERE <this field> = <'this value'> on case-insensitive
indexes or case-sensitive indexes depending on the way the application
needs to work, and got the appropriate result set.

Indeed, this does work differently for apparently identical SQL, but only
because the index says it should. This is exactly how specifying UNIQUE
works.

What Sam wants, ISTM, and what I initially expected (and keep on
forgetting I won't get) is an index that silently takes a peek at its own
definition and acts accordingly, without the programmer having to add
these unnecessary and obfuscating constructs, just as it does to decide
whether it can accept a new row or not on a unique index.

There is also the problem of collation for e.g. French accented
characters, where é=è=ê=e=E in a case-insensitive index, except in DBISAM
where they are only the same if included in UPPER(). If you use LOWER()
they should not be seen as the same - I haven't tried that one on a case-
insensitive index in DBISAM.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)

Sam,

<< I noticed a lot of Delphi databases you have to apply "Upper(Col)='ABC'"
on a case insensitive index, and I just don't get it.
MySQL indexes are case insenstive by default and we can use "Where
col='ABC'" or "col='AbC'" and it returns the same rows. So why can't the
database engine be smart enough to realize the index is case insensitive,
and add the Upper() part for us behind the scene. Otherwise why would
someone want to perform a case sensitive search on a case insensitive index
which means going through the entire table? >>

Indexes don't even exist in the SQL standard, so forget them when it comes
to how an SQL statement should work.  SQL statements should work exactly as
written, which is not something that MySQL and others are doing if they take
a case-sensitive search and make it case-insensitive simply because an index
exists on the column being compared.  In fact, I have a hard-time believing
that this is even the case.  Perhaps what you meant to say was that, by
default, all MySQL string column comparisons are case-insensitive ?

EDB, for example, will allow you to perform a case-insensitive search on a
column without the UPPER() provided that the collation assigned to the
column includes the case-insensitive attribute.  However, that is different
from an index definition because it is standard part of a table definition
and results in the same rows being returned regardless of whether an index
exists or not.  DBISAM doesn't have column-level collations, so it has to
rely on the UPPER() construct for you to tell it that the comparison is
case-insensitive.

This is the key principle - searches and comparisons should perform in the
same fashion and return the exact same rows irrespective of any defined
indexes.  The SQL standard dictates that all comparions are case-sensitive
unless the collation used dictates otherwise.

--
Tim Young
Elevate Software
www.elevatesoft.com