Hi all, Buddy made a good suggestion. Create a default datetime column. Create a computed column that has a dextract. That computed column can then be indexed for fast retrieval. In one scenario, a temporary table can be projected using the default datetime stamp and used to print the labels after which the details are merged into the main table.
With computed columns the columns are reevaluated after the row is touched/updated. A projected table will reevaluate the computed columns. This is only noticeable with datetime columns. Regards, Ken On Sat, Jun 21, 2025 at 1:59 PM Bruce Chitiea <[email protected]> wrote: > Doug: > > Leaving the question of WHY once performs real-time searches against a > logically after-the-fact metric untouched, might the "semi-annual data > archiving" be performed quarterly, monthly, or even weekly to reduce the > search volume, without compromising real-time operations? > > As for real-time evaluation of computed columns, would love to have RBTI > chime in. I'm thinking one clue to how tightly the computation links to the > column appears when PROJECTing; only the column value makes the jump. > > Best, Bruce > > Bruce A. Chitiea > SafeSectors, Inc. > 1142 S Diamond Bar Blvd # 442 > Diamond Bar CA 91765-2203 > > [email protected] > (909) 238-9012 m > > ------ Original Message ------ > From "Doug Hamilton" <[email protected]> > To "R:Base List" <[email protected]> > Date 6/18/2025 12:31:51 PM > Subject [RBASE-L] - Speeding up SELECT WHERE ColName IS NULL > > I have an order table with a DateTime column, LabelsPrintedDTS. (DTS = > DateTimeStamp) > The table has a couple hundred thousand rows. > LabelsPrintedDTS gets updated with .#NOW when labels for specified rows > are printed. > Labels to be printed are selected WHERE LabelsPrintedDTS IS NULL. > > As the table grows between semi-annual data archiving, the search for > labels to print becomes slower. > I don't think indexing LabelsPrintedDTS column will help because: > 1) NULL values aren't indexed (this db is RB 10.5) > 2) Indexing this column will bloat RX3, there may be up to 100 duplicate > LabelsPrintedDTS values. > > What about: > Creating a calculated Boolean column, PartLabelsPrintedYN = > IFNULL(PartLabelsPrintedDTS,0,1). > i.e. If labels have not been printed, the value of PartLabelsPrintedYN is > 0, if printed, the value is 1. > Then the WHERE clause becomes: WHERE PartLabelsPrintedYN= 0 > Would such a Boolean search be faster? > > I'd test it but don't think my single-user system would be a fair > evaluation compared to customer's multi-user system. > > I saw a Tip-Of-The-Day article about NULLS and indexing in RB 11 but can't > find it. > For whatever reason, I didn't think it would help this situation. > > TIA, > Doug > > > > > -- This email has been checked for viruses by Avast antivirus software. > www.avast.com > > -- For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- You received this message because you are subscribed to the Google > Groups "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/rbase-l/5926b3d5-a5a7-4547-ae52-8ed9ab8448f7%40wi.rr.com > . > > -- > For group guidelines, visit > http://www.rbase.com/support/usersgroup_guidelines.php > --- > You received this message because you are subscribed to the Google Groups > "RBASE-L" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/rbase-l/em83ba50bc-2f20-4610-8758-a6eaea782428%40829c55c8.com > <https://groups.google.com/d/msgid/rbase-l/em83ba50bc-2f20-4610-8758-a6eaea782428%40829c55c8.com?utm_medium=email&utm_source=footer> > . > -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/CADeqk-4QUOsVWVk1C%3DuSzoLsFcw_EkL%2BgHiTgn7HCcccM7xusA%40mail.gmail.com.
