SQLite and speed #203

osc2nuke · 2023-09-02T13:21:21Z

osc2nuke
Sep 2, 2023

I working on a desktop App ( Windows Only for now) where it requires to load +100.000 rows of data on a single page.
I understand that 'web-applications' are not the best way to do that, however, NativePHP is for Desktop apps.

So i ported sqlite.exe directly in my project.
And bc of that i can load a .CSV file of 30MB in less then 2 seconds into the nativephp.sqlite DB.

When i use Laravels basics, it takes between 15 and 20 minutes.

Just not figured out yet how to GET that data loaded to the blade-view, in a decent speed.

I picked-up coding again after several years, and i prob just have no clue anymore.

simonhamp · 2024-05-08T10:27:46Z

simonhamp
May 8, 2024
Maintainer

There are a few things to consider:

How often are you doing this kind of sizeable import?
Importing directly using SQLite's CLI tool is bound to be faster as there are fewer layers in between and I would recommend this method.

That said, while doing this via Laravel is never going to be as fast as using SQLite's tool directly, the speed of doing it via Laravel is really going to depend on how you implement it. If you're inflating models with each row of data and importing one by one, it will indeed be very slow.

If doing it from the Laravel side is required for whatever reasons, you should consider batching the import and using a secondary/background process to handle the work so that your main php thread isn't blocked. I've had a good time using Laravel Excel. Achieving some of this right now in NativePHP during the alpha is going to be tricky.

If speed is your main concern tho, you could potentially ship a SQLite executable with your app.
Can you/should you ship the SQLite executable with your app?
Can you? Yes. There's not direct support for this in NativePHP just yet, but the same principle is already in use by NativePHP - we ship the PHP binary as part of the bundle. A similar approach could be applied to any self-contained executable.

Should you? This will depend on a lot of factors, but the first one is licensing: Are you allowed to? This is your responsibility to check before you distribute any third-party software.

Secondly consider safety: What does this executable do? Could it endanger your users by disrupting their system in some way? Again it's your responsibility to make sure that any software you distribute is free from bugs and vulnerabilities that could harm your users.

Thirdly consider usage vs bloat: Is everyone who downloads your app going to use this feature? When bundling executables is easy, there's the risk of bundling every little tool you need for every little job that your app needs to do. But if that only benefits one user, you could be making the experience of downloading and installing your app unnecessarily burdensome (and risky!) for the majority of your users.

Finally, consider support: If you ship someone else's software, are you prepared to support your users in cases where something goes wrong? Remember, this is a tool you don't own, control or have a deep insight into, so can you really support it?

How about ongoing updates? You need to keep up to speed with each piece of software's release cycle and ship new versions of your app when your dependencies need updating, especially if one of your dependencies needs updating because of a security vulnerability.
Is NativePHP the right tool for this job?
This ultimately depends on what you're trying to achieve. If your only goal is to provide a simple GUI for loading large CSV files into a SQLite database and rendering the result back out to the user, you may be better off getting your users to use a more robust database management tool.

If this is just one part of a more feature-full app tho, you should use whatever tool you're most comfortable with to build and maintain. Of course, I would personally love to see you build this with NativePHP!

Remember that NativePHP is based on web technologies. HTML performance will degrade when you start rendering tens of thousands of nodes in a web view. The parameters here will depend on the specific web rendering engine used. In Electron, that's Blink (via Chromium), which is used across all platforms.

Even native UI libraries might struggle fi you were to render hundreds of thousands of UI elements to the screen, though they would likely have much better performance than HTML.

So good practice here would see you introduce some form of pagination whatever solution you use when rendering the data out to the screen.

And don't forget that performance of your apps in NativePHP is largely limited by the tools it uses under the hood - right now, that's Electron, Chromium, and Laravel running on top of a statically-compiled PHP executable - none of which the NativePHP maintainers are directly involved in to have a meaningful impact on performance.

So while there are undoubtedly areas where NativePHP can improve performance, I wouldn't anticipate any considerable performance improvements to come from updates to NativePHP itself; these would most likely come from upgrades to the aforementioned projects.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

NativePHP

SQLite and speed #203

{{title}}

Replies: 1 comment

{{title}}

Select a reply

NativePHP

SQLite and speed #203

osc2nuke Sep 2, 2023

Replies: 1 comment

simonhamp May 8, 2024 Maintainer

osc2nuke
Sep 2, 2023

simonhamp
May 8, 2024
Maintainer