When I was working on my talks for London Perl Workshop 2012 I looked to a whole bunch of Perl code at different places. I noticed that alot people are still using SQL code and use the DBI interface. While there is nothing wrong with it, it leaves the reponsibilty to the developer – but the scalability is with the operation engineers.
I’ve found a code snippet which basically does this (in gluecode):
seeked_value = "Ulrich Habel" data_in_hashes = run_sqlquery( * from table); loop (data_in_hashes) until value = seeked_value;
Interesting. This always will be a full table scan on the database, pulling all the available data to look through every result until a certain value is found.
Is there possibility to limit the query to the data needed? If you know the column name there is a way to look specify a certain query. Probably we can have several talks about proper SQL queries here but it won’t solve the real problem. Probably you are working with huge lists anyway. You don’t have an idea what your database looks like – you don’t have a clue about business items in there – do you think SQL is a good way to access the data?
Don’t worry – this won’t lead to a NoSQL/SQL comparison here. This is about SQL inside Perl. So, what you are doing is running a structure query language on non (or not that you know) structured data. You are querying everything and use Perl to parse the outcome. What you want is a regex engine for data stored in SQL databases.
Stop it. It doesn’t make sense and it makes us look bad – but this a common example of how things are being implemented, especially with older systems.
Suggestions for a solution? Yes, fix your data store technology stack either with SQL or NoSQL and use a proper API.