Zugg Software :: View topic - Database Searching/Pattern Matching Efficiency Question

Newbie Joined: 30 Jun 2013 Posts: 7

Question about the database and searching functions. I currently have a column in a database that labels things out in a string list. In an example: 'black dragonscale'|adamantium|pearl|redstone|white dragonscale'. for context, these are alchemy ingredients. Whenever i try an alchemy it checks against these entries, creating a similar looking string of the current materials up for use, and comparing against the column listing all previous attempts, then if it is not a repeat, it attempts it, and logs the result and new string entry.

The problem that's occuring is over the lifetime of an enchanter, we start surpassing 10k, 15k, and 20k attempts. At about 10k, lag on the search to match against previously attempted formula's gains noticeable lag, thus slowing down the whole process. The more entries in the database, the longer the lag is.

I'm curious if instead I was using a double digit system (as there are 100 materials usable in alchemy), I would eliminate the lag I'm experiencing. in example, instead of the previous string, what if all the strings were to the theme of: A1|B2|C3|D4|E5. Would the reduced characters in each entry and reduced characters in the search string impact the speed at which it performs?

Or, of course, if anybody has any other ideas to eliminate the lag i experience when working with so many database entries, i'd be happy to hear it!

Thanks in advance,
Toade

Posted: Thu Sep 05, 2013 12:28 am

how are you searching against the string?
you could always assign the column to a $localVar and use %ismember() on it

Newbie Joined: 30 Jun 2013 Posts: 7

I do believe this specific part

#if (%dblist(list) =~ @mats)

Addresses what your asking... As it uses neither of the commands you mention, and judging by your post rating, I'll start looking into those two commands and see if i can figure out your meaning more precisely. I imagine it'd likely be more efficient? If a bigger chunk of the work is needed, I've no issues displaying it.

After reading the manual as I understand your suggestion, a string list of these lists (inceptionlist?), and using is member to search, then using the result to decide the appropriate course of action... I'm guessing that'd be much better than using an if statement and dblist to search and actually call values from the database? Any confirmation?

GURU Joined: 23 Dec 2000 Posts: 4834 Location: USA

Since you invested so much time into the DB module database, it's a tossup at this point, but you will see by far the biggest speed gains by switching to an SQL-based database. The "interface" consists of just three commands/functions:

#SQLDB -- sets up a database connection object
%sqldb() -- same as #SQLDB, except that you assign the result to a variable (this will be the more useful approach most of the time). You would add, edit, and delete records via this variable.
%sql() -- retrieves qualifying records based on the query string