TechRepublic ingyenes SQL Server hírlevél, szállított minden kedden, tartalmaz gyakorlati tippeket, amelyek segítenek abban, hogy jobban jártas ebben a nagy teljesítményű relációs adatbázis-kezelő rendszer. Automatikus feliratkozás ma!

a tárolt eljárásokkal (vagy sprocs-okkal) kapcsolatos általánosan elfogadott bölcsesség az, hogy mivel az SQL optimalizálhatja és összeállíthatja őket, gyorsabban futnak, mint a QueryAnalyzer-től végrehajtott egyenértékű SQL-utasítások (vagy esetleg valamilyen front-end alkalmazásból, például weboldalról vagy Vbprogramból). Ez bizonyos mértékig igaz.

először tudnia kell, hogy mit tesz az SQL Server egy új sproc-val. A létrehozás idején ellenőrzi a szintaxist. Ha nem talál hibát, akkor hozzáadja a sproc-ot a rendszertáblákhoz: sysobjects, sysdepends és syscomments (az utóbbi tárolja a sproc testét). Alapértelmezés szerint nem fordítja le a sproc-ot a létrehozás idején.

a sproc első végrehajtásakor az SQL Server optimalizálja és lefordítja. Ez az, amikor az SQL Server elkészíti a queryplan-t, majd tárolja az eljárás gyorsítótárában. A későbbi meghívások során az SQL először a gyorsítótárban találja meg a sproc-ot, ésnem fordítja le. Ha a sproc nincs a gyorsítótárban, akkor az SQL Server lefordítja és a gyorsítótárba helyezi.

tapasztalatom A WITH RECOMPILE opcióval

egy ideje támogattam egy keresési oldalt, amely lehetővé tette a felhasználók számára, hogy több oszlop bármelyikével keressenek. Ezután az oldal úgynevezett sproc, átadva egy paramétert, amely jelzi, hogy melyik oszlopkeresés. Megvizsgáltam a paramétert egy ESETBLOKK segítségével, majd végrehajtottam az egyikettöbb lekérdezés, a keresendő oszloptól függően.

tudtam, hogy valami nem stimmel, amikor elkezdtem tesztelni az allegedlyclever tárolt eljárást. Elméletileg az egyes keresések teljesítményének legalább nagyjából azonosnak kell lennie, de nem ez történt. Amikor végrehajtottamtöbbszörös keresések, a megrendeléstől függetlenül, az első gyors lenne, ésa későbbi keresések sokkal lassabbak voltak.

végül rájöttem, hogy amikor először hívták az eljárást, egy lekérdezési tervet dolgoztunk ki, majd tároltuk a gyorsítótárban. Amíg kerestem, hogy az adott oszlop, minden működni fog asexpected. Abban a pillanatban, amikor oszlopokat váltottam, a teljesítmény zuhant. Miért történt ez?

az első elvégzett keresés létrehozott egy lekérdezési tervet, majd tárolja a gyorsítótárban. Például, mondjuk kerestem az oszlop OrderDate. Ha a keresést a CompanyName oszlopra váltottam, az SQL vakon használja a gyorsítótárazott queryplan-t, keresve a célvállalat nevét az OrderDateindex segítségével. Nem csoda, hogy a teljesítmény ilyen drámai módon zuhan.

a javítás meglehetősen egyszerű. Végrehajtottam a sprocsupplying a RECOMPILE opció:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Ez azt mondja SQL Server, hogy dobja el a meglévő lekérdezési tervés építeni egy másikat-de csak egyszer.

A WITH RECOMPILE-t közvetlenül a storedprocedure-hoz is hozzáadhatja közvetlenül az AS kulcsszó előtt. Ez azt mondja SQL Server, hogy dobja ki aquery terv minden végrehajtását a sproc.

van egy harmadik lehetőség is. Létrehozhattam volna minden egyes keresési metódushoz külön sproc-t, és akkor eldönthettem volna, hogy melyiket hajtsam végre az ESETBLOKKON belül. Így a lekérdezési tervaz al-sprocs-okhoz társított lekérdezési terv a gyorsítótárban marad, ahol az SQL kihasználhatja azokat. Mivel mindegyik láncpontosan egy oszlopot keresett, nincs szükség újrafordításra.

SQL Server képes optimalizálni és összeállítani egy storedprocedure nagyszerű, de ha nem vagy óvatos, akkor harapni, ha leastexpect azt. Most, hogy tudja, hogyan kell kezelni a problémát, talán vannak olyan helyzetek a saját adatbázisában, amelyeket érdemes újra megvizsgálni.