[RBASE-L] - Re: How to make this Delete command faster

[MDRD]

So the most Null goes last?
Never new that.

Thanks
Marc


--------------------------------------------------
From: "James Bentley" <jamesraybent...@yahoo.com>
Sent: Wednesday, July 14, 2010 2:01 PM
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
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
jamesraybent...@yahoo.com
tel: 1-504-737-3293



----- Original Message ----
From: Dennis McGrath <dmcgr...@qmiusa.com>
To: RBASE-L Mailing List <rbase-l@rbase.com>
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: rbase-l@rbase.com [mailto:rbas...@rbase.com] 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" <mdrd...@sbcglobal.net>
Sent: Wednesday, July 14, 2010 11:33 AM
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
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" <alb...@albertberry.com>
Sent: Wednesday, July 14, 2010 11:05 AM
To: "RBASE-L Mailing List" <rbase-l@rbase.com>
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