Most Effecient Practice For Database Interaction

[ordinaryToucan]

Hi There,

 

I'm developing for a web site that requires multiple bits of data from the database for each outputted page (eg. site settings parameters; module settings; page content; sidebar content; etc.).

 

At the moment my script creates a new "database query" object for each query, which in itself creates a connection to the database, sends the query, and parses the results. That means that a new connection is created and closed for each query...

 

Now I am wondering is it better to

1) Open and close connection for each query within the script;

2) Open a pconnect for each query within the script;

2) Open a connection at the top of the script and close it at the end of the script;

 

I seem to be able to find arguments for and against each method, but I am wondering which is likely to be the most efficient in terms of maximum number of simultaneous outputted pages. This site this is for is usually quiet but occasionally gets large numbers (thousands or tens of thousands) of hits in a few minutes.

 

The setup is PHP running as an Apache2 Module, with the database on the same box. I'd be very grateful for any insights.

[Daniel0]

Definitely not open and close all the time. That will just result in unnecessary overhead and if you sometimes get tens of thousands of hits in few minutes then your performance must be top-notch. You should only connect once per script execution (unless you need simultaneous access to a secondary database server of course).

[corbin]

My first advice would be to cache anything that seems logical to cache.

 

 

As to connecting:

 

Do not connect each query!  What would be the point in that?  That would be like reintroducing your self everytime you said something to a certain person.

 

Like Daniel said, I would suggest just having 1 connection open to the database.

 

You could use a persistent connection, but depending on the database driver that you're using, it might be being handled silently.

[keeB]

Sound advice here.

 

Just make a db.inc and include it in whichever page you need a database connection. Then, you can just call say

 

$result = mysql_query("SELECT * FROM LOLO;", $db /* this is the resource created in db.inc */);

 

Cheers

[ordinaryToucan]

Thanks guys, that's fantastic advice and really appreciated. I have reworked my app as suggested, to make a single connection on the first database query which seems to stay open until the end of the script - the execution time on each page is quicker and the code is neater too!

 

I've never actually worked with caching data before, but I think it's a valuable suggestion. I have a quick question about the best way to approach the security aspect of caching...?

 

Site settings, for example, I guess could be written to a file whenever they are changed, and then called from the file (rather than the database). But given that I don't seem to be able to write to any directory that doesn't have 777 permissions, what's to stop some ner'do'well grabbing the file and making a mess of it (or worse)?

 

The particular server that the site will be on is not administered by me, so I don't have any choice over open base dir and safe mode settings.

 

Again, any insight from those with experience is very gratefully received!

[corbin]

What kind of hosting are you on?  Shared?  VPS?  Guessing shared.

[ordinaryToucan]

No, it's on a dedicated server, but the client has a paranoid system admin who won't allow me anything more than ftp access!

 

Plus the setup on their server is a mess, and I don't want to do anything that causes their installation of Plesk to fall over!

[corbin]

If it's a dedicated server, I would just make the folder writable and not worry about it.  If it were shared hosting, you would have to worry about other people getting into your folder and what not....