Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Are filters using the index? |
Mon, Sep 17 2007 6:21 PM | Permanent Link |
Dave Harrison | How do I make table filters run faster? In a 15M row table it takes 75
to 90 seconds to pull up 5k rows based on an index. The key is case-sensitive. It doens't matter what the index is set to, it's always slow. With DBISAM it takes about 1 second or less. filter: ProductId='ABCD' It is a compound index but that shouldn't matter since ProductId is the first field of the index. TIA Dave |
Tue, Sep 18 2007 1:45 AM | Permanent Link |
Dave Harrison | Dave Harrison wrote:
> How do I make table filters run faster? In a 15M row table it takes 75 > to 90 seconds to pull up 5k rows based on an index. The key is > case-sensitive. It doens't matter what the index is set to, it's always > slow. With DBISAM it takes about 1 second or less. > > filter: ProductId='ABCD' > > It is a compound index but that shouldn't matter since ProductId is the > first field of the index. TIA > > Dave I discovered the SQL queries are also slow so I'm repairing the table. It will have to run overnight and I'll check it in the morning. Dave |
Tue, Sep 18 2007 10:58 AM | Permanent Link |
Dave Harrison | Dave Harrison wrote:
> Dave Harrison wrote: > >> How do I make table filters run faster? In a 15M row table it takes 75 >> to 90 seconds to pull up 5k rows based on an index. The key is >> case-sensitive. It doens't matter what the index is set to, it's >> always slow. With DBISAM it takes about 1 second or less. >> >> filter: ProductId='ABCD' >> >> It is a compound index but that shouldn't matter since ProductId is >> the first field of the index. TIA >> >> Dave > > > I discovered the SQL queries are also slow so I'm repairing the table. > It will have to run overnight and I'll check it in the morning. > > Dave After the repair, the filter and queries are still slow. Simple queries to another large table is also slow (over 1 minute for index search). I'm sure all this worked pretty fast a couple of months ago with v1.04. The same query in DBISAM 4 takes 1.4 seconds compared to 90 seconds for ElevateDb. Of course executing the EDB query a second time will take a modest 4.5 seconds because it is still in the cache. Disconnecting the session and repeating the query takes 90 seconds again. Am I missing something obvious here? Here is the query plan: ================================================================================ SQL Query (Executed by ElevateDB 1.05 Build 2) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "ProductHistory"."RecordID" AS "RecordID", "ProductHistory"."Rcd_Id" AS "Rcd_Id", "ProductHistory"."Product_Symbol" AS "Product_Symbol", "ProductHistory"."Product_Date" AS "Product_Date", "ProductHistory"."Period" AS "Period", "ProductHistory"."Category" AS "Category", "ProductHistory"."Quantity" AS "Quantity", "ProductHistory"."Num1" AS "Num1", "ProductHistory"."Num3" AS "Num3", "ProductHistory"."Num4" AS "Num4", "ProductHistory"."Num2" AS "Num2", "ProductHistory"."Log_Date" AS "Log_Date" FROM "ProductHistory" WHERE "Product_symbol" = 'AVD-301' Source Tables ------------- ProductHistory: 16389154 rows Result Set ---------- The result set was sensitive The result set consisted of zero or more rows The result set was ordered using the index PrimaryKey Filtering --------- The following filter condition was applied to the Query table: "Product_symbol" = 'AVD-301' [Index scan: 11355 keys, 802816 bytes estimated cost] ================================================================================ 20726 row(s) returned in 98.516 secs ================================================================================ Dave |
Tue, Sep 18 2007 3:14 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< After the repair, the filter and queries are still slow. Simple queries to another large table is also slow (over 1 minute for index search). I'm sure all this worked pretty fast a couple of months ago with v1.04. >> I doubt it, the issue is the reposition of the row pointer to the first matching row based upon the ordering index. It may take some time if you have a lot of rows in the table, the number of rows selected is fairly small in relation to the size of the table, and the first matching row is towards the end of the table according to the ordering index. The workaround for this is to simply set RequestSensitive to False. That will generate the rows fairly quickly. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 18 2007 5:23 PM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << After the repair, the filter and queries are still slow. Simple queries > to another large table is also slow (over 1 minute for index search). I'm > sure all this worked pretty fast a couple of months ago with v1.04. >> > > I doubt it, the issue is the reposition of the row pointer to the first > matching row based upon the ordering index. It may take some time if you > have a lot of rows in the table, the number of rows selected is fairly small > in relation to the size of the table, and the first matching row is towards > the end of the table according to the ordering index. The workaround for > this is to simply set RequestSensitive to False. That will generate the > rows fairly quickly. > Ok, setting RequestSensitive to False has speeded up the queries because they are now static. Can I assume that if the table is encrypted, then the temporary files created by the static queries also remain encrypted? But setting FilterOptions.foCaseInsensitive = True has no effect on table filters and they still take over 90 seconds to execute. Don't table filters make use of indexes when they're available? If not, then it looks like I'll have to change from using tables to queries because waiting 90 seconds for a filter to complete (compared to 1 second) isn't going to sit well with users. Dave |
Wed, Sep 19 2007 1:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Ok, setting RequestSensitive to False has speeded up the queries because they are now static. Can I assume that if the table is encrypted, then the temporary files created by the static queries also remain encrypted? >> Yes. << But setting FilterOptions.foCaseInsensitive = True has no effect on table filters and they still take over 90 seconds to execute. Don't table filters make use of indexes when they're available? >> It's not an issue of the filter using the index. The actual filtering process probably only takes a few milliseconds. The issue is re-positioning the current row pointer on the first row that matches the filter condition. Filters maintain their row numbers in a bitmap that corresponds to the physical position of the row in the table, and this does not correspond to the active index order. Subsequently, after executing the filter, EDB has to position itself on the first matching row in the active index order by literally navigating from the first row until it finds a row that is part of the filtered set. This can take a long time if the first matching row is the 1 millionth out of 1.6 million rows. DBISAM got around this with the TDBISAMEngine.FilterIndexThreshhold property, which used an alternative method when the number of rows satisfying the filter was low compared to the total row count, but it was problematic, had quite a few bugs initially, and doesn't fit well with EDB. I have to come up with a better alternative for EDB. << If not, then it looks like I'll have to change from using tables to queries because waiting 90 seconds for a filter to complete (compared to 1 second) isn't going to sit well with users. >> I'll see what I can do about fixing this, but for now filters are equivalent to a query that generates a sensitive result set, so you'll always see the issue with a filter. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Sep 19 2007 4:14 PM | Permanent Link |
Dave Harrison | Tim,
Ok, thanks for the explanation. It looks like I'll be switching to static queries for my large EDB tables later this week. Filters can be 60x-100x slower than queries (for the reasons you mentioned) and unless I use ranges with the filters, I don't think using TEDBTables are going to be a viable option for filtering large tables. Oh well, it's back to SQL for me. Dave |
Thu, Sep 20 2007 2:08 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Ok, thanks for the explanation. It looks like I'll be switching to static queries for my large EDB tables later this week. Filters can be 60x-100x slower than queries (for the reasons you mentioned) and unless I use ranges with the filters, I don't think using TEDBTables are going to be a viable option for filtering large tables. Oh well, it's back to SQL for me. >> As I said, I'll be looking into a solution for this after 1.06 is out, so there may be a solution by the end of the year. I would have ported the DBISAM functionality to EDB, but it really was problematic and not an ideal solution. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |