Thanks all for explaining this.
The next time I am working on that part of the program I will drop the
index.
Marc
--------------------------------------------------
From: "Emmitt Dove" <[email protected]>
Sent: Wednesday, January 06, 2010 9:15 AM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: Update command taking forever
Marc,
That would be totally useless. Think about it this way: You have two
decks
of cards, one with red backs and one with blue backs, shuffled together.
You want to sort the cards by suit, rank and color of back. You put all
the
cards are face down on the table, so that all you see is red and blue.
You
can do the sort into two piles by back color easily, but that doesn't help
much with the desired goal. You have to turn them over in two piles, then
sort each pile by suit and rank, then merge the piles by interleaving them
perfectly. On the other hand, if you start with the cards face up, once
you
have them sorted by suit and rank it is simple to turn one each pair of
cards over and observe the color of the back.
A very wise man by the name of Wayne Erickson once told me, in effect, to
only use indexes on columns with more than a few distinct values. He held
that the overhead imposed by the indexes on the columns with very few
values
far outweighs any possible benefit. An index-based retrieval is going to
work best when none of your values are identical.
Emmitt Dove
Manager, Converting Applications Development
Evergreen Packaging, Inc.
[email protected]
(203) 214-5683 m
(203) 643-8022 o
(203) 643-8086 f
[email protected]
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of MDRD
Sent: Wednesday, January 06, 2010 9:47 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Update command taking forever
Is it OK to index a Y/N column if you have 95% N and 5% Y
and you only search for Y?
I was wondering if those 5% Y's would pop up faster compared
to RBase going row by row.
Thanks
Marc
. Do not index any column with relatively few
distinct values (such as a Y/N column).
Emmitt Dove
Manager, Converting Applications Development
Evergreen Packaging, Inc.
[email protected]
(203) 214-5683 m
(203) 643-8022 o
(203) 643-8086 f
[email protected]
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Dan
Sent: Wednesday, January 06, 2010 8:19 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Update command taking forever
Bob,
Thanks for responding.
1. using latest version dated last week
2. QUALCOLS is set to 10 already
Have not unloaded and reloaded in the last week, but that table never
gets deletes, but that still is something to try, can't do it now
till everyone goes home tonight and I stay late. I could try it on
a backup database and see. I guess that is my next step.
3. Using the update on just one column where there is a non zero, I
am getting one row updated per second and a half. So the command is
working, but will take a long time.
Dan
At 08:08 AM 1/6/2010, you wrote:
Dan,
Indexes are only meant to increase the speed of identifying rows,
not the actual update.
So you definitely do not want to add indexes to the columns you are
updating. That would
actually slow down the process as you would now be updating the
indexes as well as the
data.
Updating 100,000 rows should not take too long, so something is
certainly askew.
So some things to check...
Look at your setting for QualCols
R:>sho qualcols
QUALCOLS is set to 10
If it is not set to 10, then set it. This can make a significant
difference.
Unload and Reload the database if you have not already done so. If
there have
been much row deleting etc. sometimes the database files need
packed. Although
I am hard pressed to think this would cause an hours long update.
Try updating just one column at a time and see if the time is
different and report back.
Make sure you are running the latest version. There were changes
made that directly
effected speed in certain cases.
-Bob
----- Original Message -----
From: "Dan" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Wednesday, January 6, 2010 6:25:51 AM GMT -06:00 US/Canada Central
Subject: [RBASE-L] - Update command taking forever
Hi,
We have finally made the conversion to Turbo 8, and am having
troubles with my month end financial processes. I assumed it was
index problems and made sure there are indexes.
Update ardetail set invcur = 0, paycur = 0
used to take seconds in 7.5 26 hours and counting in turbo
in 7.5 neither invcur nor paycur had indexes.
I added them now, and still no increase in speed. So then I
thought, ok with the new indexes, lets key just off those..
Update ardetail set invcur = 0 where invcur <> 0 (excluding 95000
rows) only 1300 should now be looked at, and this still takes forever.
Where should I look next?
