[00:03] performance improvement in its SQL parser. And in this video, I'm going to take the time to understand it, how it works, why it works, and measure the myself. Learning from performance
[00:16] improvements in open-source libraries is a powerful way of improving one's own performance detective skills. It's also a great time to celebrate the contributors. In this case, kudos go out to Sona from Shopware. Let's look at the
[00:31] patch feature improvement performance of SQL Passer. It just has a single file SQL Passer. It just has a single file changed. Um just 20 lines added, 33 changed. Um just 20 lines added, 33 removed. Um it the explanation for the
[00:44] performance improvement is merged SQL patterns into one call and the and call the correct one based on the rag X hit. It includes a performance uh benchmark using the Hyperfine tool which uh we also explain
[00:59] Hyperfine tool which uh we also explain on our blog. Uh running an old and a new version of a test script. The test script is attached here. We'll go into script is attached here. We'll go into that in detail to show a two uh a factor
[01:11] that in detail to show a two uh a factor two improvement um for the new uh changes. So let's look at the diff and what it's actually doing. So in the constructor of the paser, there's additional logic being
[01:27] executed. So in addition to the previously available SQL pattern uh variable, there's now a second new one token patterns and it includes a new rax using named
[01:43] arguments. So the uh or named matches these named matches that can then be accessed directly from the prep match uh um result. So uh what is happening in the
[01:56] parse function? So sona removed a lot of code and especially an array of regular code and especially an array of regular expressions which maps to these calls expressions which maps to these calls here, these
[02:12] bit hard to read. So let's switch to PHP storm. We can see here in the old code that we are iterating over the are iterating over the patterns and
[02:26] um the patterns are again here the rax to uh closure calls and then the code here iterates over that and executes those patterns against the SQL statement
[02:38] and performs the work uh uh if there are matches. The new code looks different like we saw before. We are iterating uh over the length of the SQL string
[02:52] over the length of the SQL string matching the token patterns and um processing them based on matches in the named and positional arguments and calling the visitors uh the visitor functions appropriately. This is a very
[03:07] clever performance improvement because it makes sure that PR match is only called once for a range of different patterns and then depending on which sub pattern is matched only the logic for that sub pattern is actually executed
[03:24] and this is a regular performance improvement that other PHP libraries also apply for example J Bizzle's crawler detect or the fast route um PHP crawler detect or the fast route um PHP um library from Nikita. Let's talk a
[03:39] little bit about the history and why doctrine debal actually needs an SQL parser. When combining parameters with an SQL statement in PHP, you usually use prepared statements to safely do that. PDO has features to do this for scala
[03:56] PDO has features to do this for scala values such as integers or strings. Doctrine Debbal improved on this by adding the feature to pass arrays of adding the feature to pass arrays of parameters to positional or named SQL
[04:12] placeholders. This is a powerful improvement that is used to make select improvement that is used to make select wherein queries uh with placeholders very simple and doctrine can just expand the placeholder to fill as many
[04:28] the placeholder to fill as many placeholders as necessary to uh work placeholders as necessary to uh work with an array uh of parameters. There have been a few iterations of this code already in doctrine and it improved
[04:40] already from version two to version 3 considerably. Shopware always had a performance problem with this API because they are using arrays of parameters everywhere through their code base and um the improvements from
[04:55] version two to version three of doctrine debal already made their code much faster. With this new improvement, Shopware is going uh getting even faster and we are now trying to measure how much impact this change has. We are
[05:09] looking at the test script. I copied this test script over to my uh IDE and I set up a little uh
[05:24] playground for this. I copied the code over into the new and old uh versions um that Sona already showed on his pull request. I'm including based on old on
[05:37] the old version or the new version different versions of doctrine debal uh using composer. I installed them using two subdirectories and I improved on the SQL query here a little bit to get some
[05:51] understanding. The SQL statements that Sona provided in this test script is extremely large. It contains a bunch of subqueries uh joins and uh especially the select clauses contain hundreds of columns
[06:05] selected. A regular expression running over a string that is that long uh obviously can take a lot of time. if implemented incorrectly. So this is why
[06:17] uh we can see uh this big improvements here um that Sona uh uh made with this patch. The test script is really simple uh for this big query. We are running
[06:29] uh for this big query. We are running the SQL path 2,000 times expanding the parameters here. Especially you can see the array parameter type triggers the um the array parameter type triggers the um SQL paraser to expand the parameters in
[06:44] this particular location here where we have an where inquiry for the category ids and we are passing three ids here. So let's run this using Hyperfine like a son that uh locally I'm running
[06:59] Hyperfine with a warm up of one meaning I'm calling the programs beforehand to maybe um trigger some Linux caches and then I'm running the the old version against the new one. The Hyperfime now calls the program
[07:16] uh a few times. You can see uh that we have an estimate here of um how long the script takes. 2.8 seconds we have estimated for now and another 10 seconds
[07:29] of the benchmark is running and uh this is uh running uh a little bit uh slower than uh when I ran this without uh video capturing before.
[07:41] So let's see how uh if we can actually get a fairly realistic example uh run here. And it looks like we do. The new PHP script uh currently estimates at 1.2
[07:54] seconds. So we have an improvement of a factor of 2.3 faster than the old.php. And if you if we scroll up here, we see the same range of improvement
[08:09] 2.37 without the video capturing active um but it uh ran much faster. So this code shows that when just looking at the SQL paraser on a factor
[08:21] looking at the SQL paraser on a factor of 2.3 faster than um with the old code a massive improvement for this kind of u code the lesson that we can learn from this performance improvement that there is a lot we can do with regular
[08:34] is a lot we can do with regular expressions if we uh merge patterns and expressions if we uh merge patterns and use named matches to reduce the amount of regular expressions we call and to delegate based on the matches that we
[08:46] delegate based on the matches that we made instead of like having separate uh regular expressions that we evaluate individually. If you want to follow along, watch this next video about PHP performance improvements and have a nice
[09:00] performance improvements and have a nice day.