Llohr
Apprentice
Joined: 17 May 2005
Posts: 108
|
 Posted: Thu Mar 27, 2014 7:57 am
The Need for Speed
|
So, having recently made a comment in some thread about never having written a script that took more than an instant to execute, I went ahead and made one.
I put together an equipment database script using nested datarecords (with some further nested stringlists) and got everything working the way I wanted. Then I started adding items to the database. As I went along, I'd occasionally use my database searching alias to ensure that everything was going in correctly, and I noticed that the larger the database got, the slower it would spit out results. Once I hit about 400 records, it took maybe 8 or 9 seconds to spit out a dozen records. I assumed (foolishly, I later learned) that the greatest contributor to the time it took to output results matching my specified criteria was the searching part.
My first instinct was to divide the database into multiple databases by wear location, which DID speed things up, of course, but only prolonged the inevitable. Eventually the changes I made included:
1. Defining a local variable as equal to my database and looping through that to find equipment instead of the actual database. No noticeable improvement gained.
2. Upon finding a matching record, defining a variable as equal to that one record, then calling the extraction alias on that instead of on the key in the full database.
Found a huge improvement in speed there. I worried that the #loopdb would find the next result and change that variable while the extraction was still in process,
but I found that the loop appears to wait until the called alias completes.
3. Setting a local variable as equal to the database key called by the searching function, and extracting values from that. Approximately the same speed as method 2.
4. Both methods 2 and 3 simultaneously. Most consistent speed, but no real improvement between this and 2 or 3 alone. Pulled all 40 records from a database in about 1.8 seconds on average.
5. All methods 1-3. No noticeable difference between this and 4.
After finding that methods 2 and 3, either together or singularly, resulted in output rates not far below the speed at which the records can be drawn to the screen, I decided to try going back to a single database. After another quick sorting alias compiled everything, I adjusted the search and extract aliases and tried again. The script still pulled records out at about 40 records in 1.8 seconds.
My conclusion is that it takes significantly longer to pull values out of a known record in a larger database than it does to find records in the same database using some number of criteria. My searching alias currently requires "wear location" and a level range, and accepts up to 3 of any other minimum stat to find matching records (now that the database is compiled, I'll be removing the "wear location "requirement).
So, my question is, why does it take longer to pull out a known quantity from a larger database than it does to loop through the same database and search for variable quantities? When I do, for instance, a "#show @EQDB.some-item.Level," does it check every key for a subkey called "level," and then check to see if it's under the right key? Does, like nested logic, recursion begin at the deepest level and work its way outward?
I'm also interested in hearing about any other database optimization tips and tricks that others have come up with. I'm currently quite satisfied with the speed at just over 400 records, but that's just what I had lying around. Who's to say what sort of speed I'll see with the current script when I'm at, say 5000 records?
I can toss up examples of code, if anybody cares enough to look. |
|
