CodeIgniter Forums
Active Records Performance vs Raw Queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Best Practices (https://forum.codeigniter.com/forum-12.html)
+--- Thread: Active Records Performance vs Raw Queries (/thread-64265.html)

Pages: 1 2


Active Records Performance vs Raw Queries - pb.sajjad - 02-01-2016

Hi to all developers...
I'm using CI v3. I have a question about query builders, in fact, active records. Using active records is really a bad approach? I googled about this and some developers says codeigniter active record is not efficient, its performance and memory usage is so bad or something like these...! I'm so confused about this! What should I do? Should I use raw queries? Should I use active records? what about performace?
please guide me if you are expert...


RE: Active Records Performance vs Raw Queries - skunkbad - 02-01-2016

I think CI's query builder is pretty fast, and certainly not much different than using plain PHP. There are a lot of benefits when using the query builder. Read the documentation and dig around in the code to see for yourself.

One thing is for sure is that PHP itself has a pretty slow connection to MySQL. For instance, I had a batch of inserts that each included many megabytes of data, and PHP couldn't handle it. Running the batch through bash (linux terminal) was completed almost instantly. Seriously, I thought there must be an error because the bash script ran so fast, but there wasn't one.

The truth is that CI's query builder is super easy to use, and you'll see that it's plenty fast for most queries. It's more likely that you would design your application poorly, contributing to the overall inefficiency, rather than CI causing issues for you.

Take for example WordPress, which is extremely slow. If you look at the MySQL query log, even the most basic of pages causes almost 30 queries to run. Compare that to a well designed application where queries are optimized, and database is normalized, and you will not likely have any reason to complain.


RE: Active Records Performance vs Raw Queries - albertleao - 02-01-2016

From what I've seen, the performance loss is minimal unless you're running systems where every .001 seconds counts.

Orms usually take more heat for being slow. While I personally think that most people who claim ORM's are slow are people who don't optimize queries within the orm itself, raw php sql queries will always be quicker.

Unless you're trying to squeeze every microsecond of performance out of your app and you've already combed through every other piece of code to speed things up, you should be fine using active record. I wouldn't switch away from active record for performance unless I've deeply optimized my front end, my back end code, my server OS, and other PHP configs.


RE: Active Records Performance vs Raw Queries - ivantcholakov - 02-02-2016

The pure CodeIgniter has no ORM-implementation, neither "Active Record", nor "Datamapper" kinds.

A query builder is what CodeIgniter uses, the former name "active record" was not precise. And this query builder simply helps in generation of SQL statements and properly escaping inside them. It is quite fast, the performance impact is negligent. Use it with preference over the raw SQL, the syntax is beautiful and expressive, and the chance of you write some insecure queries gets lower. Even it gives you some abstraction of the SQL-server type, although 100% is not the goal.

The CodeIgniter's query builder has nothing to do with the "To ORM or not?" debates, it is not a ORM-kind of feature.


RE: Active Records Performance vs Raw Queries - Narf - 02-02-2016

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".


RE: Active Records Performance vs Raw Queries - ivantcholakov - 02-02-2016

@Narf

No, it is not just "nice". I've seen tons of unescaped raw SQL, I knew people that just did not care about that. The query builder at least gives escaping automatically, this is important.


RE: Active Records Performance vs Raw Queries - PaulD - 02-02-2016

For someone like me, a self taught coder, SQL has always been a hurdle. I am sure if I dedicated some time (probably quite a bit) I could get good with it, but I don't really want to in all honesty. I have built some fairly complex sites to date including e-commerce, community and download sites, but have rarely had to delve into SQL directly. The query builder lets me build relatively complex queries with only a very basic working knowledge of SQL.

So personally, I love the CI query builder. For me it is one of the really great core aspects of CI.

But I suppose that just puts me firmly in the camp of "it's nice", but 'nice' doesn't seem to give it the importance I think it deserves.

Paul.

PS I have never used anything other than MySql so this opinion is nothing more than a non-expert's opinion with very limited knowledge and experience.


RE: Active Records Performance vs Raw Queries - Narf - 02-02-2016

(02-02-2016, 05:42 AM)ivantcholakov Wrote: @Narf

No, it is not just "nice". I've seen tons of unescaped raw SQL, I knew people that just did not care about that. The query builder at least gives escaping automatically, this is important.

Yea, and that's "nice" in the same sense that IDEs will do auto-completion for you, while plain-text editors won't. It's not a fair comparison to assume that you won't do escaping if you write raw SQL.
That you actually need to know what you're doing doesn't make one solution better than the other.

(02-02-2016, 05:52 AM)PaulD Wrote: For someone like me, a self taught coder, SQL has always been a hurdle. I am sure if I dedicated some time (probably quite a bit) I could get good with it, but I don't really want to in all honesty. I have built some fairly complex sites to date including e-commerce, community and download sites, but have rarely had to delve into SQL directly. The query builder lets me build relatively complex queries with only a very basic working knowledge of SQL.

So personally, I love the CI query builder. For me it is one of the really great core aspects of CI.

But I suppose that just puts me firmly in the camp of "it's nice", but 'nice' doesn't seem to give it the importance I think it deserves.

Paul.

PS I have never used anything other than MySql so this opinion is nothing more than a non-expert's opinion with very limited knowledge and experience.

All QB methods (in CodeIgniter) are named and designed after SQL keywords, so if you know how to use the query builder, you should know how to write the equivalent SQL code.


RE: Active Records Performance vs Raw Queries - ivantcholakov - 02-02-2016

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.


RE: Active Records Performance vs Raw Queries - Narf - 02-02-2016

No, it doesn't. Smile

Not for anybody above the "junior developer" level. And if you'll be paying juniors to do a job for you, well ... you know you probably wouldn't be happy with the result anyway.