Jim, All the queries involved are searches of one column for reporting or error checking purposes. I would remove the indexes if I could get away with it!
I discovered this "problem" when running some tests of an import routine in a test copy of the db. When we tried to delete 1000 rows to restart the test, it took over an hour to delete those rows when it should have taken less than a minute. The reason I usually avoid inefficient indexes is that deletes from a table containing them will take an unusual amount of time. In addition, I have found that these deletes can sometimes interfere with other users or cause the indexes to become corrupted. I had this situation at one client where they would have index problems every couple of months. It turned out that I had a Y/N column indexed and they were deleting hundreds of rows in a batch at the end of each month. Once I fixed the column so it would contain unique positive or negative values instead of y/n the problem went away and has not returned. I fact, they haven't called me for 2 years! While this worked for Y/N, or True/False, or 1/0, it would not help where there were more than 2 unique values. In any case, it would have been far simpler to use this new trick to fix the problem, rather than having to change the column type and rewrite code. I've also run into slow deletion issues in my current application. I had workarounds to do a logical delete and then do the actual deletes off hours. Once I removed the inefficient indexes on the tables, I went back to doing the deletes immediately. They executed instantly. So, to make a long story short, I'm thinking that some of these problem indexes could be hacked in the way I described, since there is an autonumber column in the table. Obviously, I will want to search for where clauses that include each of those indexes before changing them to avoid causing a slowdown from confusing the query optimizer. My big concern right now is whether I am going to run into problems down the line with using the same column in several multi-column indexes. I wouldn't want to fix one problem only to create another hard-to-debug problem. You advise about "If it ain't broke, don't fix it." Is well taken! But I think that as this table grows beyond its current size of 144K rows, we will be seeing more "waiting for resources" messages when a row is deleted. Thanks for stirring the pot for me! Dennis McGrath -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James Bentley Sent: Thursday, May 15, 2008 5:14 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Index Question Dennis, You state that the "inefficient indexes" are heavily used in queries. 1) if you convert the indexes to a multi-column indexes are you sure that the RBase query optimizer can use the revised indexes in the query without revising the query? 2) Have you verified that the RBase query optimizer really uses those indexes in processing the query? In my opinion, if the index is "inefficient" I believe the optimizer is less likely to be using that index. 3) Often there is a delicate balance between indexing items, disk space used, and processing efficiency. This can be especially inportant if the database is one that has existed/been updated through many version of RBase. Also, If there has been substantial changes in the database structure as company operations have changed. I have several databases that were created way back in version prior to v3.1 for DOS and subsequently upgraded to the latest and greatest. Over the years I have tried to take advantage of improvements implemented but often I have inefficent programming since the system is working and it would require a major rewrite of the existing application to remove some of the inefficencies. There is an programming principal "If it ain't broke don't fix it". 4) Instead of a general worry about "inefficient" indexes I would focus on specific processes that take the most time/resources and try to improve them. Jim Bentley --- Dennis McGrath <[EMAIL PROTECTED]> wrote: > I was discussing inefficient indexes with a colleague today. > We have a table with many indexes on inefficient columns. > It has not become an issue (at least not an obvious one) since > we seldom delete more than one record at a time. > > There apparently was no way to avoid indexing these columns as > they are used heavily in various queries. > > Today I had a brainstorm. I read recently that some types of > SQL Server indexes add a hidden "uniquifier" to each table and > include it in their indexes behind the scenes so even an index > on a mostly null column will not cause problems. > > The obvious parallel in RBASE would be to change all these > inefficient indexes I mentioned to multi column indexes with > the table primary key as the second column in each. > My tests indicate that this does not seem to affect > performance negatively. > > My BIG concern is whether having the primary key included in > so many multi-column indexes would cause problems in its own > right. > > If it would not, it would be a sweet solution. > > Has anyone tried such a scheme? > > Any feedback, especially from Razzak, would be appreciated. > > Dennis McGrath > > > Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293
