Login ProductsSalesSupportDownloadsAbout |
Home Technical Support DBISAM Technical Support Support Forums DBISAM General View Thread |
Messages 1 to 10 of 13 total |
Error in table indexed - Filter / query not Full optimized |
Wed, Apr 30 2008 4:38 AM | Permanent Link |
"Enrico Ghezzi" | Hi
my system : Delphi 2007 upd 3 - Dbisam 4.24 local ( not C/S ) I have a big problem when make a filter or a query on my big ( 1'000'000 records ) database. I have indexs for All fields. but when i make a query like : WHERE CATEGORIA = 'X' AND REGIONE = 'Y' or table filter like : CATEGORIA = 'X' AND REGIONE = 'Y' 1000 records of filtered records. but is very slow. ( 15 seconds in local pc ) the problem is : tblIsamTemp.FilterOptimizeLevel = foPartial !!! when there are separated indexs for CATEGORIA and REGIONE WHY ? in dbys is always slow, too. my index are all INCASE , my filter optins is always INCASE i have try with UPPER(CATEGORIA) = UPPER('X') AND UPPER(REGIONE) = UPPER('Y') but notting.. always foPartial / slow. this is my SQL src : /* SQL-92 Table Creation Script with DBISAM Extensions */ DROP TABLE IF EXISTS "DataOffice"; CREATE TABLE IF NOT EXISTS "DataOffice" ( "ID" INTEGER, "RAGIONE_SOCIALE" VARCHAR(130), "INDIRIZZO" VARCHAR(32), "CAP" VARCHAR(5), "LOCALITA" VARCHAR(26), "PROVINCIA" VARCHAR(2), "TELEFONO" VARCHAR(18), "FAX" VARCHAR(18), "FORMA" VARCHAR(40), "EMAIL" VARCHAR(80), "WEB" VARCHAR(80), "CATEGORIA" VARCHAR(100), "REGIONE" VARCHAR(30), "COD_CLIENTE" INTEGER, "DATA" DATE, "COD_OPERATORE" SMALLINT, "NOTA" VARCHAR(100), PRIMARY KEY ("RecordID") COMPRESS NONE LOCALE "ANSI Standard" USER MAJOR VERSION 1 ); CREATE NOCASE INDEX IF NOT EXISTS "CATEGORIA" ON "DataOffice" ("CATEGORIA","RAGIONE_SOCIALE","LOCALITA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "RAGIONE_SOCIALE" ON "DataOffice" ("RAGIONE_SOCIALE","PROVINCIA","LOCALITA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "LOCALITA" ON "DataOffice" ("LOCALITA","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "PROVINCIA" ON "DataOffice" ("PROVINCIA","LOCALITA","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "CAP" ON "DataOffice" ("CAP","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "TELEFONO" ON "DataOffice" ("TELEFONO") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "REGIONE" ON "DataOffice" ("REGIONE","RAGIONE_SOCIALE","LOCALITA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "ID" ON "DataOffice" ("ID") COMPRESS FULL; -- delphi src - unit filternotoptimized; interface procedure CreateTables(CreateDatabaseName: string); implementation {$IFDEF VER180} uses Forms, Classes, DB, DBISAMTb, SysUtils, Variants; {$ENDIF} {$IFDEF VER140} uses Forms, Classes, DB, DBISAMTb, SysUtils, Variants; {$ENDIF} {$IFDEF VER130} uses Forms, Classes, DB, DBISAMTb, SysUtils; {$ENDIF} procedure CreateTables(CreateDatabaseName: string); var TableToCreate: TDBISAMTable; StopWords: TStrings; begin TableToCreate:=TDBISAMTable.Create(Application); try with TableToCreate do begin DatabaseName:=CreateDatabaseName; TableName:='dataoffice'; Exclusive:=True; if (not Exists) then begin with FieldDefs do begin Clear; Add('ID',ftInteger,0,False,'','','','',fcNoChange,0); Add('RAGIONE_SOCIALE',ftString,130,False,'','','','',fcNoChange,0); Add('INDIRIZZO',ftString,32,False,'','','','',fcNoChange,0); Add('CAP',ftString,5,False,'','','','',fcNoChange,0); Add('LOCALITA',ftString,26,False,'','','','',fcNoChange,0); Add('PROVINCIA',ftString,2,False,'','','','',fcNoChange,0); Add('TELEFONO',ftString,18,False,'','','','',fcNoChange,0); Add('FAX',ftString,18,False,'','','','',fcNoChange,0); Add('FORMA',ftString,40,False,'','','','',fcNoChange,0); Add('EMAIL',ftString,80,False,'','','','',fcNoChange,0); Add('WEB',ftString,80,False,'','','','',fcNoChange,0); Add('CATEGORIA',ftString,100,False,'','','','',fcNoChange,0); Add('REGIONE',ftString,30,False,'','','','',fcNoChange,0); Add('COD_CLIENTE',ftInteger,0,False,'','','','',fcNoChange,0); Add('DATA',ftDate,0,False,'','','','',fcNoChange,0); Add('COD_OPERATORE',ftSmallint,0,False,'','','','',fcNoChange,0); Add('NOTA',ftString,100,False,'','','','',fcNoChange,0); end; with IndexDefs do begin Clear; Add('','RecordID',[ixPrimary,ixUnique],'',icNone); Add('CATEGORIA','CATEGORIA;RAGIONE_SOCIALE;LOCALITA',[ixCaseInsensitive],'',icFull); Add('RAGIONE_SOCIALE','RAGIONE_SOCIALE;PROVINCIA;LOCALITA',[ixCaseInsensitive],'',icFull); Add('LOCALITA','LOCALITA;RAGIONE_SOCIALE',[ixCaseInsensitive],'',icFull); Add('PROVINCIA','PROVINCIA;LOCALITA;RAGIONE_SOCIALE',[ixCaseInsensitive],'',icFull); Add('CAP','CAP;RAGIONE_SOCIALE',[ixCaseInsensitive],'',icFull); Add('TELEFONO','TELEFONO',[ixCaseInsensitive],'',icFull); Add('REGIONE','REGIONE;RAGIONE_SOCIALE;LOCALITA',[ixCaseInsensitive],'',icFull); Add('ID','ID',[ixCaseInsensitive],'',icFull); end; StopWords:=TStringList.Create; try with StopWords do begin Add('A'); Add('AN'); Add('AND'); Add('BE'); Add('FOR'); Add('HOW'); Add('IN'); Add('IS'); Add('IT'); Add('OF'); Add('ON'); Add('OR'); Add('THAT'); Add('THE'); Add('THIS'); Add('TO'); Add('WAS'); Add('WHAT'); Add('WHEN'); Add('WHICH'); Add('WHY'); Add('WILL'); end; CreateTable(0,1,0,False,'','',4096,512,0,'',StopWords, #1+#2+#3+#4+#5+#6+#7+#8+#9+#10+#11+#12+#13 +#14+#15+#16+#17+#18+#19+#20+#21+#22+#23 +#24+#25+#26+#27+#28+#29+#30+#31+#32+'*+'+ ',-./:;<=>\`', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_ab'+ 'cdefghijklmnopqrstuvwxyz?,f".??^?S<OZ'+ '''"".--~Ts>ozY '+ ''+ ''); finally StopWords.Free; end; end; end; finally TableToCreate.Free; end; end; end. |
Wed, Apr 30 2008 5:01 AM | Permanent Link |
"Enrico Ghezzi" | add...
This single filter , work well : CATEGORIA = 'X' tblIsamTemp.FilterOptimizeLevel = foFull!!! This single filter , work well : REGIONE = 'Y' tblIsamTemp.FilterOptimizeLevel = foFull!!! but together is foPartial. HELP ^^ |
Wed, Apr 30 2008 9:21 AM | Permanent Link |
"Enrico Ghezzi" | i have update my dbisam to 4.26 B2
but don't work, always foPARTIAL when filter 2 fields indexeds. i have read the dbisam manual and i can read : ------------------ Optimization Levels DBISAM determines the level of optimization for a filter using the following rules: Optimized Condition AND Optimized Condition = Fully-Optimized filter ----------------- this is wrong. |
Wed, Apr 30 2008 11:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Enrico,
<< I have a big problem when make a filter or a query on my big ( 1'000'000 records ) database. I have indexs for All fields. but when i make a query like : WHERE CATEGORIA = 'X' AND REGIONE = 'Y' or table filter like : CATEGORIA = 'X' AND REGIONE = 'Y' 1000 records of filtered records. but is very slow. ( 15 seconds in local pc ) the problem is : tblIsamTemp.FilterOptimizeLevel = foPartial !!! >> Sometimes DBISAM will decide to make a filter or query condition partially optimized because it is more efficient that way. Is this query generating a live result set, or a canned result set ? If you could post a query plan for the query, that would help immensely. Also, since your indexes are case-insensitive, you definitely want to use this syntax: UPPER(CATEGORIA) = UPPER('X') AND UPPER(REGIONE) = UPPER('Y') in order to have DBISAM take advantage of the available indexes. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 30 2008 11:23 AM | Permanent Link |
"Enrico Ghezzi" | > Sometimes DBISAM will decide to make a filter or query condition partially
> optimized because it is more efficient that way. Is this query generating > a live result set, or a canned result set ? If you could post a query > plan for the query, that would help immensely. > > Also, since your indexes are case-insensitive, you definitely want to use > this syntax: > > UPPER(CATEGORIA) = UPPER('X') AND UPPER(REGIONE) = UPPER('Y') > > in order to have DBISAM take advantage of the available indexes. ( Local , new PC quadcore 4 gb ram... ) Query LiveResult : 16 seconds. Query canned : 23 seconds. I don't want to think with a normal pc on netwok disk..... too much time. always. not usable. i have try , already the Upper function. WHERE UPPER(CATEGORIA) = UPPER('X') AND UPPER(REGIONE) = UPPER('Y') or FILTER = 'UPPER(CATEGORIA) = UPPER('X') AND UPPER(REGIONE) = UPPER('Y')' always slow / foPartial. |
Wed, Apr 30 2008 2:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Enrico,
<< too much time. always. not usable. i have try , already the Upper function. >> Are you missing my requests for the query plan, or just ignorning them ? I can't tell you much more until you post a copy of the query plan for the query in question. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, May 1 2008 5:09 AM | Permanent Link |
"Frans van Daalen" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:566537CD-B884-4BDC-90D1-1AB4FDA68CAB@news.elevatesoft.com... > Are you missing my requests for the query plan, or just ignorning them ? > I can't tell you much more until you post a copy of the query plan for the > query in question. > Maybe he doesn't know what a query plan is Enrico : In dbsys there is an option to "Generate plan" and the third tab named "plan" will show the plan that the engine is executing for your query. |
Fri, May 2 2008 5:08 AM | Permanent Link |
"Enrico Ghezzi" | i was a OLD dbys.exe , witoout PLAN page.
this is the text ( not much info... no news info ) ================================================================================ SQL statement (Executed with 4.26 Build 2) ================================================================================ SELECT * from DataOffice2008 where UPPER(REGIONE) = UPPER('NEWZONE') and UPPER(CATEGORIA) = UPPER('SOFTWARE') Tables Involved --------------- DataOffice2008 (DataOffice2008) table opened shared, has 1252675 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- The expression: UPPER(REGIONE) = UPPER('NEWZONE') and UPPER(CATEGORIA) = UPPER('SOFTWARE') is PARTIALLY-OPTIMIZED, covers 20166 rows or index keys, costs 21815578 bytes, and will be applied to the DataOffice2008 table (DataOffice2008) before any joins ================================================================================ >>>>> 4851 rows affected in 24,836 seconds ================================================================================ now , LIVE query : ================================================================================ SQL statement (Executed with 4.26 Build 2) ================================================================================ SELECT * from DataOffice2008 where UPPER(REGIONE) = UPPER('NEWZONE') and UPPER(CATEGORIA) = UPPER('SOFTWARE') Tables Involved --------------- DataOffice2008 (DataOffice2008) table opened shared, has 1252675 rows Result Set Generation --------------------- Result set will be live Result set will consist of one or more rows Result set will be ordered by the primary index for the table DataOffice2008 WHERE Clause Execution ---------------------- The expression: UPPER(REGIONE) = UPPER('NEWZONE') and UPPER(CATEGORIA) = UPPER('SOFTWARE') is PARTIALLY-OPTIMIZED, covers 20166 rows or index keys, costs 21815578 bytes, and will be applied to the DataOffice2008 table (DataOffice2008) before any joins ================================================================================ >>>>> 4851 rows affected in 27,878 seconds ================================================================================ |
Fri, May 2 2008 5:12 AM | Permanent Link |
"Enrico Ghezzi" | this is a Reverse Engineer with a new dbsys 2008
/* SQL-92 Table Creation Script with DBISAM Extensions */ CREATE TABLE IF NOT EXISTS "DataOffice2008" ( "ID" INTEGER, "RAGIONE_SOCIALE" VARCHAR(130), "INDIRIZZO" VARCHAR(32), "CAP" VARCHAR(5), "LOCALITA" VARCHAR(26), "PROVINCIA" VARCHAR(2), "TELEFONO" VARCHAR(18), "FAX" VARCHAR(18), "FORMA_GIURIDICA" VARCHAR(40), "EMAIL" VARCHAR(80), "WEB" VARCHAR(80), "CATEGORIA" VARCHAR(100), "REGIONE" VARCHAR(30), "COD_CLIENTE" INTEGER, "DATA" DATE, "COD_OPERATORE" SMALLINT, "NOTA" VARCHAR(100), PRIMARY KEY ("RecordID") COMPRESS NONE LOCALE CODE 0 USER MAJOR VERSION 1 ); CREATE NOCASE INDEX IF NOT EXISTS "CATEGORIA" ON "DataOffice2008" ("CATEGORIA","RAGIONE_SOCIALE","LOCALITA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "RAGIONE_SOCIALE" ON "DataOffice2008" ("RAGIONE_SOCIALE","PROVINCIA","LOCALITA") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "LOCALITA" ON "DataOffice2008" ("LOCALITA","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "PROVINCIA" ON "DataOffice2008" ("PROVINCIA","LOCALITA","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "CAP" ON "DataOffice2008" ("CAP","RAGIONE_SOCIALE") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "TELEFONO" ON "DataOffice2008" ("TELEFONO") COMPRESS FULL; CREATE NOCASE INDEX IF NOT EXISTS "REGIONE" ON "DataOffice2008" ("REGIONE","RAGIONE_SOCIALE","LOCALITA") COMPRESS FULL; CREATE UNIQUE NOCASE INDEX IF NOT EXISTS "ID" ON "DataOffice2008" ("ID") COMPRESS FULL; |
Fri, May 2 2008 9:34 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Enrico,
<< this is the text ( not much info... no news info ) >> You should try adding an index to the table on just the CATEGORIA column or REGIONE column, depending upon which is more selective. I suspect that the issue is that the index keys are simply so large that the indexes are requiring a lot of I/O. As for the partial-optimize, DBISAM usually makes that decision when the less-selective (2nd) index scan is more expensive in terms of I/O than performing a row scan based upon the results of the more selective index scan (1st). It looks at the I/O costs of both index scans, picks the smallest, and then determines if it should go ahead with the second index scan or perform a row scan instead. Also, do you really need 100 characters for the CATEGORIA column ? That's an awful lot of wasted space, if not. -- 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 Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |