ordinaryToucan Posted July 3, 2008 Share Posted July 3, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/ Share on other sites More sharing options...
Daniel0 Posted July 4, 2008 Share Posted July 4, 2008 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). Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581541 Share on other sites More sharing options...
corbin Posted July 4, 2008 Share Posted July 4, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581568 Share on other sites More sharing options...
keeB Posted July 4, 2008 Share Posted July 4, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581756 Share on other sites More sharing options...
ordinaryToucan Posted July 4, 2008 Author Share Posted July 4, 2008 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! Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581777 Share on other sites More sharing options...
corbin Posted July 4, 2008 Share Posted July 4, 2008 What kind of hosting are you on? Shared? VPS? Guessing shared. Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581807 Share on other sites More sharing options...
ordinaryToucan Posted July 4, 2008 Author Share Posted July 4, 2008 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! Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581830 Share on other sites More sharing options...
corbin Posted July 4, 2008 Share Posted July 4, 2008 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.... Quote Link to comment https://forums.phpfreaks.com/topic/113057-most-effecient-practice-for-database-interaction/#findComment-581861 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.