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 >>> >> >> > >
