Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 12 total |
VERY slow query/filter execution ! |
Sun, Dec 10 2006 2:30 PM | Permanent Link |
Max Terentiev | Hello,
I have table with about 5 000 000 of records. Simple queries like SELECT * FROM "Cards" WHERE PoductID=25 executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM). ProductID field is indexed of cource. RequestLive is true Session is Local. Filter speed with TDBIsamTable is very slow to. Simple expression like ProductID=25 is slow as query (40+ seconds). It's possible to speed-up query execution of large tables ? Or DBISam is not for large tables ? Thanx. |
Sun, Dec 10 2006 5:47 PM | Permanent Link |
"Adam H." | Hi Max,
How large is the result set? Are you doing this query from within DBSYS, or your own application? (If your own application, can you try it in DBSys, just to make sure you're haivng the same results)? What antivirus software are you running? Does it make a difference if you disable it? Cheers Adam. "Max Terentiev" <maxterentiev@mail.ru> wrote in message news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com... > Hello, > > I have table with about 5 000 000 of records. > > Simple queries like SELECT * FROM "Cards" WHERE PoductID=25 > executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM). > > ProductID field is indexed of cource. > RequestLive is true > Session is Local. > > Filter speed with TDBIsamTable is very slow to. Simple expression > like ProductID=25 is slow as query (40+ seconds). > > It's possible to speed-up query execution of large tables ? > Or DBISam is not for large tables ? > > Thanx. > |
Sun, Dec 10 2006 6:08 PM | Permanent Link |
Max Terentiev | "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote:
>Hi Max, >How large is the result set? Result set is very large to (at least 500 000 records). >Are you doing this query from within DBSYS, or your own application? In my app. >(If your own application, can you try it in DBSys, just to make sure you're >haivng the same results)? Same results >What antivirus software are you running? Does it make a difference if you >disable it? All antiviruses disabled. My application is only running program. Cheers Adam. "Max Terentiev" <maxterentiev@mail.ru> wrote in message news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com... > Hello, > > I have table with about 5 000 000 of records. > > Simple queries like SELECT * FROM "Cards" WHERE PoductID=25 > executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM). > > ProductID field is indexed of cource. > RequestLive is true > Session is Local. > > Filter speed with TDBIsamTable is very slow to. Simple expression > like ProductID=25 is slow as query (40+ seconds). > > It's possible to speed-up query execution of large tables ? > Or DBISam is not for large tables ? > > Thanx. > |
Sun, Dec 10 2006 6:35 PM | Permanent Link |
Max Terentiev | I try to set GeneratePlan option and see strange result
in Plan log after executing query: ------------------------------------------------------- SELECT * FROM Probes1 WHERE DirectID<5000 Tables Involved --------------- Probes1 (Probes1) table opened shared, has 923749 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 Probes1 WHERE Clause Execution ---------------------- The expression: DirectID < 5000 is UN-OPTIMIZED, covers 923749 rows or index keys, costs 1041988872 bytes, and will be applied to the Probes1 table (Probes1) before any joins ================================================================================ >>>>> 11550 rows affected in 28,25 seconds ================================================================================ Why it's UN-OPTIMIZED ???? DirectID is indexed non-unique field. What must i do to make this query OPTIMIZED ??? |
Sun, Dec 10 2006 6:35 PM | Permanent Link |
"Adam H." | Hi Max,
Sorry - I should have asked. What version of DBISam are you running? Best Regards Adam. "Max Terentiev" <maxterentiev@mail.ru> wrote in message news:AE9ABD4E-2870-4CDB-8383-BA13C0A2CC2C@news.elevatesoft.com... > "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote: > >>Hi Max, > >>How large is the result set? > > Result set is very large to (at least 500 000 records). > >>Are you doing this query from within DBSYS, or your own application? > > In my app. > >>(If your own application, can you try it in DBSys, just to make sure >>you're >>haivng the same results)? > > Same results > >>What antivirus software are you running? Does it make a difference if you >>disable it? > > All antiviruses disabled. My application is only running program. > > Cheers > > Adam. > > "Max Terentiev" <maxterentiev@mail.ru> wrote in message > news:E40564DA-93DE-4232-AAAC-CA334059922C@news.elevatesoft.com... >> Hello, >> >> I have table with about 5 000 000 of records. >> >> Simple queries like SELECT * FROM "Cards" WHERE PoductID=25 >> executes very slow (about 40+ seconds on Athlon 64 3500, 2 gig RAM). >> >> ProductID field is indexed of cource. >> RequestLive is true >> Session is Local. >> >> Filter speed with TDBIsamTable is very slow to. Simple expression >> like ProductID=25 is slow as query (40+ seconds). >> >> It's possible to speed-up query execution of large tables ? >> Or DBISam is not for large tables ? >> >> Thanx. >> > > |
Sun, Dec 10 2006 6:37 PM | Permanent Link |
Max Terentiev | Hi Max,
Sorry - I should have asked. What version of DBISam are you running? Plan log says 4.24 build 1: ================================================================================ SQL statement (Executed with 4.24 Build 1) ================================================================================ SELECT * FROM Probes1 WHERE DirectID<5000 Tables Involved --------------- Probes1 (Probes1) table opened shared, has 923749 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 Probes1 WHERE Clause Execution ---------------------- The expression: DirectID < 5000 is UN-OPTIMIZED, covers 923749 rows or index keys, costs 1041988872 bytes, and will be applied to the Probes1 table (Probes1) before any joins ================================================================================ >>>>> 11550 rows affected in 28,25 seconds ================================================================================ Why it's un-optimized ? DirectID is non-unique indexed field ! |
Sun, Dec 10 2006 6:42 PM | Permanent Link |
"Adam H." | Hi Max,
> Why it's un-optimized ? DirectID is non-unique indexed field ! Do you have an index on DirectID where directID is the only field in the index? If not, can you please create an index with DirectID being the only field in the index, and see if this optimizes the query? Thanks & Regards Adam. |
Mon, Dec 11 2006 2:24 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Max,
<< Why it's UN-OPTIMIZED ???? DirectID is indexed non-unique field. What must i do to make this query OPTIMIZED ??? >> Could you send me an empty table structure for the table ? My initial guess is that DirectID is not the first field in any of the indexes, which is a requirement in DBISAM for optimization: http://www.elevatesoft.com/dbisam4d5_optimizations.htm -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Dec 11 2006 3:42 PM | Permanent Link |
Max Terentiev | Hi Tim,
>Could you send me an empty table structure for the table ? My initial guess >is that DirectID is not the first field in any of the indexes, which is a >requirement in DBISAM for optimization: I was change table structure and create individual Index for DirectID field (before i have one index for several fields include DirectID). Now query is optimized (in Plan log) and execute fasten - about 17-20 seconds. But it's still to slow for me ! My table contain about 5 000 000 of records. Result set for SELECT * FROM Probes WHERE DirectID=X may be very large (from 50 000 to 500 000 records). I use DirectID field for Master-Detail link. So, navigation in Master table is not possible (user must wait up to 20 seconds before active record in Master table changed). |
Mon, Dec 11 2006 3:59 PM | Permanent Link |
"Robert" | "Max Terentiev" <maxterentiev@mail.ru> wrote in message news:733FD2AE-FB8E-4F72-AE66-7930081AFB25@news.elevatesoft.com... > > My table contain about 5 000 000 of records. > Result set for SELECT * FROM Probes WHERE DirectID=X may be very large > (from 50 000 to 500 000 records). > Why in the world would you want to scroll thru a master table that displays 50K details? Seems some redesign might be in order. But if you MUST, try using tTables, in my experience they are much faster for master/detail, Robert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |