Jaki jest sens i korzyść z używania SqlCommand.Prepare ()?

Natknąłem się na kod programisty, w którym metoda SqlCommand.Prepare () (patrz MSDN) jest szeroko stosowana przed wykonywaniem zapytań SQL. I zastanawiam się, jaka jest z tego korzyść?

Próba:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

Grałem trochę i prześledziłem. Wykonanie polecenia po wywołaniu Prepare()metody powoduje, że Sql Server wykonuje następującą instrukcję:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

Następnie, gdy parametr otrzyma jego wartość i SqlCommand.ExecuteNonQuery()zostanie wywołany, następujące polecenie zostanie wykonane na serwerze Sql:

exec sp_execute 1,@id=20

Dla mnie wygląda to na kompilację instrukcji, gdy tylko Prepare()zostanie wykonana. Zastanawiam się, jaka jest z tego korzyść? Czy to oznacza, że ​​jest on umieszczany w pamięci podręcznej planu i może być ponownie użyty, gdy tylko ostatnie zapytanie zostanie wykonane z żądanymi wartościami parametrów?

Doszedłem do wniosku (i udokumentowałem to w innym pytaniu ), że SqlCommands, które są wykonywane za pomocą SqlParameters, zawsze są opakowane w sp_executesqlwywołania procedur. Dzięki temu Sql Server może przechowywać i ponownie wykorzystywać plany niezależnie od wartości parametrów.

W związku z tym zastanawiam się, czy prepare()metoda jest w pewnym sensie bezużyteczna lub przestarzała, czy też czegoś mi brakuje?

Przygotowanie partii SQL oddzielnie od wykonania przygotowanej partii SQL to konstrukcja, która jest skutecznie ^**bezużyteczne dla SQL Server, biorąc pod uwagę sposób buforowania planów wykonania. Rozdzielenie etapów przygotowania (parsowanie, wiązanie dowolnych parametrów i kompilacja) i wykonywania ma sens tylko wtedy, gdy nie ma buforowania. Celem jest zaoszczędzenie czasu poświęcanego na analizowanie i kompilację poprzez ponowne użycie istniejącego planu, i właśnie to robi wewnętrzna pamięć podręczna planu. Ale nie wszystkie RDBMS wykonują buforowanie na tym poziomie (wyraźnie na podstawie istnienia tych funkcji), więc kod klienta musi zażądać „buforowania” planu, a więc zapisać ten identyfikator pamięci podręcznej, a następnie ponownie użyć to. Jest to dodatkowe obciążenie dla kodu klienta (i programisty, aby pamiętać, aby to zrobić i zrobić to dobrze), co jest niepotrzebne. W rzeczywistości strona MSDN dla metody IDbCommand.Prepare () stwierdza:

Serwer automatycznie buforuje plany do ponownego użycia w razie potrzeby; dlatego nie ma potrzeby wywoływania tej metody bezpośrednio w aplikacji klienckiej.

Należy zauważyć, że o ile moje testy pokazują (które pasują do tego, co pokazujesz w pytaniu), wywołanie SqlCommand.Prepare () , nie wykonuje operacji „tylko”: wywołuje sp_prepexec, która przygotowuje i wykonuje SQL ; nie wywołuje sp_prepare, które jest tylko parsowane i kompilowane (i nie przyjmuje żadnych wartości parametrów, tylko ich nazwy i typy danych). Dlatego nie może być żadnej korzyści z „prekompilacji” wywołania, SqlCommand.Prepareponieważ wykonuje ono natychmiastowe wykonanie (zakładając, że celem jest odroczenie wykonania). JEDNAK istnieje ciekawa potencjalna niewielka korzyść z dzwonienia SqlCommand.Prepare(), nawet jeśli dzwoni sp_prepexeczamiast sp_prepare. Zobacz notatkę (^** ) na dole po szczegóły.

Moje testy pokazują, że nawet gdy SqlCommand.Prepare()zostanie wywołane (i pamiętaj, że to wywołanie nie wydaje żadnych poleceń na SQL Server), to ExecuteNonQuerylub ExecuteReadernastępne jest w pełni wykonane, a jeśli jest to ExecuteReader, zwraca wiersze. Mimo to SQL Server Profiler pokazuje, że pierwsze SqlCommand.Execute______()wywołanie po SqlCommand.Prepare()połączeniu rejestruje się jako zdarzenie „Przygotuj SQL”, a kolejne .Execute___()połączenia rejestrują się jako zdarzenia „Exec Prepared SQL”.

Kod testowy

Został on przesłany do PasteBin pod adresem : http://pastebin.com/Yc2Tfvup . Kod tworzy aplikację konsoli .NET / C #, która ma być uruchomiona, gdy uruchomione jest śledzenie programu SQL Server Profiler (lub sesja zdarzeń rozszerzonych). Zatrzymuje się po każdym kroku, aby było jasne, które stwierdzenia mają określony efekt.

AKTUALIZACJA

Znaleziono więcej informacji i niewielki potencjalny powód, aby unikać połączeń SqlCommand.Prepare(). Jedną z rzeczy, które zauważyłem podczas testów, i co należy zauważyć dla każdego, kto korzysta z tej testowej aplikacji na konsolę: nigdy nie jest wykonywane wyraźne połączenie sp_unprepare. Zrobiłem trochę kopania i znalazłem następujący post na forach MSDN:

SqlCommand - Przygotować czy nie przygotować?

Akceptowana odpowiedź zawiera wiele informacji, ale najważniejsze punkty to:

• ^** Przygotowanie naprawdę oszczędza przede wszystkim czas potrzebny na przesłanie ciągu zapytania przez drut.
• Przygotowane zapytanie nie ma gwarancji zapisania planu w pamięci podręcznej i nie jest szybsze niż zapytanie ad-hoc po dotarciu do serwera.
• RPC (CommandType.StoredProcedure) nic nie zyskuje na przygotowaniach.
• Na serwerze przygotowany uchwyt jest w zasadzie indeksem do mapy w pamięci zawierającej TSQL do wykonania.
• Mapa jest przechowywana dla poszczególnych połączeń, nie jest możliwe użycie połączenia krzyżowego.
• Reset wysyłany, gdy klient ponownie wykorzystuje połączenie z puli, usuwa mapę.

Znalazłem również następujący post z zespołu SQLCAT, który wydaje się być powiązany, ale może być po prostu problemem specyficznym dla ODBC, podczas gdy SqlClient porządnie czyści po zlikwidowaniu SqlConnection. Trudno powiedzieć, ponieważ post SQLCAT nie wspomina o żadnych dodatkowych testach, które pomogłyby udowodnić to jako przyczynę, takich jak wyczyszczenie puli połączeń itp.

Uważaj na przygotowane instrukcje SQL

WNIOSEK

Jeśli się uwzględni:

• jedyną prawdziwą korzyścią z wywoływania SqlCommand.Prepare()jest to, że nie trzeba ponownie przesyłać tekstu zapytania przez sieć,
• wywoływanie sp_preparei sp_prepexeczapisywanie tekstu zapytania jako części pamięci połączenia (tj. pamięci SQL Server)

Polecam przed wywołaniem SqlCommand.Prepare()ponieważ potencjał jedyną korzyścią jest oszczędność pakietów sieciowych, natomiast minusem bierze więcej pamięci serwera. Podczas gdy ilość zużywanej pamięci jest prawdopodobnie bardzo niewielka w większości przypadków, przepustowość sieci rzadko stanowi problem, ponieważ większość serwerów DB jest bezpośrednio połączonych z serwerami aplikacji ponad 10-megabitowymi (obecnie prawdopodobnie 100-megabitowymi lub gigabitowymi) połączeniami ( a niektóre są nawet na tym samym pudełku ;-). To i pamięć są prawie zawsze bardziej rzadkim zasobem niż przepustowość sieci.

Podejrzewam, że dla każdego, kto pisze oprogramowanie, które może łączyć się z wieloma bazami danych, wygoda standardowego interfejsu może zmienić tę analizę kosztów i korzyści. Ale nawet w takim przypadku muszę wierzyć, że nadal łatwo jest wyodrębnić „standardowy” interfejs DB, który pozwala różnym dostawcom (a zatem różnicom między nimi, takim jak brak konieczności dzwonienia Prepare()), biorąc pod uwagę, że jest to podstawowy obiekt Zorientowane programowanie, które prawdopodobnie już wykonujesz w kodzie aplikacji ;-).

W związku z tym zastanawiam się, czy metoda prepar () jest w pewnym sensie bezużyteczna lub przestarzała, czy też czegoś mi brakuje?

Powiedziałbym, że metoda Prepare ma ograniczoną wartość w świecie SqlCommand, nie że jest całkowicie bezużyteczna. Jedną z korzyści jest to, że Prepare jest częścią interfejsu IDbCommand, który implementuje SqlCommand. Pozwala to na uruchamianie tego samego kodu z innymi dostawcami DBMS (które mogą wymagać przygotowania) bez logiki warunkowej w celu ustalenia, czy należy wywołać przygotowanie.

Przygotowanie nie ma wartości u dostawcy .NET dla SQL Server poza przypadkami użycia AFAIK.