STORED PROCEDURE是在大型数据库中使用的最佳方式吗？ [英] Is STORED PROCEDURE the best way to use in large database?

问题描述

STORED PROCEDURE是在大型数据库中使用的最佳方式还是在应用程序/编码中使用的简单程序（在编码中查询）？



哪个程序是最好的使用C＃（桌面应用程序）插入，获取，删除，更新大型数据库的记录（CURD操作）。



对不起愚蠢的问题，我我是一个初学者并且学习这就是为什么混淆了。



我尝试过：



我使用简单程序（编码中的查询）

Is STORED PROCEDURE the best way to use in large database or the simple procedure using in application/coding (Queries in Coding)?

Which procedure is the best for inserting, fetching, deleting, updating record(s) (CURD Operations) for a large database, using C# (Desktop Application).

Sorry for silly question, I am a beginner and learning that's why confused.

What I have tried:

I am using Simple procedure (Queries in Coding)

推荐答案

它不一定是只是大型数据库，存储过程也有小优势。

看看这里：优势和使用存储过程处理数据的缺点 [ ^ ] - 根据程序正在进行的操作，执行操作的频率以及许多其他因素进行操作！



就个人而言，我在适当时使用 - 这意味着当我做一些复杂的事情而不是简单的SELECT或INSERT语句时 - 因为他们制作代码更具可读性和可维护性，有时还可以灵活地更改架构，而无需更改访问数据库的应用程序。

It's not necessarily "just" large databases, stored procedures have advantages in small ones as well.
Have a look here: Advantages and Drawbacks of Using Stored Procedures for Processing Data[^] - it's goign to depened on what the procedure is doing, how often it does it, and a host of other factors!

Personally, I use them when appropriate - which means when I'm doing something complex rather than a simple SELECT or INSERT statement - because they make code more readable and maintainable as well as sometimes giving you the flexibility to change the schema without altering the applications accessing the DB.

我不会说数据库的大小应该是驱动强制使用或不使用程序。毕竟有人认为很大的数据库可能很小。



影响程序使用的因素包括：

• 通过程序消除往返，您可以消除从数据库来回传输数据的需要。这意味着如果整个操作在数据库端完成，单个工作单元可能会表现得更好
• 安全性，您可以使用过程阻止用户（或应用程序）查看或修改基础表控制访问路线，程序
• 可维护性，将程序视为界面。例如，您可以更改基础数据结构并仍然向调用方提供相同的结果
• 动态查询，创建和处理动态查询通常在程序内部更加不方便和困难
• 用户对话，在事务中不允许用户对话，因此在设计体系结构时需要考虑这一点。
• 多个调用者，如果您的环境有多个应用程序，则可能更容易在内部实现逻辑程序并从几个地方调用它，而不是在主叫方的多个地方实现逻辑。这可能发生在例如使用多种语言的开发环境中

I wouldn't say that the size of the database should be the driving force to use or not to use procedures. After all a database which someone thinks large may be small to another.

The factors that affect the usage of the procedures include:

• Eliminate round-trips, with procedures you may be able to eliminate the need of transferring data back and forth from the database. This means that a single unit of work may perform better if the whole operation is done on the database side
• Security, with procedures you can prevent the user (or application) from seeing or modifying the underlying tables allowing only controlled route of access, the procedure
• Maintainability, think of the procedure like an interface. For example you can change the underlying data structure and still provide the same result to the calling side
• Dynamic queries, creating and handling dynamic queries is typically more inconvenient and difficult inside procedures
• User conversation, user conversation isn't allowed inside a transaction so this needs to be taken into account when designing the architecture
• Multiple callers, if your environment has multiple applications, it may be easier to implement the logic inside a procedure and call it from several places than to implement the logic in multiple places at calling side. This can happen for example in development environments utilizing multiple languages

这是一个很好的问题，并指出热烈的讨论。

我个人更喜欢保留所有代码一起（C＃和数据库代码），但在大型数据库中可能有（性能）原因选择存储过程。

数据库时我们遇到 SQL Server 性能问题查询的大小和数量都在增长，但是我们通过切换到 PostgreSQL 解决了这个问题，并且没有使用存储过程。

这里我们的DAL（数据访问层）的优势显示up：将代码从SQL Server转换为PostgreSQL相对容易，因为所有代码都在一个地方。

如果您对PostgreSQL的数据库转换感兴趣，请参阅：将SQL Server数据库转换为PostgreSQL [ ^ ]



对于没有留下任何评论的人来说，我的问题是：我真的很想知道为什么，是不是因为你确信SQL Server是最好的？

你有没有尝试过PostgreSQL？如果是的话，你反对它的论点是什么？

That's a good question, and point for heated discussions.
Personally I prefer to keep all code together (C# and database code), but in big databases there might be (perfomance) reasons to opt for stored procedures.
We had problems with SQL Server performance when the database size and number of queries was growing, but we solved this by switching to PostgreSQL and did not use stored procedures.
Here an advantage of our DAL (Data Access Layer) showed up: it was relatively easy to convert the code from SQL Server to PostgreSQL as all code was in one place.
If you are interested in database conversion to PostgreSQL, see: Convert SQL Server Database to PostgreSQL[^]

To the people downvoting this without leaving any comment, my question is: I would really like to know why, is it just because you are convinced SQL Server is the best ?
Have you ever tried PostgreSQL ? if so, what are your arguments against it ?

这篇关于STORED PROCEDURE是在大型数据库中使用的最佳方式吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！