Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 19 total |
filtering tables takes |
Tue, May 13 2008 12:04 PM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
=?iso-8859-1?Q?Santy_Concepci=F3n?= | Hi, Eduardo...
Using 'Between' doesn't seem to improve the speed "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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 06:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |