ISTech Support Forum - Version 11 very slow?

kkmfg Senior Member Ghost of the code Posts: 411 Gender:	Re: Version 11 very slow? Reply #15 - 09/10/07 at 06:32:36 Ahh... I see what you mean. Yes, the index.ddf file may not include the new index and so you'd have to add it back.
Back to top	Collin K & K Manufacturing, Inc EvoERP Version 1-22-10 SP3 5 User Workgroup Pervasive 10 IP Logged

kkmfg

Senior Member

Ghost of the code

Posts: 411
Gender: male

Re: Version 11 very slow?
Reply #16 - 09/10/07 at 13:21:31

Quote from kevind on 09/09/07 at 18:34:17:

Now, having said that, it is only necessary to run ODBCDDF.RUN when there is a change to the table definition. This does not happen
very often. It would be nice if I did not lose the newly created index when it becomes necessary to run ODBCDDF.RUN in the future.

Well, since PervasiveSQL makes file.ddf, field.ddf, and index.ddf into tables it should be possible to store SQL commands that add the indices back in (along with your security additions and views.) Of course, then you'd have trouble if they ever did decide to add the index in themselves. In a perfect world the Evo updater would scan the file, field, and index tables for things it needs to add or change and leave the rest alone. I'm not sure what it currently does when it has to update.

Back to top

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10

IP Logged

NovaZyg

Administrator

When all else
fails.. Read the
instructions.

Posts: 789
Gender: male

Re: Version 11 very slow?
Reply #17 - 09/10/07 at 14:16:03

The Evo update, does just that. It looks to see what you have in your dictionary, and compares it to the xfr files that I supply (my Dictionary) if it finds new stuff in mine it adds it. If you have new stuff it ignores it and just leaves it there.
If I were to add the index to the update then if I used the same name as you did, it would just leave your stuff alone, but if i had a different name (and values/fields) for the index it would create mine.

Wink

As for the DDF's I have nothing to do with that stuff. There are 2 utils that were provided by DBA way back when.. UT-J and ODBCDDF that read the current DBA btrieve dictionary and create the ODBC Files. I do not have the source to either of those programs so I do not know what exactly they do.
Roll Eyes

Back to top

Allen Landry
Evo~ERP

EvoERP and DBA Classic always Beta's of the Latest versions.

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: Version 11 very slow?
Reply #18 - 09/10/07 at 17:57:08

Alan,
Are you saying that if you change the DBA btrieve dictionary for Classic (if there is a database change) then ODBCDDF will create the ODBC compatible .DDF files from the Btrieve Dictionary??

If so, then is it correct that it is only necessary to add any new index definitions in the Btrieve Dictionary to make them 'Stick' when ODBCDDF is run??

If so, then I would lobby for the Invoice Date field in BKARHINV be indexed.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

NovaZyg

Administrator

When all else
fails.. Read the
instructions.

Posts: 789
Gender: male

Re: Version 11 very slow?
Reply #19 - 09/11/07 at 08:14:18

Kevin,

Yes, that is exactly what I am saying. But the downside is that if I add it then on our next update it will have to create this index for everyone, and the ARH files tend to be BIG files so it will take time.. If you can get all the users to agree to it I will add it.. Also what do you want, just the INVDTE or INVDTE+INV.NUM or...?

Back to top

Allen Landry
Evo~ERP

EvoERP and DBA Classic always Beta's of the Latest versions.

IP Logged

kkmfg

Senior Member

Ghost of the code

Posts: 411
Gender: male

Re: Version 11 very slow?
Reply #20 - 09/11/07 at 09:05:56

Quote from NovaZyg on 09/11/07 at 08:14:18:

Kevin,

Yes, that is exactly what I am saying. But the downside is that if I add it then on our next update it will have to create this index for everyone, and the ARH files tend to be BIG files so it will take time.. If you can get all the users to agree to it I will add it.. Also what do you want, just the INVDTE or INVDTE+INV.NUM or...?

One thing that irks me about btrieve is the lack of bitmap indexing. In postgresql you can index individual columns and if you ask for an index that involves multiple columns it combines the one column indexes to build a master multicolumn index. Quite nice...

Anyway... Unless there are huge numbers of invoices a day I would think that indexing by just the date would be fine. So that's my vote.

Back to top

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: Version 11 very slow?
Reply #21 - 09/11/07 at 12:33:11

Quote from kevind on 09/06/07 at 12:36:50:

The Root cause problem with this report is that the Invoice Date field in BKARHINV is not Indexed.

Lynn / Allan - Could an index for this field be added to this table?

I do not know why the V8 of this report ran so fast. Both are accessing the DBA data with ODBC.

By using INVTXN (where invoice date IS indexed), the report can be made to run very fast.

Further investigation reveals that I made more than 1 change when I used INVTXN to provide a Invoice date index.
I also changed the order of linking in the report.

So here is the bottom line:

No Invoice date index:
8 minutes to run with original table links
7 seconds to run with revised links (BKARHINV as root table instead of BKARCUST)

With an Invoice date index:
1 Minute 30 seconds with Original table Links
2 seconds to run with revised links

Other Times
Time to Add Index through Table Designer: 10 Minutes (72Meg file) (Pervasive had to expand the file a couple of Meg and allocate Index pages)
Time to remove index through Table Designer: 20 seconds
Time to Add Index again through table designer: 14 seconds (internal index pages were already allocated, just needed to re-index 49650 records)

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged