Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 27 total |
Speed accessing data |
Fri, Oct 3 2008 9:17 AM | Permanent Link |
Malcolm | Hi, After a few months I a back testing an upgrade to one of our systems which migrates its database from paradox to dbisam. I am finding that
the reports I have written over the DBISAM tables are somewhat slower than those running over the Paradox ones. I am using VB6 with ODBC in both cases. Even the programs within the system are slower and I believe these are writtem using Delphi. One report takes less than a minutes when run over the Paradox Tables but in excess of 7 minutes over DBISAM Is this something you would expect? Is there a "Best" programming language to use with DBISAM Using Joins in SQL appears to be even slower than using "WHERE field1 = field2", is this as you would expect, would I be better of reading from table one and passing parameters to a second sql to run over table 2. I have developed a number of report programs to run over the paradox system which management have become reliant on and need to be able to produce these reports within similar timescales in order to move to your more robust database. Any help or advice will be much appreciated. Regards Malcolm |
Fri, Oct 3 2008 11:13 AM | Permanent Link |
Fernando Dias Team Elevate | Malcolm,
I suspect that after a few optimization steps you can obtain completely different result. Can you post the query (or queries) used in that report taking 7 minutes and also the types of the columns in that query? A query execution plan would help too. > Is there a "Best" programming language to use with DBISAM Delphi is *the best*, period -- Fernando Dias [Team Elevate] |
Fri, Oct 3 2008 11:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
You need to discover the execution plan - its your friend in these circumstances. DBISAM needs the right indices in place to run well and the execution plan will tell you which are missing. Look in DBSys - there's a checkbox for generate plan - tick it and examine the plan after running your query. If you can't figure it out post it here and we'll try and help. Roy Lambert [Team Elevate] |
Sun, Oct 5 2008 5:34 PM | Permanent Link |
"Adam H." | Good Morning Mal,
As the other's have suggested, you'll need to look at optimizing your query. I had the same problems when I first started working with DBISam, until I found that you need to have the right indexes on the table. For best performance, I've found one should have a single index for every field used in either a join, or a where statement (on all tables). As the others also suggested, check the execution plan and look for any statements that say NOT OPTIMIZED - these are the ones that you'll need to create the correct indexes for, and once done - I suspect you'll see a huge improvement to your query. Cheers Adam. |
Mon, Oct 6 2008 7:36 AM | Permanent Link |
Malcolm | "Adam H." <ahairsub5@jvxp_removeme.com> wrote:
Good Morning Mal, As the other's have suggested, you'll need to look at optimizing your query. I had the same problems when I first started working with DBISam, until I found that you need to have the right indexes on the table. For best performance, I've found one should have a single index for every field used in either a join, or a where statement (on all tables). As the others also suggested, check the execution plan and look for any statements that say NOT OPTIMIZED - these are the ones that you'll need to create the correct indexes for, and once done - I suspect you'll see a huge improvement to your query. Hi All, And many thanks for you replies. Sorry for being very green but the execution plan means nothing to me so can someone please explain how and where. The situation I am in is that we have some software which feeds and collects data from our tills. Version 3 of this software uses a Paradox database, when I run a particular report in this version it takes around 1minute. The upgrade for the system uses DBISAM and the same report takes over 7 minutes (this has been reduced from around 1hour by me complaining). These reports are part of the system and as such I do not have access to the code. My programs are written in VB6(recently self taught) and again One report in Version 3 takes 12minutes in Version4 it takes 30. The sql I am using is sqlProdDay = "SELECT ProductDay.SalesDate, ProductDay.LocationNo, ProductDay.ProductCode, ProductDay.SalesQty, ProductDay.SalesValue, ProductDetail.ECRDept From ProductDetail, ProductDay Where (ProductDetail.ProductCode = ProductDay.ProductCode) and (ProductDetail.LocationNo = ProductDay.LocationNo) and (ProductDay.SalesDate >= " & "'" & txtFrmDate & "'" & " AND ProductDay.SalesDate <= " & "'" & txtToDate & "'" & ") And ProductDay.LocationNo = " & txtSavedLoc & "" ProductDay has around 3.7million records while ProductDetail has around 750 thousand. I am reluctant to mess around with indexes as I do not want to cause any problems with the system. I copy the complete database to my laptop to run my reports. Any help and advice is very much appreciated. Malcolm Cheers Adam. |
Mon, Oct 6 2008 8:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>Sorry for being very green but the execution plan means nothing to me so can someone please explain how and where. There are two ways to get the execution plan. 1. The simplest - use DBSys - open the new sql query page (table icon with ?) tick the box Generate Plan put in your sql and click Run SQL. When its finished click on the Plan page. 2. The alternative - In the source code set queryr.GeneratePlan := True, after the query has run access query.Plan When you have an execution plan post it here and we'll help interpret. >These reports are part of the system and as such I do not >have access to the code. You have a bit of a problem then, is that no access, access to the sql or ..... Looking at the sql you posted you want indices on: ProductDetail.ProductCode ProductDay.ProductCode ProductDetail.LocationNo ProductDay.LocationNo ProductDay.SalesDate >I am reluctant to mess around with indexes as I do not want to cause any problems with the system. I copy the complete database to >my laptop to run my reports. That must take a long time. ADDING an index to a database in DBISAM should cause no problems at all (unlike removing one). If the indices I've indicated are not present then the query will not be optimised. Roy Lambert [Team Elevate] |
Mon, Oct 6 2008 9:50 AM | Permanent Link |
Malcolm | Roy Lambert <roy.lambert@skynet.co.uk> wrote:
Malcolm >Sorry for being very green but the execution plan means nothing to me so can someone please explain how and where. There are two ways to get the execution plan. 1. The simplest - use DBSys - open the new sql query page (table icon with ?) tick the box Generate Plan put in your sql and click Run SQL. When its finished click on the Plan page. 2. The alternative - In the source code set queryr.GeneratePlan := True, after the query has run access query.Plan When you have an execution plan post it here and we'll help interpret. >These reports are part of the system and as such I do not >have access to the code. You have a bit of a problem then, is that no access, access to the sql or ..... Looking at the sql you posted you want indices on: ProductDetail.ProductCode ProductDay.ProductCode ProductDetail.LocationNo ProductDay.LocationNo ProductDay.SalesDate >I am reluctant to mess around with indexes as I do not want to cause any problems with the system. I copy the complete database to >my laptop to run my reports. That must take a long time. ADDING an index to a database in DBISAM should cause no problems at all (unlike removing one). If the indices I've indicated are not present then the query will not be optimised. Roy Lambert [Team Elevate] |
Mon, Oct 6 2008 10:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
I don't know if you meant to reply but I can't spot any new text in that email. Roy Lambert [Team Elevate] |
Mon, Oct 6 2008 10:27 AM | Permanent Link |
Malcolm | Roy Lambert <roy.lambert@skynet.co.uk> wrote:
Malcolm I don't know if you meant to reply but I can't spot any new text in that email. Roy Lambert [Team Elevate] Hi Roy, Don't know what happened there, appreciate your time You are right I do not have access to the system programs/sql's in any way shape or form. By using EMS's DBISAM Manager I can see the tables have the following indexes ProductDay Primary - SalesDate,LocationNo,TerminalNo,PriceLevel,ProductCode SalesDate - SatesDate LocationNo - LocationNo TerminalNo - TerminalNo PriceLevel - PriceLevel ProductCode - ProductCode ProductDetail Primary - ProductCode,LocationNo SellCode - SellingCode,LocationNo SellingCode - SellingCode LocationNo - LocationNo ProductCode - ProductCode I don't know how to get to DBSys all I have downloaded is the ODBC driver........ Will "queryr.GeneratePlan := True," run/work in VB6 Regards Malcolm |
Mon, Oct 6 2008 11:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
>Don't know what happened there, appreciate your time No problemo >You are right I do not have access to the system programs/sql's in any way shape or form. Ouch >By using EMS's DBISAM Manager I can see the tables have the following indexes Comparing those it looks as though you have the necessary indices >I don't know how to get to DBSys all I have downloaded is the ODBC driver........ Will "queryr.GeneratePlan := True," run/work in VB6 No idea I've never used the ODBC driver. To get DBSys just download the additional utilities from Customers - Download Products DBSys is a freebie. The other thing I can suggest is if the data isn't sensitive, and not to large as a zip send it to me with the query and I'll have a look Roy Lambert [Team Elevate] |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |