This would not be considered best practice. Indexes are kept in the .xx3 file.
(xx depending on what version you are using) The more indexes you create the larger this file gets. The larger it gets the slower the indexes work. This is why you do not want to create an index on every column... you .xx3 file would soon get larger than the .xx2 data file and the overall database performance would come to a halt. So when indexing a Y/N column, it does not "index" only the Y values, but both. So the index file still contains 100% of the Y/N "data", 95% N and 5% Y. So the index would only be of benefit on 5% of your data, if at all. Not a very good retun on processing power. You most likely would see some increase in speed when finding the "Y" values, but if you did this on a number of tables and columns, your overall index performance would start to degrade significantly. A high cost to pay for finding only a very few Y values. So the theory is meant to apply to over all database index performance. If you apply too many indexes, your performance will start to decrease. You should shoot to get the best performance for the amount of data. -Bob ----- Original Message ----- From: "MDRD" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Wednesday, January 6, 2010 8:46:52 AM GMT -06:00 US/Canada Central 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? >> > >
