Users report that response times from a stored procedure have become slower. In the stored procedure, you examine the following code.
CREATE PROCEDURE GetCustomer
( @CustomerID Int )
AS
SELECT FirstName, LastName, Address1, Address2, City,
State, Zip, MAX(PurchaseDate) AS LastPurchase
FROM Customers C
JOIN Addresses A ON A.AddressID = C.AddressID
JOIN Purchases P ON P.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip
You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do?
A．Recompile the stored procedure.
B．Update the database statistics.
C．Check the consistency of the database and repair errors.
D．Create a view and modify the stored procedure to use the view.
参考答案
正确答案：A
解析：Explanation:ByrecompilingthestoredprocedureSQLServerwillcreateanewexecutionplanforthestatementsintheprocedure.SQLServerwilldetectthatstatisticsarenotuptodateanditwillautomaticallyrecreatethestatisticsduringthisprocess.Theexecutionplanwillbeoptimizedforthecurrentdistributionofdatainthetables.Note:Storedprocedurescanbeusedtoimprovedatabasequeryperformance.IftheseSQLstatementsandconditionallogicarewrittenintoastoredprocedure,theybecomepartofasingleexecutionplanthatiscachedontheserver.Whenthedatabaseischangedbytheadditionofnewindexesorwhendatainindexedcolumnsarechanged,theoriginalqueryplansandstoredproceduresusedtoaccessthetablesshouldbereoptimizedbyrecompilingthem.ThisoptimizationhappensautomaticallythefirsttimeastoredprocedureisrunafterSQLServer2000isrestarted.Italsooccursifanunderlyingtableusedbythestoredprocedurechanges.Butifanewindexisaddedfromwhichthestoredproceduremightbenefit,optimizationdoesnotautomaticallyoccuruntilthenexttimethestoredprocedureisrunafterSQLServerisrestarted.Thestoredprocedurecanbemanuallyrecompiledbyusingthesp_recompilesystemstoredprocedure.Thisforcesthestoredproceduretoberecompiledthenexttimeitisrun.IncorrectAnswers:B:SQLServerdetectswhetherstatisticsarenotup-to-date,andbydefaultitautomaticallyupdatesthemduringqueryoptimization.Byrecompilingthequerythestatisticswillautomaticallybeupdated.Note:SQLServerkeepsstatisticsaboutthedistributionofthekeyvaluesineachindexandusesthesestatisticstodeterminewhichindex(es)touseinqueryprocessing.Queryoptimizationdependsontheaccuracyofthedistributionsteps.Ifthereissignificantchangeinthekeyvaluesintheindex,orifalargeamountofdatainanindexedcolumnhasbeenadded,changed,orremoved,orthetablehasbeentruncatedandthenrepopulated,UPDATESTATISTICSshouldbererun.C:Consistencyofadatabaseismaintainedthroughtheuseofconstraintsandtriggers.Thesemechanismsareusedtoensurethatthedatabaseremainsconsistentbycontrollingthetypeofdatathatcanbeinsertedintoacolumn,throughreferentialintegritychecks,andthroughcascadingreferentialconstraints.D:Aviewisavirtualtablethatcanbeusedtorestrictusersaccesstothebasetable,ortojoinvariousbasetablessothattheyappearasasingletable.However,creatingaviewthatthestoredproceduremustaccesswouldrequirethecreationofindexesontheviewandwillhavetobeupdatedwhenthebasetablesareupdates.Thiswouldrequiregreateradministrativeeffort.