Good day,
I'm actually doing some tests, and found out an "interesting" way to optimize the reports.
As most of us know, computers are much better at handling numbers than strings. To speed up the sorting process, and minimize resources requirements to perform them, in the past with a larger project, I was using weight factors for strings and doing the sorts based on these weights instead of doing the alphanumerical sort over and over again, which lead to a significant improvement, specially for large tables.
Since there is quite a few strings manipulation involved in a product like MetaTraffic, I was about to create a prototype and see if that would be worthwhile to use a similar approach with it.
Any suggestions/comments/thoughts are more than welcome.
Regards.
Optimization in reports sorting.
Seems you've got a trade-off when writing a record to the database:
1. do some analisys of each string and store the weighting function with the INSERT command, or
2. index the field, which makes the server do the same thing.
Both will cost some performance when INSERTing, and both will yield a great improvement when reading the data out.
For me it was enough to index the pl_datetime field, as most of the report functions I rarely use, but pl_datetime gets used on every query. And since records are entering the database sequentially on datetime, the indexing that follows every INSERT costs almost nothing in performance.
Optimization in reports sorting.
Good day,
After re-evaluation, I'm gaining only but few milliseconds, so I don't think it is worthing all the coding effort.
For a non-normalized database, I could see a substantial, even worthwhile improvement. Naturally, the process of weighting the string would not be performed at each insert. It would be invoked on at reporting time, in fact as an action prior to the reporting session, or as a on-demand thing, pretty much like the compaction.
For almost completely normalized data as I have now, I can easily deal with indexes on some string fields, since the tables containing the strings are actually relatively small, all being under 1000 records.
Anyhow, thanks for the input.