Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 13 total
Thread Error in table indexed - Filter / query not Full optimized
Wed, Apr 30 2008 4:38 AMPermanent 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 AMPermanent 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.  Frown


HELP ^^
Wed, Apr 30 2008 9:21 AMPermanent Link

"Enrico Ghezzi"
i have update my dbisam to 4.26 B2

but don't work,

always foPARTIAL when filter 2 fields indexeds.   Frown


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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent 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..... Frown

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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent 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 Smile

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 AMPermanent 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 AMPermanent 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 2Next Page
Jump to Page:  1 2
Image