Direct queries vs. stored procedures

xenon · 12-13-2007, 05:18 PM

What would you choose? I go with direct queries. Simpler, faster, portable. What more would I want?

ReSpawN · 12-13-2007, 05:46 PM

Depending on the resultsize, direct queries. If you're getting, lets say a log, it's also easy. But then again, free_result your query since it slows your scripts/site.

Wildhoney · 12-13-2007, 05:53 PM

Hmm, I did try stored procedures at one time but PHP was having a lot of issues with the server "going away" as it liked to put it. I thought it was quite humorous anyway. That was just less than a year ago though so it's probably all fixed now, but it was logged as a PHP bug on the PHP bug website. I then went back to direct queries and never looked forward since.

I guess I should really! As for the vote though, I think I'm going to have to go with stored procedures

! I just don't practice what I preach.

Salathe · 12-13-2007, 07:26 PM

Ideally stored procedures would be my choice since I like to keep DB related things within the DB. With PHP and MySQL however, I'm still somewhat behind the times because I just don't use stored procedures with that combo. I'd also argue against 'direct queries' being more "simple, faster and portable" than stored procedures (in general terms). I'm not quite sure how xenon thinks otherwise, maybe he can tell us.

xenon · 12-13-2007, 10:51 PM

Well, since sql queries and php code go in the same place, you can move your code more freely (without having to worry about moving the procedures aswell). Also, it's faster to write a query than a SP, and I consider wrong writing a SP just for the fun of it (replacing queries with SPs, that is). Of course, if I was to make something more advanced, which strictly required operating a database table or more, I would go with SPs and only capture the result with PHP. But I just don't see the usefullness of SPs in plain queries ("select a from b") or simple operations (like a join, for example). I actually saw people who wrote a SP for everything.

I'm strictly talking about PHP and MySQL here.

ReSpawN · 12-13-2007, 11:09 PM

Quote:

Originally Posted by xenon

Well, since sql queries and php code go in the same place, you can move your code more freely (without having to worry about moving the procedures aswell). Also, it's faster to write a query than a SP, and I consider wrong writing a SP just for the fun of it (replacing queries with SPs, that is). Of course, if I was to make something more advanced, which strictly required operating a database table or more, I would go with SPs and only capture the result with PHP. But I just don't see the usefullness of SPs in plain queries ("select a from b") or simple operations (like a join, for example). I actually saw people who wrote a SP for everything.

I'm strictly talking about PHP and MySQL here.

I think I understand where you're going with this. And the way Salathe described it, he's thinking about the in-depth database layout. This is simply for getting the queries.

CoryMathews · 12-14-2007, 02:21 PM

well if you are doing the same query over and over again you should use a sp. since then its already compiled and will run quicker.. but for most querys that you do only one time its not really worth writting the sp.

Erutan409 · 03-31-2008, 02:48 PM

I'm using numerous stored procedures/functions. I need them especially for the project I'm working where I work. I have a database server, MySQL, OPC server, which is integrated into our production floor, Visual Basic connector that I'm writing, which links OPC data to my database server, and PHP as a GUI. The VB connector is doing a lot of work. It's looking for updates in the OPC server and saving/sending data to/from the database. I use a lot of stored procedures in my VB app. If I didn't the VB connector would be doing a lot more work than I need it to. Right now I have 1200 lines of code for my stored procedures and functions and I expect it to keep growing.