Re: [sqlite] which is faster, PHP or SQLite?
On Feb 18, 2008 11:33 AM, Scott Baker <[EMAIL PROTECTED]> wrote: > > The less database hits you have to do, the faster your code will be. > Getting all the data into a PHP data structure should be the way to go. After reading all the replies, I have to agree with Scott for my particular situation. The dataset I am getting from SQLite is only selected images, more often then not less then 10 records. Storing them in a PHP array and checking the array to see if it contains the file that is being displayed seems the most speedy approach. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
Sqlite uses cacheing. I would suggest not storing large amounts of data in PHP arrays. It is buffer shadowing. Ideally with Sqlite you would use a cursor (the sqlite3_step logic) and pick up rows as you need them from the Sqlite cache. Digging a string of holes and filling them in is a tedious way to move a hole in the ground Zbigniew Baniewski wrote: > On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote: > >> The less database hits you have to do, the faster your code will be. >> Getting all the data into a PHP data structure should be the way to go. > > But, if one really is "loading all the data into memory at once" (just > "SELECT * FROM xyz") - where are, actually, any benefits from using SQL > database engine? > > Using plain file you can have about the same: open/read_all/close... done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote: > The less database hits you have to do, the faster your code will be. > Getting all the data into a PHP data structure should be the way to go. But, if one really is "loading all the data into memory at once" (just "SELECT * FROM xyz") - where are, actually, any benefits from using SQL database engine? Using plain file you can have about the same: open/read_all/close... done. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
On Mon, 18 Feb 2008 11:25:16 -0500, you wrote: >I am new to SQLite and databases, so I am stil learning how to >optimize their use... > >I am working on a "shopping cart" type of feature, it is actually a >favorites feature for a system that displays images in multiple >galleries. There is a SQLite table that contains the user_id, >gallery_id, and image_id. When a index page is displayed, only a sub >set of the images in the gallery are displayed. So the question is >what will be faster: > >1: Doing a SELECT for each image on the favorites table to see if it is >selected >2: Doing one SELECT to get all the images for the current gallery and >store that into a PHP array and then simply look in the PHP for each >image? > >My thought is option 2. Is that correct? Yes. >Sam Since a gallery usually has more pictures than will fit on one index page, and you probably want the users to be able to jump to the next page, this page might be of interest for you: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
Michael Hooker wrote: > This is an issue which interests me too. The answer Scott gives makes > absolute sense to me, but all the PHP/MySQL books I've seen (and MySQL can't > be that different from Sqlite3 in this respect) seem to go the way of a new > query to the database every time a different set of data is needed, rather > than keeping all the data in an array, even with small inconsequential > tables. I suppose if there is a possibility that the db tables may be > updated while the user is online this approach is valid. Or maybe there is > a downside to PHP arrays, I can see that a huge array might have memory > implications, especially on a busy shared server. My experience has been that each sqlite DB hit on my server takes about .01 seconds to run. So if you're hitting that DB 20 times to get the 20 most popular images that really adds up. I've spent countless hours optimizing my site to do things like: SELECT * FROM Entry WHERE ID IN (1,2,3,4); instead of SELECT * FROM Entry WHERE ID = 1; SELECT * FROM Entry WHERE ID = 2; SELECT * FROM Entry WHERE ID = 3; SELECT * FROM Entry WHERE ID = 4; If you can (sometimes its hard programatically) reducing the number of database hits will always speed up your application. Unless of course the data you're loading in RAM is huge. When you're storing megs of data in ram just to speed up your queries you probably should look at other routes for optimization. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
This is an issue which interests me too. The answer Scott gives makes absolute sense to me, but all the PHP/MySQL books I've seen (and MySQL can't be that different from Sqlite3 in this respect) seem to go the way of a new query to the database every time a different set of data is needed, rather than keeping all the data in an array, even with small inconsequential tables. I suppose if there is a possibility that the db tables may be updated while the user is online this approach is valid. Or maybe there is a downside to PHP arrays, I can see that a huge array might have memory implications, especially on a busy shared server. Michael Hooker - Original Message - From: "Scott Baker" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Monday, February 18, 2008 4:33 PM Subject: Re: [sqlite] which is faster, PHP or SQLite? > Sam Carleton wrote: >> I am new to SQLite and databases, so I am stil learning how to >> optimize their use... >> >> I am working on a "shopping cart" type of feature, it is actually a >> favorites feature for a system that displays images in multiple >> galleries. There is a SQLite table that contains the user_id, >> gallery_id, and image_id. When a index page is displayed, only a sub >> set of the images in the gallery are displayed. So the question is >> what will be faster: >> >> 1: Doing a SELECT for each image on the favorites table to see if it is >> selected >> 2: Doing one SELECT to get all the images for the current gallery and >> store that into a PHP array and then simply look in the PHP for each >> image? >> >> My thought is option 2. Is that correct? > > The less database hits you have to do, the faster your code will be. > Getting all the data into a PHP data structure should be the way to go. > > > -- > Scott Baker - Canby Telcom > RHCE - System Administrator - 503.266.8253 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
At 16:25 18/02/2008, you wrote: >I am new to SQLite and databases, so I am stil learning how to >optimize their use... > >I am working on a "shopping cart" type of feature, it is actually a >favorites feature for a system that displays images in multiple >galleries. There is a SQLite table that contains the user_id, >gallery_id, and image_id. When a index page is displayed, only a sub >set of the images in the gallery are displayed. So the question is >what will be faster: > >1: Doing a SELECT for each image on the favorites table to see if it >is selected >2: Doing one SELECT to get all the images for the current gallery and >store that into a PHP array and then simply look in the PHP for each >image? > >My thought is option 2. Is that correct? My thought is that it would depend. I'd guess that If you have 100 images, and you are wanting to show 20, then (2) may be quicker, but if you have 1,000,000 images, then (1) would be quicker. (Assuming you have a usable index on the table). Leaving aside possible database design considerations, it's generally best to let the database engine do the work if it can. With SQLite, I'd qualify that to say that it's best to let SQLite do the work if the queries are simple enough that its optimiser will use indices to do the work. We have found that it can be quicker to do things partially in SQLite and partially in C++. When SQLite would have to do a sequential scan to get the result, it can be quicker to do, say, two indexed scans in SQLite and then operate on the two result sets (eg doing a union or intersect) to produce the final result set, but this is the exception rather than the rule (for us anyway). Paul Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] which is faster, PHP or SQLite?
Sam Carleton wrote: > I am new to SQLite and databases, so I am stil learning how to > optimize their use... > > I am working on a "shopping cart" type of feature, it is actually a > favorites feature for a system that displays images in multiple > galleries. There is a SQLite table that contains the user_id, > gallery_id, and image_id. When a index page is displayed, only a sub > set of the images in the gallery are displayed. So the question is > what will be faster: > > 1: Doing a SELECT for each image on the favorites table to see if it is > selected > 2: Doing one SELECT to get all the images for the current gallery and > store that into a PHP array and then simply look in the PHP for each > image? > > My thought is option 2. Is that correct? The less database hits you have to do, the faster your code will be. Getting all the data into a PHP data structure should be the way to go. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] which is faster, PHP or SQLite?
I am new to SQLite and databases, so I am stil learning how to optimize their use... I am working on a "shopping cart" type of feature, it is actually a favorites feature for a system that displays images in multiple galleries. There is a SQLite table that contains the user_id, gallery_id, and image_id. When a index page is displayed, only a sub set of the images in the gallery are displayed. So the question is what will be faster: 1: Doing a SELECT for each image on the favorites table to see if it is selected 2: Doing one SELECT to get all the images for the current gallery and store that into a PHP array and then simply look in the PHP for each image? My thought is option 2. Is that correct? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
