Welcome Guest, Not a member yet? Register   Sign In
Active Records Performance vs Raw Queries
#11

(02-02-2016, 07:28 AM)ivantcholakov Wrote: If someone's work contains more than 5% raw SQL I will not accept it (and pay). The query builder adds value to security, this is important. This is my period on the subject.

I'm worked on some projects that required some pretty intricate SQL to be written. To try and do all of that was a pain in query builder. However, skipping the Query Builder and using the parameter binding property of $this->db->query() still handles auto-escaping the data for you. I would argue that might be something you should consider when drawing that line in the sand about Query Builder. And, yes, I do use QB a lot, also, just not for the overly complex queries with sub-queries, etc.
Reply
#12

Um ... there's also one thing we're all kind of missing here - the question is about performance and nothing else.
Reply
#13

(02-02-2016, 03:18 PM)Narf Wrote: Um ... there's also one thing we're all kind of missing here - the question is about performance and nothing else.

Good point. SQL always faster. Smile
Reply
#14

(02-02-2016, 05:16 AM)Narf Wrote: Should your application be able to run on different databases? Or do you imagine having to migrate your application to another database, any time in the future?

If yes - a query builder will be of great help.
If no - raw SQL is always better, period.

Any other arguments are just people thinking it's "nice".

For now, the answer is no. but may be yes for the future, I'm not sure now.

over all, I developed my app (about 80% of it is completed). Should I come back to first and rewrite all written queries with query builder, and using raw sql? how much the performance is different between these two approach? if I user query builders, is there situation that I encounter to lack of memory or so slow execution?

All of developers here and out of here say opinions that I couldn't be sure to choose a way. If there is a logical answer to these question, why answers are different?!
Reply
#15

I'm never encounter lack of memory or so slow execution when using CI. Query builder is very useful and save a lot of time.
Keep calm.
Reply
#16

You cannot answer a question if raw SQL or Querybuilder is generally better. It is a trade off. Querybuilder adds some nice features at the costs of some speed. Raw SQL is always faster but you will not have some of the benefits of Querybuilder.

You will have to decide what you define as "better". Is that for example the fastest method of executing a query then raw SQL is the better one. If you like some of the features in Querybuilder or want your app to be more interoperable, like being able to migrate to another database like Narf mentioned, without much headache then Querybuilder is better.

I prefer to use Quirybuilder. I believe there is room for improvement in every application in some query, database structure optimization or a php loop that will way more impact than switching from Querybuilder to raw SQL. If you run into performance issues you need to solve the issue itself, don't expact that switching from Querybuilder to raw SQL is some miricle solution Wink

Does anybody have some benchmarks data which shows some difference in speed between raw SQL vs Quirybuilder?
Reply
#17

(02-02-2016, 04:42 PM)pb.sajjad Wrote:
(02-02-2016, 05:16 AM)Narf Wrote: Should your application be able to run on different databases? Or do you imagine having to migrate your application to another database, any time in the future?

If yes - a query builder will be of great help.
If no - raw SQL is always better, period.

Any other arguments are just people thinking it's "nice".

For now, the answer is no. but may be yes for the future, I'm not sure now.

over all, I developed my app (about 80% of it is completed). Should I come back to first and rewrite all written queries with query builder, and using raw sql? how much the performance is different between these two approach? if I user query builders, is there situation that I encounter to lack of memory or so slow execution?

All of developers here and out of here say opinions that I couldn't be sure to choose a way. If there is a logical answer to these question, why answers are different?!

Because there are people who care about performance and people who don't. Smile

The performance penalty itself is neglible in most cases, you wouldn't notice it. Chances are that if you're asking this question, you're worrying way too much about a problem that doesn't really exist.
Reply
#18

(This post was last modified: 02-03-2016, 08:47 AM by sv3tli0.)

This sounds the same as Template Engines vs Plain PHP (why to use Temp.Eng. when it finally generates and run plain php)..

Same is and with Plain SQL vs QB .. At the end you always have execution of plain SQL query.
Its normal that if you write it directly it will be faster than building it with some PHP classes.

For easier managing of the application I think that its better to use QB, but when there is some specific need (usually complicated queries) plain query is always an option.
Best VPS Hosting : Digital Ocean
Reply
#19

Honestly, the QB class doesn't do enough to actually have a significant impact on performance unless you're in a situation that requires extreme performance. Anyone could write poorly-performing SQL using either approach.

If you need to generate your SQL in your application based on a number of conditions, it's possible that your method of building the SQL could end up being less efficient (or more error-prone) than it would have been using Query Builder. Alternatively, you could build something more efficient and less error-prone for your particular situation, simply because Query Builder has to be more flexible than your own solution.

Portability between database engines is probably going to have more to do with your database design and how you generate your tables than the majority of the SQL statements in your code. Query Builder can only do so much to prevent you from having to rewrite some of your SQL if you have to change database engines.

As with any question about performance, the answer is completely dependent on your situation. In most cases, the best thing to do is to take the approach that will cost you the least amount of time in development (with an eye towards being able to change things if necessary), then worry about performance when you can actually measure performance and determine what (if anything) is causing a problem. I've never run into a situation in which XDebug's profiler showed me that moving from Query Builder to a hand-built query would be the best thing I could do to improve performance.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB