Would you move arrays to MySQL, or keep it in the script? - Webmaster General forum at WebmasterWorld - WebmasterWorld

Forum Moderators: phranque

Message Too Old, No Replies

Would you move arrays to MySQL, or keep it in the script?

csdude55

4:47 am on Sep 9, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I'm posting this under General since I'm debating between multiple languages, and this is really more about efficiency than the language. @phranque, I'll totally understand if you need to move it :-D

I have a Perl script that I import to other scripts via:

eval { require 'whatever.filter' };

This script has 4 associative arrays, totaling about 12k worth of data. The whole script is bout 42kb, so a little under 1/4 of it is these arrays. I don't modify them TOO often, maybe a few times a month.

I'm thinking about moving them to a MySQL table with 3 columns: array_name (ENUM with 4 possible values), key (VARCHAR), and value (VARCHAR). Then I could replace the 12kb of data in the script with this, which is more like 271 bytes:

# already using this module, so it's just here for your reference
use DBI;

my $sth = $dbh->prepare("SELECT array_name, key, value FROM table")
 or die "prepare statement failed: $dbh->errstr()";

$sth->execute()
 or die "execution failed: $dbh->errstr()"; 

while (($array_name, $key, $value) = $sth->fetchrow()) {
 $$array_name{$key} = $value;
}

Pros:
1. Smaller script means it would compile marginally faster
2. Moving these to MySQL means no real chance for a syntax error, so I could (probably) remove the EVAL function

Cons:
1. Harder to read
2. Each run is another MySQL query
3. It would take longer to process due to the query time of MySQL

Can you think of any other pros or cons? What would you do?

robzilla

8:09 am on Sep 9, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

Looks like your cons outweigh your pros. And you haven't even mentioned management yet. How are you going to update the "arrays" in MySQL?

If you are primarily worried about syntax errors, but you only update these arrays a few times a month, I'd say the most efficient route would be to validate your syntax after making changes, as mentioned in the other thread. Then you can drop the eval.

Alternatively, you could build a simple UI to edit the arrays (or build it into your cms), storing them as JSON, although that may be a little slower than your current method.

Does Perl have some sort of opcode caching, or is the code compiled on-the-fly every single time?

NickMNS

12:42 pm on Sep 9, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

First off I assume that the difference in speed between these two methods would be marginal at best. I doubt that it should be a deciding factor.

Typically one would want to separate the data from the code. If the contents of the associative array has no bearing on the execution of the code then it should be kept separate. As such you would not need to update a functioning script if the data is appended, thus saving the need to ensure that no syntax or other errors have be added to the script.

How are you going to update the "arrays" in MySQL?

I use a JSON file and an instantiation script, that at start-up checks the DB for the existence of the table, if it exists nothing happens else it imports the data to the DB.

csdude55

5:31 pm on Sep 9, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

Another two pros that I realized while laying in bed, unable to sleep:

3. I can share data between scripts more easily. I have 12 of these .filter files, with 3 of them being included to almost every script while the other 9 are unique for another script. But I could use this to filter users in all 9 scripts without duplicating information.

4. I could add / modify the arrays from my phone while on the road, instead of SSHing or FTPing in to the server.

How are you going to update the "arrays" in MySQL?

Probably just in phpMyAdmin (PMA). It would be a simple matter of going to the table and inserting a new row, which eliminates any possibility of a fatal syntax error. 99% of the time I don't have an issue with those fatal syntax errors, but every once in a while I find myself updating right before bed and forgetting to validate :-( So I've chosen to use EVAL as a safety net against that 1%, so that I don't accidentally take a major feature offline while I sleep. Or, more commonly, lay there wide awake and worrying that I did, then have to get up and triple check! LOL

Does Perl have some sort of opcode caching, or is the code compiled on-the-fly every single time?

That's a great question, and... I don't know. I'm sure there's a module for it, Perl has a module for pretty much everything! LOL There's probably one that'll make Perl hop out of the server and make you a ham sandwich, if you look for it. At the very least I've used PHP::Session to read sessions set in PHP.

But since these filters are typically used to stop an immediate problem (scams, spam, and trolls), I would really need it to run anew with every process.

After sounding that out, and after @NickMNS's reply, I'm leaning towards moving the arrays to MySQL...

robzilla

9:55 pm on Sep 9, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

It seems to be built into mod_perl. Keeps the compiled program in memory between requests.

There's probably a little extra overhead involved with MySQL compared to, say, a simple JSON file, but to update a JSON file without risk of errors you'd need to write some sort of phpMyAdmin-like interface, so that would be a bit more work. I still use text files a lot myself, often as caches for data in MySQL, so that's also something you could consider if you're concerned about speed (although the results I cache usually come from more complex queries).

graeme_p

1:57 pm on Sep 10, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

Moving these to MySQL means no real chance for a syntax error, so I could (probably) remove the EVAL function

That is what would make me use the DB: its less error prone.

csdude55

3:40 am on Sep 16, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

For those curious, I just finished up the rebuild.

The original file size was 53,722 bytes, and the final modified size is 24,280 bytes.

Running a speed test, though, on 1000 iterations the original script took 0.7948899269104s. The new one took 0.0207040309906006s! So it's 38 times faster!

robzilla

9:40 am on Sep 16, 2021 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I'm surprised it's that fast despite the MySQL invocation. Probably due to caching, whereas the eval had to run every time.

At ~0.8ms the speed bump isn't really worth it, but the increased ease of management certainly is, along with better sleep ;-)