Vad är det som är så snabbt med SP (Stored Procedures)

Citat från den översta (!) träffen i min googling:

They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

Källa: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_31vb.asp

Vad jag förstod av det @nders skrev så har inte SP något att göra med datamängden utan om det är en SP så behövs den bara bli genomgången en gång. Och vid icke användning av SP så måste den gå ingenom sql satsen varje gång för att optimera osv.

Har jag fattat det rätt?

Min uppfattning (det jag tycker är viktigt) är att det man tjänar är:

1. Proceduren är färdigkompilerad och cachead efter första körningen. Servern håller queryplans i minnet.
2. Du slipper skicka komplicerade långa SQL-frågor över nätverket, det räcker med ett kort exekveringskommando.
3. Det är lättare att ändra i en SP än x antal ställen varifrån SQL-frågan ställs.

Pass på att inte använda dynamisk SQL i dina SP:s om du inte vill tappa fördelarna nämnda i punkt 1.

mvh

Det sägs överallt att SP (Stored Procedures) i SQL Server är så mycket snabbare?

Njaaaaeee, det är inte riktigt sant längre. Inte i nyare versioner av SQL server :bire

Ska förklara varför.

I tex SQL-server 6.5 och tidigare precompilerades SP:en och lades i cachen, medans dynamisk SQL fick kompileras om varje gång de kördes. Vilket gjorde SP mycket snabbare än dynamisk SQL. Men detta är ändrat i SQL server 7 och framåt, där kompileras BÅDE SP och dynamisk SQL och läggs i cachen, och varje gång du exekverar en fet dynamisk SQL så kollar SQL servern först upp om denna dynamiska SQL har exekverats tidigare, och då kommer den att hämta execution planen från cachen precis som en SP. Så skillnaden i prestanda mellan dynamisk SQL och SP är inte särskilt stor längre. Om det är nog skillnad alls eftersom de fungerar på precis samma sätt.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp

Scrolla ner till:Stored Procedures and Execution Plans

Nöff, på vilket sätt motsäger detta att stored procedures skulle vara snabbare?

Procedures har en färdigkompilerad execution plan.

They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_31vb.asp

Och då kontrar jag med: :bire

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp

Och här kan ni läsa lite om vilka algoritmer SQL servern använder för att hitta cachade execution plans för dynamiska sql frågor och om den inte hittar så compilerar den den och lägger in i cachen utifall någon kommer exekvera samma fråga igen.

When any SQL statement is executed in SQL Server 2000, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2000 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2000 generates a new execution plan for the query.

SQL Server 2000 has an efficient algorithm to find any existing execution plans for any given SQL statement. In most systems, the minimal resources used by this scan are less than the resources saved by being able to reuse existing plans instead of compiling every SQL statement.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_99wl.asp

Procedures har en färdigkompilerad execution plan.

Nä, nte i nyare versioner av SQL server, där kompileras den vid första körningen och sen cachas(kolla ovan), precis samma sak sker med dynamiska sql frågor, de kompileras vid första körningen och sen läggs i cachen. A stored procedure is compiled at execution time, like any other Transact-SQL statement

Det är ingen skillnad mellan SP och dynamiska sqlfrågor längre. Förutom det att när man ställer en dynamisk sql fråga så kommer SQL servern att leta igenom alla execution plans som finns i cachen och använda den som matchar, om den inte finns så kompileras den och läggs i cachen. Men denna "sökning" tar upp så minimala resurser att man inte behöver bry sig. In most systems, the minimal resources used by this scan are less than the resources saved by being able to reuse existing plans instead of compiling every SQL statement.

Där ser man. Det är härligt när man lär sig nya saker. Tack för lektionen Nöff. :)

Om inte annat så är SP bra om man vill modularisera sin applikation. Att ha SQL-kod i en kompilerad komponent kanske man vill undvika.

Drar upp tråden igen. Hur är det med MS SQL 2005 blivit några förändringar som gör att SP är snabbare?

En jättefördel med SP är ju att det är samma oavsett från vilken miljö eller språk man anropar den:
Därför skall man använda SP