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

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]

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]

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.

"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.

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]

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]

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]

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

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]