Hi,

I am using following expression:

   ( Placano = 'J' )
       and
   ( Storno  = 'N' )
       and
   ( Datum_Od BETWEEN '2008-01-01' AND '2008-12-31' )
       and
   ( Datum_Do BETWEEN '2008-01-01' AND '2008-12-31' )

Why is this filter only partially optimized?

Table has few indexes between them also 4 indexes on every field used in
expression.

Using UPPER(..)=UPPER(...) doesn't change anything as indexes are case insens.

Fields:
Placano, Char(1)
Storno, Char(1)
Datum_od, Date
Datum_do, Date

I tested this in DBsys.

TIA for any answer,

Joze

Joze,

<< I am using following expression:

  ( Placano = 'J' )
       and
   ( Storno  = 'N' )
       and
   ( Datum_Od BETWEEN '2008-01-01' AND '2008-12-31' )
       and
   ( Datum_Do BETWEEN '2008-01-01' AND '2008-12-31' )

Why is this filter only partially optimized? >>

Could you post the query plan for the query along with the table structures
(CREATE TABLE and CREATE INDEX statements from the DBSYS reverse-engineering
facilities are fine).

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

After rebuilding tables and indexes everything is OPTIMIZED

There was some problem somewhere before that.

Thanks.

Regards, Joze

Joze,

<< After rebuilding tables and indexes everything is OPTIMIZED >>

What do you mean by "rebuild" ?  Did you use the OptimizeTable method to
optimize the table(s) ?

--
Tim Young
Elevate Software
www.elevatesoft.com

>What do you mean by "rebuild" ?  Did you use the OptimizeTable method to
>optimize the table(s) ?

My problems was on my laptop. Filter was allways only partially optimized. Then
I noticed I had problem also with my master-detail relation. Something was
obviously wrong with my indexes.

Then I moved my test program to my main computer. I started with creating table
and filling with data. This is "rebuilt" table I was talking. On my main
computer same test application with "rebuilt table" works without problem. All
filters are optimized and also my master-detail relation works again.

So on the laptop there was some problem with my table or its indexes.
When I was asking this question here at the forum I was working with my laptop.
When I tried it on other computer everything was OK.

So DBISAM works OK, filter is optimized.

Best regards,

Joze

I still have my old table on the laptop with only partially optimized filter.
Verify, repair and optimize table doesn't help.

Here are details:

================================================================================
SQL statement (Executed with 4.22 Build 1)
================================================================================

SELECT                                                                          
 N.ID_placnika,
 SUM(N.Vse_meritve_skupaj) AS Sestevek
FROM
 Narocila N
WHERE
 (
   ( N.Placano = 'J' )
       and
   ( N.Storno  = 'N' )
       and
   ( N.Datum_Od BETWEEN '2008-01-01' AND '2008-12-31' )
       and
   ( N.Datum_Do BETWEEN '2008-01-01' AND '2008-12-31' )
 )
GROUP BY N.ID_placnika

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

Narocila (N) table opened shared, has 70 rows

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

Result set will be canned

Result set will consist of one or more grouped rows

Result set will be grouped by the following column(s) using a temporary index:

ID_placnika

Result set will be ordered by the temporary index created for the grouping

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

The expression:

N.Placano = 'J' and N.Storno = 'N' and N.Datum_Od BETWEEN '2008-01-01' AND
'2008-12-31' and N.Datum_Do BETWEEN '2008-01-01' AND '2008-12-31'

has been rewritten and is PARTIALLY-OPTIMIZED, covers 4 rows or index keys,
costs 1767 bytes, and will be applied to the Narocila table (N) before any joins

================================================================================
>>>>> 1 rows affected in 0 seconds
================================================================================




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

CREATE TABLE IF NOT EXISTS "Narocila"
(
  "ID_narocila" AUTOINC DESCRIPTION 'ID naroèila',
  "ID_narocnika" INTEGER DESCRIPTION 'ID naroènika' NOT NULL,
  "ID_tip_meritve" INTEGER DESCRIPTION 'ID tipa meritev' NOT NULL,
  "ID_panoge" INTEGER DESCRIPTION 'ID panoge' NOT NULL,
  "ID_placnika" INTEGER DESCRIPTION 'ID plaènika' NOT NULL,
  "ID_vodje_meritev" INTEGER DESCRIPTION 'ID vodje meritev' NOT NULL,
  "Ime_merjencev" VARCHAR(30) DESCRIPTION 'Oznaka merjencev' NOT NULL,
  "Kraj_meritve" VARCHAR(20) DESCRIPTION 'Kraj meritve' NOT NULL DEFAULT
'Ljubljana',
  "Datum_od" DATE DESCRIPTION 'Zaèetek meritev' NOT NULL DEFAULT CURRENT_DATE,
  "Datum_do" DATE DESCRIPTION 'Konec meritev' NOT NULL DEFAULT CURRENT_DATE,
  "Predracun_stevilka" AUTOINC DESCRIPTION '©tevilka predraèuna',
  "Predracun_poslan_dne" DATE DESCRIPTION 'Predraèun poslan dne' NOT NULL
DEFAULT CURRENT_DATE,
  "Predracun_poslan" CHAR(1) DESCRIPTION 'Predraèun poslan?' NOT NULL DEFAULT
'N',
  "Racun_stevilka" AUTOINC DESCRIPTION '©tevilka raèuna',
  "Racun_poslan_dne" DATE DESCRIPTION 'Raèun poslan dne',
  "Racun_poslan" CHAR(1) DESCRIPTION 'Raèun poslan?' NOT NULL DEFAULT 'N',
  "Valuta" DATE DESCRIPTION 'Valuta plaèila' NOT NULL,
  "Placano" CHAR(1) DESCRIPTION 'Plaèano?' NOT NULL DEFAULT 'N',
  "Placano_dne" DATE DESCRIPTION 'Plaèano dne',
  "Vse_meritve_skupaj" MONEY DESCRIPTION 'Se¹tevek vseh meritev skupaj' NOT
NULL DEFAULT 0,
  "Porocilo_dne" DATE DESCRIPTION 'Poroèilo oddano dne',
  "Porocilo_oddano" CHAR(1) DESCRIPTION 'Poroèilo oddano?' NOT NULL DEFAULT
'N',
  "Narocilnica_stevilka" VARCHAR(10) DESCRIPTION '©tevilka naroèilnice',
  "Storno" CHAR(1) DESCRIPTION 'Storno' NOT NULL DEFAULT 'N',
  "Uvozeno_iz_racunovodstva" CHAR(1) DESCRIPTION 'Uvo¾eno iz raèunovodstva' NOT
NULL DEFAULT 'N',
  "Izvozeno_v_racunovodstvo" CHAR(1) DESCRIPTION 'Izvo¾eno v raèunovodstvo' NOT
NULL DEFAULT 'N',
  "Opomba" VARCHAR(200) DESCRIPTION 'Opomba',
  "Leto" CHAR(4) DESCRIPTION 'Leto' NOT NULL,
  "Rezija" INTEGER DESCRIPTION 'Re¾ija',
  "Ustvarjeno" TIMESTAMP DESCRIPTION 'Datum nastanka' NOT NULL DEFAULT
CURRENT_TIMESTAMP,
  "Zadnja_sprememba" TIMESTAMP DESCRIPTION 'Datum zadnje spremembe' NOT NULL
DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ("ID_narocila") COMPRESS NONE
DESCRIPTION 'baza naroèil meritev'
LOCALE CODE 1060
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "ID_narocnika" ON "Narocila" ("ID_narocnika");
CREATE INDEX IF NOT EXISTS "ID_tip_meritve" ON "Narocila" ("ID_tip_meritve");
CREATE INDEX IF NOT EXISTS "ID_panoge" ON "Narocila" ("ID_panoge");
CREATE INDEX IF NOT EXISTS "ID_placnika" ON "Narocila" ("ID_placnika");
CREATE INDEX IF NOT EXISTS "ID_vodje_meritev" ON "Narocila"
("ID_vodje_meritev");
CREATE INDEX IF NOT EXISTS "Ime_merjencev" ON "Narocila" ("Ime_merjencev")
COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "Datum_od" ON "Narocila" ("Datum_od");
CREATE INDEX IF NOT EXISTS "Placano" ON "Narocila" ("Placano");
CREATE INDEX IF NOT EXISTS "Porocilo_oddano" ON "Narocila" ("Porocilo_oddano");
CREATE INDEX IF NOT EXISTS "Storno" ON "Narocila" ("Storno");
CREATE INDEX IF NOT EXISTS "Datum_do" ON "Narocila" ("Datum_do");

Joze,

<< I still have my old table on the laptop with only partially optimized
filter. Verify, repair and optimize table doesn't help. >>

Are you sure that the data is exactly the same as with the other table ?  If
the data is the same, and you've used OptimizeTable to ensure that all
deleted space is gone, then the query plans should be exactly the same as
with the other machine.

--
Tim Young
Elevate Software
www.elevatesoft.com

>Are you sure that the data is exactly the same as with the other table ?  If
>the data is the same, and you've used OptimizeTable to ensure that all
>deleted space is gone, then the query plans should be exactly the same as
>with the other machine.

No, data are not the same for sure...

Regards, Joze