SQL,SP and ORM

SQL is translated as per-case basis,sp means a stored procedure, an ORM is an object-relational map, such as Hibernate One, Evolution
At first, only SQL statements, which can be executed in an interactive sentence, can also be executed in batch mode, and multiple rows of SQL statements are committed at a time.
It was soon discovered that a bunch of SQL languages executed in batch mode could be stored in the database in the form of a process, which became a storage procedure.
As the object-oriented technology matures, SQL statements can be generated automatically from the program, which is the ORM

Two, Performance
If we send a bunch of SQL to the server one at a time, then the server caches the heap of SQL, and the next time it executes again, it's like invoking an "anonymous" stored procedure. In this case, the performance is similar.
However, if we do not pay attention, it is very likely that the SQL can be committed once, into multiple commits, or even a single commit per loop, then the performance is very poor, that is, if you use SQL, as long as the correct wording, performance and SP difference is not small.
Similarly, the performance of ORM depends on the ORM SQL generation algorithm, and user use, the control of the generation algorithm, such as the use of good lazy laoding, etc., in some cases, even can not pass SQL, after all, no SQL than the most optimized SQL is faster.

Third, maintainability
Maintainability is the most important factor in the selection of Sql,sp,orm.
At first, the maintainability of SQL seems to be the worst because it is often scattered around every corner of the program. And the memory is placed in the database, there is a clear interface.
But if we do a refactoring, the situation will turn upside down.
First of all, stored procedures can be copied into C #, SP's name directly into the name of method, SP parameter table directly into the method parameter table, (in fact, Command mode).
Next, put these methdod in a file or folder. (The so-called DAL layer, if you like layers)
Through this refactoring, we gain the following benefits,

1, the first is the process of the call and the definition of the process is put together, it is more convenient to modify. The IDE has a defined jump function.
2, the invocation and definition of the procedure are versioned at the same time, no mismatch occurs. Reduced SP parameter table and call mismatch, including spelling, type, parameter order
3, unit test is very convenient

Of course, SP also has the value of existence, such as the so-called security, will be mentioned later. such as a friendly debugging environment, for small and medium-sized projects, and junior programmers, is also a good choice.
ORM adds maintainability to a new level, trying to shield SQL and automatically run the database at the same time as the object is manipulated.
ORM has two modes, one is ActiveRecord, one is Datamapper, the former is read from the database, the latter is defined in the program. However, because the former often use migration to generate the database, in fact, is also defined in the program inside. Good ORM has a "leaking" design, that is, left a "backdoor", so you have the opportunity to use SQL to control.
Microsoft's LINQ, from an angle class, is an ORM, probably because it feels that writing SQL statements is more efficient than writing C # code, so it provides a mechanism to write SQL statements directly in C #, and then automatically generates real SQL. The real value of ORM, however, is that it can completely abandon SQL at the right time, such as reading the cache and writing a queue. and Microsoft's LINQ, is completely "unreasonable" style, in O in the wording of R, is the RRM, the only advantage is to lock the program and the programmer on the Microsoft platform.

Third, security

For enterprises, security is sometimes more important than performance, and because stored procedures add a barrier to the database, many enterprises take the stored procedure as their first choice.
ORM can be said to be the least secure, because only when the program is running can you know what kind of SQL it will produce.
However, there are many ways and means to ensure security, such as pre-deployment tests, database backups, and the setting of permissions on tables. such as Use SP to ensure security, just one of several options.
In startup enterprises, high-level programmers often play a leading role, so they will not hesitate to choose ORM.
In a traditional enterprise, an SP is often used if a DBA or a technical director is more powerful.

Sum up:

	Orm	Sql
Development speed	Fast	Slow
Operating performance	Slow	Fast
Easy to modify
Maintainability	High	Low

ORM, the biggest problem is complex query, can consider both SQL and ORM use simultaneously, simple place with ORM, complex place with SQL, using technology such as cache can improve the query speed, in Phalcon, is to use both the way:

Returned in 1.Model is the current model set 2.PHQL is returned in the query set the former support magic method Get/count with the associated model name, with optional parameters to do two filter; the latter uses a SQL-like method to correlate tables, This makes it possible to access all fields using the Hump rule in the query set: $PHQL = "Select Cars.*, brands.* from Cars, Brands WHERE brands.id = cars.brands_id" $rows = $mana    Ger->executequery ($PHQL); foreach ($rows as $row) {echo "Car:", $row->cars->name, "\ n"; echo "Brand:", $row->brands->name, "\ n";} You can also access the specified field: $phql = "Select Cars.name as Car_name, brands.name as brand_name where brands.id = cars.brands_id"; $rows = $mana    Ger->executequery ($PHQL); foreach ($rows as $row) {echo $row->car_name, "\ n"; echo $row->brand_name, "\ n";} Of course you can add alias access: $PHQL = "Select C.*, b.* from Cars C, Brands b WHERE b.id = c.brands_id"; $rows = $manager->executequery ($phql    ), foreach ($rows as $row) {echo "Car:", $row->c->name, "\ n"; echo "Brand:", $row->b->name, "\ n";}

SQL,SP and ORM