PHP和数据库：视图，函数和存储过程的性能 [英] PHP and Databases: Views, Functions and Stored Procedures performance

问题描述

我正在使用PostgreSQL的PHP​​ Web应用程序。所有的SQL查询都是从PHP代码调用的。我看不到任何视图，功能或存储过程。从我学到的知识，使用这些数据库子例程总是更好，因为它们存储在数据库中，具有以下优点：

• 封装


• 抽象


• 访问权限（限于DB管理员）和责任


• 避免编译

我想我也读了一些关于性能改进的内容。我真的不明白为什么团队还没有使用这些。在这种特殊情况下，我想知道，从经验来看，有没有什么好的理由不使用它们？

主要是当有这么多SELECT查询代码，为什么不使用视图？

我正在计划重构代码，并开始编写DB服务器上的子例程。我想知道赞成或反对的意见。该项目相当大（许多表），并期望存储大量数据。你在一个社交网络中拥有的数据量会比较多，所以是的，这个数字很大。

解决方案

。视图提供封装，但如果不仔细设计，它们可能会减慢应用程序的速度。谨慎使用。

II。如果需要，使用功能，没有理由将它们放在不需要的位置。

III。存储过程是一个godsend，使用它们到处有静态查询!!

为了响应视图与查询，尝试使用存储过程的视图，存储程序将减轻使用 大多数 视图所拍摄的一些性能。

I'm working on a PHP web application with PostgreSQL. All of the SQL queries are being called from the PHP code. I've seen no Views, Functions or Stored Procedures. From what I've learned, it's always better to use these database subroutines since they are stored in the database with the advantages:

• Encapsulation
• Abstraction
• Access rights (limited to DB Admin) and responsibility
• Avoid compilation

I think I read something about performance improvements too. I really don't see why the team hasn't used these yet. In this particular case, I would like to know, from experience, is there any good reason to NOT use them?

Mostly when there are so many "SELECT" queries along the code, why not use Views?

I am planning on refactoring the code and start coding the subroutines on the DB Server. I would like to know opinions in favor or against this. The project is rather big (many tables), and expects lots of data to be stored. The amount of data you would have in a social network with some more stuff in it, so yeah, pretty big.

解决方案

I. Views offer encapsulation, but if not carefully designed they can slow down the application. Use with caution.
II. Use functions if needed, no reason to put them in if they are unneeded.
III. Stored Procedures are a godsend, use them everywhere there is a static query!!

In response to the views vs. queries, try to use views with Stored Procedure's, the Stored Procedure's will mitigate some of the performance hit taken with most views.

这篇关于PHP和数据库：视图，函数和存储过程的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！