Also, your where clause should be in the same column order as you index Dennis McGrath
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MDRD Sent: Wednesday, July 14, 2010 2:53 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: How to make this Delete command faster So the most Null goes last? Never new that. Thanks Marc -------------------------------------------------- From: "James Bentley" <[email protected]> Sent: Wednesday, July 14, 2010 2:01 PM To: "RBASE-L Mailing List" <[email protected]> Subject: [RBASE-L] - Re: How to make this Delete command faster > 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 >>>> >>> >>> >> >> > > > > >
