During development, if both programming languages (such as PHP) and SQL (such as MySQL) can implement a certain function, do we use programming languages or SQL? Or is the problem analyzed?

Source: Internet
Author: User
Php Chinese network (www.php.cn) provides the most comprehensive basic tutorial on programming technology, introducing HTML, CSS, Javascript, Python, Java, Ruby, C, PHP, basic knowledge of MySQL and other programming languages. At the same time, this site also provides a large number of online instances, through which you can better learn programming... Reply content: My principle is: to put all the data in PHP, DB (such as MySQL) only put data, not logic (foreign key constraints, stored procedures, etc ).

Use MySQL, but remember which database can be changed at any time. in particular, do not use logic-related features supported by MySQL. Specific analysis of specific problems the most common performance bottleneck is at the database layer. Although horizontal scaling of relational databases is developing, there is no widely accepted stability solution yet, so NoSQL has developed so fast in recent years. The horizontal expansion of application server is much more mature. after adding application server, you only need to add a new machine to the server load balancer list on the web server.

Since database horizontal scaling is much harder than application server horizontal scaling, should we try to put the logic in the code rather than SQL to minimize the pressure on the database? For example:
For example, the number of visits per user per hour is recorded in tbl_hour. for example, the table of tbl_hour has three columns: username, hour, and visit_couter.

At every day, you need to add the number of daily visits of each user in the previous day to generate the total number of daily visits of each user in the previous day, such as tbl_day. The table has three columns: username, dateName, visit_counter.

You can use a program or SQL to calculate the total number of daily visits from the previous day:

1. extract all the data FROM the previous day (SELECT * FROM tbl_hour WHERE hour betwen LAST_DAY_START AND LAST_DAY_END) AND calculate the number of workers for each user through a program;

2. calculate the number of records of each user in the previous day using SQL (SELECT username, Sum (visit_counter)FROM tbl_hour WHERE hour betwen LAST_DAY_START AND LAST_DAY_END Group by username )

Which one do you choose?
Let me choose, if tbl_hour's data volume on the previous day is particularly large, for example, millions or more, choose computing by myself. if the magnitude is not too large, directly let SQL computing, it will save some trouble.

How did you choose? Listen to your suggestions. There are three situations in which the logic is put in the program.
The first one is relatively weak. it is not recommended to use the programming language system to call command line commands. for example, db2 only uses c APIs for load, and does not implement load modules in python or perl. Therefore, you can only use command lines.
The second is to use the database module of the language to operate the database. in essence, computing is stored in the database. it only encapsulates the logic in the program. there is no difference in efficiency from stored procedures, but because stored procedures certainly have no strong language and many features, they are generally better than stored procedures.
The third is to extract the database data and use the language to process it in the memory. the computing pressure is on the machine running the program. it is only suitable for business computing with small data volumes. this cannot be used if several large tables are involved.

In the development process, the use of programs is certainly better than the use of stored procedures. the various version management of programs is much more mature than the storage process.

In addition, if c and other compiled languages are used, and the logic at some point is frequently changed, some of them are actually stored in the stored procedure. let's just say a little bit:
Business logic, if encapsulated in a stored procedure, saves the release time of the program, you only need to change the SP, and then update the database. The program does not need to be modified, which is much simpler.
Especially for many compiled languages, every release is disgusting ~~~~~~ I personally feel that code maintenance is too good for database maintenance.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.