Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 18 total |
Query optomisation help |
Mon, Mar 13 2006 1:02 AM | Permanent Link |
"Clive" | Hi,
I am updating a memory table and the update is taking over 90 seconds on a 2.4ghz 1GB windows XP machine V4.22 B4 All columns are indexed and I have tried the NOJOINOPTIMIZE command and theres not much difference between using that and not. The memory table has 12672 rows and the disk table has 23328 rows. Would 90 seconds to run this seem usual?. or is there something I can do to speed this up Cheers Clive. ================================================================================ SQL statement ================================================================================ update "\memory\exp1" set value = 1 from "\memory\exp1" inner join scenario_expense_detail sed on sed.expenses_code = exp1.expenses_code and sed.scenario_code = exp1.scenario_code and sed.project_code = exp1.project_code and sed.period = exp1.period Tables Involved --------------- exp1 (exp1) table opened shared, has 12672 rows scenario_expense_detail (sed) table opened shared, has 23328 rows Join Ordering ------------- The driver table is the exp1 table (exp1) The exp1 table (exp1) is joined to the scenario_expense_detail table (sed) with the INNER JOIN expression: exp1.expenses_code = sed.expenses_code and exp1.scenario_code = sed.scenario_code and exp1.project_code = sed.project_code and exp1.period = sed.period Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further The expression: exp1.expenses_code = sed.expenses_code and exp1.scenario_code = sed.scenario_code and exp1.project_code = sed.project_code and exp1.period = sed.period has been rewritten and is OPTIMIZED ================================================================================ |
Mon, Mar 13 2006 2:29 AM | Permanent Link |
Abdulaziz Jasser | Do you have indexes for "expenses_code", "scenario_code", and "project_code" in both memory table and the physical table? If not, create ones.
It should speed up the query. |
Mon, Mar 13 2006 4:38 AM | Permanent Link |
"Clive" | Yep and yep.
The posted query plan indicates that all is optimised. "Abdulaziz Jasser" <jasser@cyberia.net.sa> wrote in message news:BDD3C954-8F2F-4CB2-B306-DCE92755B4F0@news.elevatesoft.com... > Do you have indexes for "expenses_code", "scenario_code", and > "project_code" in both memory table and the physical table? If not, > create ones. > It should speed up the query. > |
Mon, Mar 13 2006 12:09 PM | Permanent Link |
Abdulaziz Jasser | Hummm, I don't think I can help you more on this due to the fact I am still using 3.30, but let me tell you
something from what I have learned over the years, db transaction really depends on how fast are BOTH your CPU+HRAD DRIVE (Not to forget memory). The other thing, are you testing this query over a LAN or a single PC? |
Mon, Mar 13 2006 12:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Clive,
<< Would 90 seconds to run this seem usual?. or is there something I can do to speed this up >> If the join condition is a 1-to-1 link, then it could take a while since DBISAM has to filter the joined disk table 12672 times. Also, what is the record size of the in-memory table ? If it is fairly large, then you might want to specify a COMMIT INTERVAL around 1000 or so in the SQL statement to allow for more rows to be updated in each internal transaction. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 13 2006 2:49 PM | Permanent Link |
"Clive" | Its all local.
"Abdulaziz Jasser" <jasser@cyberia.net.sa> wrote in message news:3CBF7CD8-4FA6-4E1F-AD22-A35557827387@news.elevatesoft.com... > Hummm, I don't think I can help you more on this due to the fact I am > still using 3.30, but let me tell you > something from what I have learned over the years, db transaction really > depends on how fast are BOTH your > CPU+HRAD DRIVE (Not to forget memory). The other thing, are you testing > this query over a LAN or a single > PC? > |
Mon, Mar 13 2006 3:00 PM | Permanent Link |
"Clive" | The structure of the memory table is
Integer, Integer, Integer, Float, Date, String 255 Float I tried the commit interval 1000, but timings remained about the same. Guess thats as fast as this query can go. Cheers Clive. "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:8CB24898-72EF-48F8-9A3B-0B03C9CF973B@news.elevatesoft.com... > Clive, > > << Would 90 seconds to run this seem usual?. or is there something I can > do to speed this up >> > > If the join condition is a 1-to-1 link, then it could take a while since > DBISAM has to filter the joined disk table 12672 times. Also, what is the > record size of the in-memory table ? If it is fairly large, then you > might want to specify a COMMIT INTERVAL around 1000 or so in the SQL > statement to allow for more rows to be updated in each internal > transaction. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > > |
Mon, Mar 13 2006 5:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Clive,
<< Guess thats as fast as this query can go. >> Is the join a 1-to-1 relationship ? -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 13 2006 5:55 PM | Permanent Link |
"Clive" | Yes.
1 Record in the Memory Table references 1 record in the disk table, .. "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:D0476368-BD6D-4BD0-BD2E-707D1F24C6E8@news.elevatesoft.com... > Clive, > > << Guess thats as fast as this query can go. >> > > Is the join a 1-to-1 relationship ? > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Tue, Mar 14 2006 5:11 AM | Permanent Link |
Chris Erdal | Just an idea out of nowhere, but perhaps things'll go faster one at a
time: (assuming value is 0 everywhere before you start) update "\memory\exp1" set value = 1000 from "\memory\exp1" inner join scenario_expense_detail sed on sed.expenses_code = exp1.expenses_code; update "\memory\exp1" set value = value / 10 from "\memory\exp1" inner join scenario_expense_detail sed on sed.scenario_code = exp1.scenario_code; update "\memory\exp1" set value = value / 10 from "\memory\exp1" inner join scenario_expense_detail sed on sed.project_code = exp1.project_code; update "\memory\exp1" set value = value / 10 from "\memory\exp1" inner join scenario_expense_detail sed on sed.period = exp1.period; (and lastly, if necessary) update "\memory\exp1" set value = 0 WHERE value > 1; -- Chris |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |