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.
