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
rby...@safesectors.com
(909) 238-9012 m
------ Original Message ------
From "Doug Hamilton" <bugl...@wi.rr.com>
To "R:Base List" <rbase-l@googlegroups.com>
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 rbase-l+unsubscr...@googlegroups.com.
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 rbase-l+unsubscr...@googlegroups.com.
To view this discussion visit
https://groups.google.com/d/msgid/rbase-l/em83ba50bc-2f20-4610-8758-a6eaea782428%40829c55c8.com.
