Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 19 total
Thread filtering tables takes
Tue, May 13 2008 12:04 PMPermanent Link

=?iso-8859-1?Q?Santy_Concepci=F3n?=
Hi!

I have been trying to improove the speed of my app on a LAN (not C/S), using
latest DBISAM 4 release and Delphi7 Pro.
I'm playing with table filters to see which conditions order is the best.

I have noticed that changing the condition values of a DATE filter condition
takes more or less time to execute depending on the number of records to
show, BUT only takes more time when there are no matching records! Is it
correct?

For example...

   upper(MOV) = upper('BUY') and DATE >= '2007-04-01' and DATE <=
'2008-05-01'

....takes 0,3 seconds to show the results (about 60 records)

However...

   upper(MOV = upper('BUY') and DATE >= '2008-01-01' and DATE <=
'2008-02-01'

....takes 16,0 seconds to show the results (0 RECORDS!!)

Why the second filter, which has no matching records, takes more time to
show?
Is it possible to optimize it? (Table contains indexes for MOV and DATE
fields)

Thanks!

Tue, May 13 2008 12:08 PMPermanent Link

"Jose Eduardo Helminsky"
Santy

> Why the second filter, which has no matching records, takes more time to
> show?
> Is it possible to optimize it? (Table contains indexes for MOV and DATE
> fields)

I don´t know how to answer your question but you can improve the speed using
between when comparing interval of dates.

upper(MOV) = upper('BUY') and DATE BETWEEN '2007-04-01' and '2008-05-01'

Eduardo

Tue, May 13 2008 12:40 PMPermanent Link

=?iso-8859-1?Q?Santy_Concepci=F3n?=
Hi, Eduardo...

Using 'Between' doesn't seem to improve the speed Frown

"Jose Eduardo Helminsky" <contato@hpro.com.br> escribió en el mensaje de
noticias:3E1C7C7A-D763-46C3-9437-F92C293E547C@news.elevatesoft.com...
> Santy
>
>> Why the second filter, which has no matching records, takes more time to
>> show?
>> Is it possible to optimize it? (Table contains indexes for MOV and DATE
>> fields)
>
> I don´t know how to answer your question but you can improve the speed
> using between when comparing interval of dates.
>
> upper(MOV) = upper('BUY') and DATE BETWEEN '2007-04-01' and '2008-05-01'
>
> Eduardo
>
>
Tue, May 13 2008 1:10 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com


<< Why the second filter, which has no matching records, takes more time to
show? >>

What is the setting that you're using for the
TDBISAMEngine.TableFilterIndexThreshhold property ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, May 13 2008 3:22 PMPermanent Link

=?iso-8859-1?Q?Luis_Concepci=F3n?=
Hi, Tim...

I'm using the default setting for that property. Which should I use?
What about if I change that setting?

BTW, what if I don't use a TDBISAMEngine component, but only the
TDBISAMDatabase one? Is it recommended to use it always?

Thanks!


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje de
noticias:BEAE6942-A3C1-4B38-A960-F60AA11394B7@news.elevatesoft.com...
>
> << Why the second filter, which has no matching records, takes more time
> to show? >>
>
> What is the setting that you're using for the
> TDBISAMEngine.TableFilterIndexThreshhold property ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
Wed, May 14 2008 5:22 AMPermanent Link

=?iso-8859-1?Q?Santy_Concepci=F3n?=
Ok, I have added a DBISAMEngine component and have tried with different
TableFilterIndexThreshhold values, with no improvement at all:

TableFilterIndexThreshhold = 0  ---> Speed = 14.135
TableFilterIndexThreshhold = 1  ---> Speed = 15.022
TableFilterIndexThreshhold = 2  ---> Speed = 15.499
TableFilterIndexThreshhold = 6  ---> Speed = 15.220

BTW, I'm using DBISAM in file share mode (not C/S)

Thanks!

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje de
noticias:BEAE6942-A3C1-4B38-A960-F60AA11394B7@news.elevatesoft.com...
>
> << Why the second filter, which has no matching records, takes more time
> to show? >>
>
> What is the setting that you're using for the
> TDBISAMEngine.TableFilterIndexThreshhold property ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
Wed, May 14 2008 8:52 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com


<< Ok, I have added a DBISAMEngine component and have tried with different
TableFilterIndexThreshhold values, with no improvement at all:

TableFilterIndexThreshhold = 0  ---> Speed = 14.135
TableFilterIndexThreshhold = 1  ---> Speed = 15.022
TableFilterIndexThreshhold = 2  ---> Speed = 15.499
TableFilterIndexThreshhold = 6  ---> Speed = 15.220

BTW, I'm using DBISAM in file share mode (not C/S) >>

If that doesn't improve the performance, than you may be hitting an external
performance barrier due to the network hardware speed.  How fast is the LAN
hardware that is being used ?

Also, could you post the actual table structure, including the indexes ?  I
want to double-check and make sure that the filter is actually optimized.
Your timings indicate that this may not be the case.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, May 14 2008 8:53 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Luis,

<< I'm using the default setting for that property. Which should I use? What
about if I change that setting? >>

The default setting should be fine.

<< BTW, what if I don't use a TDBISAMEngine component, but only the
TDBISAMDatabase one? Is it recommended to use it always? >>

You can also access the global TDBISAMEngine component at runtime via the
dbisamtb.Engine function.  So no, it is not necessary to use a TDBISAMEngine
component explicitly unless you want to.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, May 14 2008 9:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Santy

Better still grab the filter, paste it as the WHERE clause for a query and post the query plan.

Roy Lambert
Wed, May 14 2008 11:39 AMPermanent Link

=?iso-8859-1?Q?Santy_Concepci=F3n?=
Hi Tim and Roy.

LAN speed is 54Mbps (wireless).
Using SQL Query it is very very fast (less than a second to show results),
but using table filter it takes about 15 seconds to filter the table.

The problem is that, using the "Set an Expression Filter" of DBISAm System
Utility, it only takes ONE second to show the records!!!
Why my application takes so long if I'm only doing the same?
When using that option, I can see the available fields, but MOV field
appears as not optimized. Why if it is indexed?
Why using DBISAM System Utility, even with MOV not optimized, it takes less
than a second?

This is the Query Plan and table structure:

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

select * from movim
where upper(MOV) = upper('COMPRA') and FECHA BETWEEN '2008-01-01' and
'2008-02-01'

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

movim (movim) table opened shared, has 9265 rows

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

Result set will be canned

Result set will consist of one or more rows

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

The expression:

upper(MOV) = upper('COMPRA') and FECHA BETWEEN '2008-01-01' AND '2008-02-01'

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

================================================================================
>>>>> 0 rows affected in 0,031 seconds
================================================================================


And this is the Table Structure:

CREATE TABLE IF NOT EXISTS "movim"
(
  "AUTO" AUTOINC,
  "FECHA" DATE,
  "FACTURA" INTEGER,
  "MOV" VARCHAR(10),
  "IMPORTE" FLOAT,
  "CANTIDAD" FLOAT,
  "CLIENTE" VARCHAR(25),
  "NOM_C" VARCHAR(100),
  "IMPORTE_E" FLOAT,
  "FECHA_VENC" DATE,
  "TOTAL" FLOAT,
  "TOTAL_E" FLOAT,
  "EXPORTADO" SMALLINT,
  "PAGADO" SMALLINT,
  "AGENTE" INTEGER,
  "TOTALAGE" FLOAT,
  "FPAGO" INTEGER,
  "COLOR" VARCHAR(10),
  "HORA" TIME,
  "BLOQUEADO" BOOLEAN,
  "SERIE" VARCHAR(1),
  "IVA_RE" VARCHAR(2),
  "ESTADO" MEMO,
  "COBRADO" FLOAT,
  "PENDIENTE" FLOAT,
  "CAMPO" VARCHAR(50),
  "DTO" FLOAT,
PRIMARY KEY ("AUTO") COMPRESS FULL
LOCALE CODE 1034
);

CREATE NOCASE INDEX IF NOT EXISTS "movname" ON "movim" ("NOM_C") COMPRESS
FULL;
CREATE NOCASE INDEX IF NOT EXISTS "movtip" ON "movim" ("MOV") COMPRESS FULL;
CREATE NOCASE INDEX IF NOT EXISTS "movfec" ON "movim" ("FECHA") COMPRESS
FULL;
CREATE NOCASE INDEX IF NOT EXISTS "movfac" ON "movim" ("FACTURA") COMPRESS
FULL;
CREATE NOCASE INDEX IF NOT EXISTS "movven" ON "movim" ("FECHA_VENC")
COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "FPAGO" ON "movim" ("FPAGO") COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "TOTAL_E" ON "movim" ("TOTAL_E") COMPRESS FULL;

I can send you my test app and test table if you need it.

Thanks!!



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje de
noticias:13B8F869-D174-48EB-8F1A-7757BB43F756@news.elevatesoft.com...
>
> << Ok, I have added a DBISAMEngine component and have tried with different
> TableFilterIndexThreshhold values, with no improvement at all:
>
> TableFilterIndexThreshhold = 0  ---> Speed = 14.135
> TableFilterIndexThreshhold = 1  ---> Speed = 15.022
> TableFilterIndexThreshhold = 2  ---> Speed = 15.499
> TableFilterIndexThreshhold = 6  ---> Speed = 15.220
>
> BTW, I'm using DBISAM in file share mode (not C/S) >>
>
> If that doesn't improve the performance, than you may be hitting an
> external performance barrier due to the network hardware speed.  How fast
> is the LAN hardware that is being used ?
>
> Also, could you post the actual table structure, including the indexes ?
> I want to double-check and make sure that the filter is actually
> optimized. Your timings indicate that this may not be the case.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
Page 1 of 2Next Page »
Jump to Page:  1 2
Image