Do you know where your variables are? | |
PerlMonks |
comment on |
( [id://3333]=superdoc: print w/replies, xml ) | Need Help?? |
Holy cow - thanks all for the input. This is really being extraordinarily interesting.
OK, i've pretty much digested the common thread to replies - that so long as the data fits in memory, there should be no surprise that a custom coded Perl solution beats an overhead-laden rdbms soltuion. The question is a strategic one - trade flexibility, robustness and standardization for speed, or not? demerphq's ideas appear to me to closely resemble my DB3 - splitting the data up into enough mysql tables to have every column indexed, except I went to the extreme and had one column per table. We got the result he expected - DB3 was the fastest of all mysql-based queries. The mere fact he suggested doing something like this makes me think I should keep this solution in the back of my mind - maybe sometimes odd design can justify itself. BrowserUK - you are a coding machine! But I'm not clear on how we went from talking about 250milliseconds-per-query in paragraph 2, then 250 queries-per-millisecond in paragraph 4. I do very much like your string-row solution. Again, it's giving me ideas about reading up on piddles. Do I understand correctly that the trial times are for a single execution? If so, it looks like your strategy applied to 3,000 individual text tables is still holding the speed record. Thanks. I think then what I have in DB4 is a baseline. This is a fast as you could possibly hope for, so shoot for the closest to to that within a mysql solution. At least we know how 'good' a solution is with a baseline for comparison. I wonder if a hand-coded Perl query system might make a good standard practice in database development when the data lends itself to that, so that developers know how fast is fast in a particular situation? The various treatments of DB1 & 2, including dragonchild's, make me really want to see that schema approach the baseline. I think it might be the best compromise. SO, I'm embarking on a matrix of tests on that schema. The variables are:
Then we'll make a strategic choice. Thanks all for your participation. In reply to Re: Basic Perl trumps DBI? Or my poor DB design?
by punch_card_don
|
|