Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 15 total |
Filter optimization stumper |
Tue, Feb 6 2007 3:24 PM | Permanent Link |
Kai | 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... |
Tue, Feb 6 2007 7:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 6 2007 7:51 PM | Permanent Link |
Kai | 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"); |
Wed, Feb 7 2007 4:26 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Wed, Feb 7 2007 12:12 PM | Permanent Link |
Kai | 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 |
Wed, Feb 7 2007 4:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Feb 8 2007 1:02 AM | Permanent Link |
Sam Karl | 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 |
Thu, Feb 8 2007 10:32 AM | Permanent Link |
Sean McCall | 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 > |
Thu, Feb 8 2007 11:53 AM | Permanent Link |
Chris Erdal | 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) |
Thu, Feb 8 2007 1:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |