Don't forget that the order of the columns in the index is also important. TBOM the following seq is best. txdate is never null subjx has is almost never null on my sample DB sympt1 has over 12,000 nulls Jim Bentley
American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- From: Dennis McGrath <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, July 14, 2010 12:08:29 PM Subject: [RBASE-L] - Re: How to make this Delete command faster There must be a lot of rows with the same date. It is having to test every row for the nulls. A multi-column index is a good fix because it will be efficient in this case. Dennis McGrath -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MDRD Sent: Wednesday, July 14, 2010 11:51 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: How to make this Delete command faster I just tested using an Index on all 3 columns, the Delete now takes less than 1 sec There are 13,339 rows of data txdate is never null sympt1 has over 12,000 nulls subjx has is almost never null on my sample DB Thanks all Marc -------------------------------------------------- From: "MDRD" <[email protected]> Sent: Wednesday, July 14, 2010 11:33 AM To: "RBASE-L Mailing List" <[email protected]> Subject: [RBASE-L] - Re: How to make this Delete command faster > Thanks Albert, Paul, Dennis, BIll > > I have an FK on Custnum, an Index on txdate. We will only be deleteing > from 0 to maybe 10 rows > > The problem is you can have either sympt1 Null or subjx Null but not both. > So, I didn't want > to Index something with lots of Nulls or Index columns that we will never > search. > > This Delete command takes 30 second on a 10/100 workstation but is fast > enough on the local computer. > So, maybe I could move this code to an End of the day command file we run. > All of our users are told to > run this on the server (local computer)? > > Thanks again, I have been playing with ( ) for the past 4 hours thinking > that may help, but no matter > where I put the ( ) it still takes 30 seconds. > > At least now I have some new ideas to test, > > Marc > > > > > > -------------------------------------------------- > From: "Albert Berry" <[email protected]> > Sent: Wednesday, July 14, 2010 11:05 AM > To: "RBASE-L Mailing List" <[email protected]> > Subject: [RBASE-L] - Re: How to make this Delete command faster > >> Try putting a compound index on the table. >> >> CREATE INDEX travcarddelete ON travcard (txDate, sympt1, subx) >> >> Albert >> >> MDRD wrote: >>> Hi >>> This command takes too long when we run it from a workstation but I can >>> not think of any way to speed it up. >>> txdate is Indexed >>> symptom is Text 150 no Index we will not search on this column >>> subjx is Varchar no Index and we will not search on this column either >>> DELETE ROWS FROM travcard + >>> WHERE txdate = .#DATE AND sympt1 IS NULL AND subjx IS NULL >>> any suggestions? >>> Thanks >>> Marc >>> >> >> > >
