저장 프로 시저가 일반적으로 최신 RDBMS의 인라인 문보다 더 효율적입니까? [복제]
이 질문에 이미 답변이 있습니다.
일반적으로 저장 프로시 저는 항상 더 빠릅니다. 그들은 항상 빠른 것 때문에 그래서, 그들에게 사용하는 모든 시간을 .
나는 이것이 한때 그것이 사실이었던 어떤 역사적 맥락에 근거하고 있다고 확신합니다. 이제 저는 Stored Procs가 필요하지 않다고 주장하는 것이 아니라 MySQL, SQL Server, Oracle 또는 < Insert_your_DB_here > 와 같은 최신 데이터베이스에서 어떤 경우에 저장 프로 시저가 필요한지 알고 싶습니다 . 저장 프로 시저를 통해 모든 액세스 권한을 갖는 것은 과잉입니까?
참고 이 특정 DBMS에 규제를받지 저장 프로 시저의 일반적인 모습이다. 일부 DBMS (및 동일한 DBMS의 다른 버전!)는 이와 반대로 작동 할 수 있으므로이 모든 것이 여전히 유지된다고 가정하기 전에 대상 DBMS를 다시 확인하는 것이 좋습니다.
저는 거의 10 년 동안 (C, PHP, PL / SQL, C # .NET 및 Ruby로 애플리케이션 개발과 함께) Sybase ASE, MySQL 및 SQL Server DBA로 일했습니다. 그래서 저는이 (때때로) 거룩한 전쟁에서 갈아 낼 특별한 도끼가 없습니다.
저장된 procs의 과거 성능 이점은 일반적으로 다음과 같습니다 (특정 순서 없음).
- 미리 구문 분석 된 SQL
- 미리 생성 된 쿼리 실행 계획
- 네트워크 대기 시간 감소
- 잠재적 인 캐시 이점
미리 구문 분석 된 SQL- 매우 마이크로 수준을 제외하고 컴파일 된 코드와 해석 된 코드와 유사한 이점.
여전히 이점이 있습니까? 최신 CPU에서는 그다지 눈에 띄지 않지만 초당 매우 큰 단일 SQL 문을 보내는 경우 파싱 오버 헤드가 추가 될 수 있습니다.
미리 생성 된 쿼리 실행 계획 . JOIN이 많으면 순열이 관리하기 어렵게 커질 수 있습니다 (최신 최적화 프로그램에는 성능상의 이유로 제한과 컷오프가 있습니다). 매우 복잡한 SQL이 "거의 최선의 결과"를 파악하려고 시도하기 때문에 매우 복잡한 SQL이 고유하고 측정 가능한 지연 시간 (DBMS를 조정하기 전에 계획을 생성하는 데 10 초 이상 소요되는 복잡한 쿼리를 보았습니다)을 갖는 것은 알 수 없습니다. "실행 계획. 저장 프로시 저는 일반적으로이 오버 헤드를 피할 수 있도록이를 메모리에 저장합니다.
여전히 이점이 있습니까? 대부분의 DBMS (최신 버전)는 INDIVIDUAL SQL 문에 대한 쿼리 계획을 캐시하여 저장된 procs와 ad hoc SQL 간의 성능 차이를 크게 줄입니다. 그렇지 않은 경우 몇 가지주의 사항과 사례가 있으므로 대상 DBMS에서 테스트해야합니다.
또한 점점 더 많은 DBMS를 통해 최적화 프로그램 경로 계획 (추상 쿼리 계획)을 제공하여 최적화 시간을 크게 줄일 수 있습니다 (임시 및 저장 프로 시저 SQL !!).
경고 캐시 된 쿼리 계획은 성능 만병 통치약이 아닙니다. 경우에 따라 생성되는 쿼리 계획이 최적이 아닙니다. 예를 들어를 보내면
SELECT * FROM table WHERE id BETWEEN 1 AND 99999999DBMS는 테이블의 모든 행을 잡기 때문에 인덱스 스캔 대신 전체 테이블 스캔을 선택할 수 있습니다 (통계라고도 함). 이것이 캐시 된 버전 인 경우 나중에을 보낼 때 성능이 저하 될 수 있습니다SELECT * FROM table WHERE id BETWEEN 1 AND 2. 이에 대한 이유는이 게시물의 범위를 벗어나지 만 더 자세한 내용은 http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx 및 http://msdn.microsoft.com/을 참조하십시오. en-us / library / ms181055.aspx 및 http://www.simple-talk.com/sql/performance/execution-plan-basics/"요약 적으로, 그들은 컴파일 또는 재 컴파일이 수행 될 때 공통 값이 아닌 다른 값을 제공하면 옵티마이 저가 해당 특정 값에 대한 쿼리 계획을 컴파일하고 캐싱하는 결과를 초래한다고 결정했습니다. 그러나 해당 쿼리 계획이 후속 실행에 재사용되었을 때 공통 값 ( 'M', 'R'또는 'T')에 대한 쿼리를 수행하면 성능이 최적화되지 않았습니다. 이러한 최적화되지 않은 성능 문제는 쿼리를 다시 컴파일 할 때까지 존재했습니다. 그 시점에서 @ P1을 기반으로합니다. 매개 변수 값이 제공되면 쿼리에 성능 문제가있을 수도 있고 없을 수도 있습니다. "
네트워크 대기 시간 감소 A) 동일한 SQL을 반복해서 실행하고 SQL이 많은 KB의 코드를 추가하는 경우 간단한 "exec foobar"로 대체하면 실제로 추가 될 수 있습니다. B) 저장된 procs를 사용하여 절차 코드를 DBMS로 이동할 수 있습니다. 이것은 많은 양의 데이터를 클라이언트에 셔플 링하여 소량의 정보를 다시 보내도록합니다 (또는 전혀 보내지 않습니다!). DBMS에서 JOIN을 수행하는 것과 코드에서 (모든 사람이 좋아하는 WTF!)
여전히 이점이 있습니까? A) 최신 1Gb (및 10Gb 이상!) 이더넷은이를 무시할 수있는 수준으로 만듭니다. B) 네트워크가 얼마나 포화 상태인지에 따라 다릅니다. 왜 정당한 이유없이 수 메가 바이트의 데이터를 앞뒤로 밀고 있습니까?
잠재적 캐시 이점 DBMS에 충분한 메모리가 있고 필요한 데이터가 서버의 메모리에있는 경우 데이터의 서버 측 변환을 수행하는 것이 잠재적으로 더 빠를 수 있습니다.
여전히 이점이 있습니까? 앱이 DBMS 데이터에 대한 메모리 액세스를 공유하지 않는 한 에지는 항상 저장된 프로세서에 있습니다.
물론 매개 변수화 된 임시 SQL에 대한 논의 없이는 저장 프로 시저 최적화에 대한 논의가 완료되지 않습니다.
매개 변수화 / 준비된 SQL
저장 프로 시저와 임시 SQL 간의 일종의 교차로, 쿼리 값에 "매개 변수"를 사용하는 호스트 언어의 내장 SQL 문입니다. 예 :
SELECT .. FROM yourtable WHERE foo = ? AND bar = ?
이는 현대의 최적화 프로그램이 쿼리 실행 계획을 캐시 (및 재사용)하는 데 사용할 수있는보다 일반화 된 버전의 쿼리를 제공하여 저장 프로 시저의 성능 이점을 상당 부분 제공합니다.
Ad Hoc SQL DBMS에 대한 콘솔 창을 열고 SQL 문을 입력하면됩니다. 과거에는 DBMS가 매개 변수화 / 저장된 proc 메소드 에서처럼 쿼리를 사전 최적화 할 방법이 없었기 때문에 평균적으로 "최악"수행자였습니다.
Still a disadvantage? Not necessarily. Most DBMS have the ability to "abstract" ad hoc SQL into parameterized versions -- thus more or less negating the difference between the two. Some do this implicitly or must be enabled with a command setting (SQL server: http://msdn.microsoft.com/en-us/library/ms175037.aspx , Oracle: http://www.praetoriate.com/oracle_tips_cursor_sharing.htm).
Lessons learned? Moore's law continues to march on and DBMS optimizers, with every release, get more sophisticated. Sure, you can place every single silly teeny SQL statement inside a stored proc, but just know that the programmers working on optimizers are very smart and are continually looking for ways to improve performance. Eventually (if it's not here already) ad hoc SQL performance will become indistinguishable (on average!) from stored procedure performance, so any sort of massive stored procedure use ** solely for "performance reasons"** sure sounds like premature optimization to me.
Anyway, I think if you avoid the edge cases and have fairly vanilla SQL, you won't notice a difference between ad hoc and stored procedures.
Reasons for using stored procedures:
- Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
- Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
- Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.
- Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc
- Separation of logic -- remove the SQL-generating code and segregate it in the database.
- Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.
- Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.
- Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.
- SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection.
In many cases, stored procedures are actually slower because they're more genaralized. While stored procedures can be highly tuned, in my experience there's enough development and institutional friction that they're left in place once they work, so stored procedures often tend to return a lot of columns "just in case" - because you don't want to deploy a new stored procedure every time you change your application. An OR/M, on the other hand, only requests the columns the application is using, which cuts down on network traffic, unnecessary joins, etc.
It's a debate that rages on and on (for instance, here).
It's as easy to write bad stored procedures as it is to write bad data access logic in your app.
My preference is for Stored Procs, but that's because I'm typically working with very large and complex apps in an enterprise environment where there are dedicated DBAs who are responsible for keeping the database servers running sweetly.
In other situations, I'm happy enough for data access technologies such as LINQ to take care of the optimisation.
Pure performance isn't the only consideration, though. Aspects such as security and configuration management are typically at least as important.
Edit: While Frans Bouma's article is indeed verbose, it misses the point with regard to security by a mile. The fact that it's 5 years old doesn't help its relevance, either.
There is no noticeable speed difference for stored procedures vs parameterized or prepared queries on most modern databases, because the database will also cache execution plans for those queries.
Note that a parameterized query is not the same as ad hoc sql.
The main reason imo to still favor stored procedures today has more to do with security. If you use stored procedures exclusively, you can disable INSERT, SELECT, UPDATE, DELETE, ALTER, DROP, and CREATE etc permissions for your application's user, only leaving it with EXECUTE.
This provides a little extra protection against 2nd order sql injection. Parameterized queries only protect against 1st order injection.
The one topic that no one has yet mentioned as a benefit of stored procedures is security. If you build the application exclusively with data access via stored procedures, you can lockdown the database so the ONLY access is via those stored procedures. Therefor, even if someone gets a database ID and password, they will be limited in what they can see or do against that database.
Obviously, actual performance ought to be measured in individual cases, not assumed. But even in cases where performance is hampered by a stored procedure, there are good reasons to use them:
Application developers aren't always the best SQL coders. Stored procedures hides SQL from the application.
Stored procedures automatically use bind variables. Application developers often avoid bind variables because they seem like unneeded code and show little benefit in small test systems. Later on, the failure to use bind variables can throttle RDBMS performance.
Stored procedures create a layer of indirection that might be useful later on. It's possible to change implementation details (including table structure) on the database side without touching application code.
The exercise of creating stored procedures can be useful for documenting all database interactions for a system. And it's easier to update the documentation when things change.
That said, I usually stick raw SQL in my applications so that I can control it myself. It depends on your development team and philosophy.
In 2007 I was on a project, where we used MS SQL Server via an ORM. We had 2 big, growing tables which took up to 7-8 seconds of load time on the SQL Server. After making 2 large, stored SQL procedures, and optimizing them from the query planner, each DB load time got down to less than 20 milliseconds, so clearly there are still efficiency reasons to use stored SQL procedures.
Having said that, we found out that the most important benefit of stored procedures was the added maintaince-ease, security, data-integrity, and decoupling business-logic from the middleware-logic, benefitting all middleware-logic from reuse of the 2 procedures.
Our ORM vendor made the usual claim that firing off many small SQL queries were going to be more efficient than fetching large, joined data sets. Our experience (to our surprise) showed something else.
This may of course vary between machines, networks, operating systems, SQL servers, application frameworks, ORM frameworks, and language implementations, so measure any benefit, you THINK you may get from doing something else.
It wasn't until we benchmarked that we discovered the problem was between the ORM and the database taking all the load.
I prefer to use SP's when it makes sense to use them. In SQL Server anyway there is no performance advantage to SP's over a parametrized query.
However, at my current job my boss mentioned that we are forced to use SP's because our customer's require them. They feel that they are more secure. I have not been here long enough to see if we are implementing role based security but I have a feeling we do.
So the customer's feelings trump all other arguments in this case.
To me one advantage of stored procedures is to be host language agnostic: you can switch from a C, Python, PHP or whatever application to another programming language without rewriting your code. In addition, some features like bulk operations improve really performance and are not easily available (not at all?) in host languages.
Read Frans Bouma's excellent post (if a bit biased) on that.
All I can speak to is SQL server. In that platform, stored procedures are lovely because the server stores the execution plan, which in most cases speeds up performance a good bit. I say "in most cases", because if the SP has widely varying paths of execution you might get suboptimal performance. However, even in those cases, some enlightened refactoring of the SPs can speed things up.
Using stored procedures for CRUD operations is probably overkill, but it will depend on the tools be used and your own preferences (or requirements). I prefer inline SQL, but I make sure to use parameterized queries to prevent SQL injection attacks. I keep a print out of this xkcd comic as a reminder of what can go wrong if you are not careful.
Stored procedures can have real performance benefits when you are working with multiple sets of data to return a single set of data. It's usually more efficient to process sets of data in the stored procedure than sending them over the wire to be processed at the client end.
Realising this is a bit off-topic to the question, but if you are using a lot of stored procedures, make sure there is a consistent way to put them under some sort of source control (e.g., subversion or git) and be able to migrate updates from your development system to the test system to the production system.
When this is done by hand, with no way to easily audit what code is where, this quickly becomes a nightmare.
I don't know that they are faster. I like using ORM for data access (to not re-invent the wheel) but I realize that's not always a viable option.
Frans Bouma has a good article on this subject : http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Stored procs are great for cases where the SQL code is run frequently because the database stores it tokenized in memory. If you repeatedly ran the same code outside of a stored proc, you will likey incur a performance hit from the database reparsing the same code over and over.
I typically frequently called code as a stored proc or as a SqlCommand (.NET) object and execute as many times as needed.
Yes, they are faster most of time. SQL composition is a huge performance tuning area too. If I am doing a back office type app I may skip them but anything production facing I use them for sure for all the reasons others spoke too...namely security.
IMHO...
Restricting "C_UD" operations to stored procedures can keep the data integrity logic in one place. This can also be done by restricting"C_UD" operations to a single middle ware layer.
Read operations can be provided to the application so they can join only the tables / columns they need.
Stored procedures can also be used instead of parameterized queries (or ad-hoc queries) for some other advantages too :
- If you need to correct something (a sort order etc.) you don't need to recompile your app
- You could deny access to all tables for that user account, grant access only to stored procedures and route all access through stored procedures. This way you can have custom validation of all input much more flexible than table constraints.
Reduced network traffic -- SP are generally worse then Dynamic SQL. Because people don't create a new SP for every select, if you need just one column you are told use the SP that has the columns they need and ignore the rest. Get an extra column and any less network usage you had just went away. Also you tend to have a lot of client filtering when SP are used.
caching -- MS-SQL does not treat them any differently, not since MS-SQL 2000 may of been 7 but I don't remember.
permissions -- Not a problem since almost everything I do is web or have some middle application tier that does all the database access. The only software I work with that have direct client to database access are 3rd party products that are designed for users to have direct access and are based around giving users permissions. And yes MS-SQL permission security model SUCKS!!! (have not spent time on 2008 yet) As a final part to this would like to see a survey of how many people are still doing direct client/server programming vs web and middle application server programming; and if they are doing large projects why no ORM.
Separation -- people would question why you are putting business logic outside of middle tier. Also if you are looking to separate data handling code there are ways of doing that without putting it in the database.
Ability to edit -- What you have no testing and version control you have to worry about? Also only a problem with client/server, in the web world not problem.
Find the table -- Only if you can identify the SP that use it, will stick with the tools of the version control system, agent ransack or visual studio to find.
Optimization -- Your DBA should be using the tools of the database to find the queries that need optimization. Database can tell the DBA what statements are talking up the most time and resources and they can fix from there. For complex SQL statements the programmers should be told to talk to the DBA if simple selects don't worry about it.
SQL injection attacks -- SP offer no better protection. The only thing they get the nod is that most of them teach using parameters vs dynamic SQL most examples ignore parameters.
'Programing' 카테고리의 다른 글
| plot.new () 오류 : R에서 그림 여백이 너무 큽니다. (0) | 2020.08.12 |
|---|---|
| Rails 3는 모델없이 커스텀 SQL 쿼리를 실행합니다. (0) | 2020.08.12 |
| Rails 및 PostgreSQL : 역할 postgres가 존재하지 않습니다. (0) | 2020.08.12 |
| 다차원 배열의 모든 열 값을 합하는 방법은 무엇입니까? (0) | 2020.08.12 |
| 문자열에서 가장 오른쪽에있는 n 개의 문자 만 추출 (0) | 2020.08.12 |