Flat file or SQL? - constant values

[Jimmyborofan]

So I have a bit of a problem,

The application I am working is (atm) very SQL hungry , it is procedural and has no framework per say.

Each page at the moment is created dependant upon a query string and the page 'content' is in actual fact an iframe(Please dont make issues arounf this, the future version is a re-write for full OOP and server efficiency) but this issue will be a future problem too

I want to be able to link a help page (from an outside url) to the current page.

Now I really am looking for efficiency here and this is why I am umming and ahhring

The link can be looked up using a flat file, SQL or hardcoded into each page, the last option is out as it means a lot of extra work, so I am looking to a Flat file look up or a SQL query.

This application has thousands of page hits each day so even a small SQL query will have larger implications later on.

But would a flat file look up be any more efficient because relative to the application the number of help files is around 35, where as the number of pages is dynamic and is built using various methods.

So My question is this, if a server building a page adds the extra data to build the link, would it be more or less efficient than using a flat file look up to index the help file to a particular page.

Please also remember that if a page needs a help file it has to calculate this, so the page table would have the help file flag and the info to build the link. whereas a flat file would be a $key=>$value set up using the query string data to retrieve the link data.

All help appreciated

[Fou-Lu]

Of options, filesystem will always be better than a dbms. But, you are indicating a lookup style functionality (key => value pairs), so this to me warrants the use of a db. If you had a file for each option available without the need for a parse and store, then the filesystem will outperform for sure. But when you need to lookup something, search, limit, etc, than the DB is typically a better option.

Are you issuing thousands of queries due to the traffic itself, or is it due to the script executing excess queries? If its the latter, you should look at why its doing this. Set the goal to 1 query per script, and run from there. I have yet to be able to write a piece of software with only 1 query per script load in PHP, but I have managed to bring down the queries in a fully functional application to 3. 3 per load is pretty good; I personally wouldn't be too worried until you are seeing in the 20ish per page load (IMO its still too high, but I wouldn't be concerned). Also note that queries can be limited to the number of queries issued per hour per user. I don't see it set very often, but I recall seeing it once set at 20,000/h.

[Jimmyborofan]

Thanks for that its pretty much what I expected. The site is a high traffic site, not because of excess queries. I proposed various alternate solutions to using SQL, as I was requested. I basically created a quick test to time a looped query. file read, and by using variables already present in the page. Showing that the server load on a query by extending the query to include an extra two columns was not really all that much...

Thanks for the reply though it kinda confirmed my own thoughts.

[MarkR]

Are you using any form of local caching? There is no reason why common queries can't be cached for a few hours if results are unlikely to change for a long duration of time.