首页
外语
计算机
考研
公务员
职业资格
财经
工程
司法
医学
专升本
自考
实用职业技能
登录
计算机
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to
admin
2009-05-19
47
问题
You are the administrator of a SQL Server 2000 database. Using the default options configures the database. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete.
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: By recompiling the stored procedure SQL Server will create a new execution plan for the statements in the procedure. SQL Server will detect that statistics are not up to date and it will automatically recreate the statistics during this process. The execution plan will be optimized for the current distribution of data in the tables.
Note: Stored procedures can be used to improve database query performance. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan that is cached on the server. When the database is changed by the addition of new indexes or when data in indexed columns are changed, the original query plans and stored procedures used to access the tables should be reoptimized by recompiling them.
This optimization happens automatically the first time a stored procedure is run after SQL Server 2000 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically occur until the next time the stored procedure is run after SQL Server is restarted. The stored procedure can be manually recompiled by using the sp_recompile system stored procedure. This forces the stored procedure to be recompiled the next time it is run.
Incorrect Answers:
B: SQL Server detects whether statistics are not up-to-date, and by default it automatically updates them during query optimization. By recompiling the query the statistics will automatically be updated.
Note: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Query optimization depends on the accuracy of the distribution steps. If there is significant change in the key values in the index, or if a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated and then repopulated, UPDATE STATISTICS should be rerun.
C: Consistency of a database is maintained through the use of constraints and triggers. These
mechanisms are used to ensure that the database remains consistent by controlling the type of data that can be inserted into a column, through referential integrity checks, and through cascading referential constraints.
D: A view is a virtual table that can be used to restrict users access to the base table, or to join various base tables so that they appear as a single table. However, creating a view that the stored procedure must access would require the creation of indexes on the view and will have to be updated when the base tables are updates. This would require greater administrative effort.
转载请注明原文地址:https://kaotiyun.com/show/RuhZ777K
本试题收录于:
微软70228题库微软认证分类
0
微软70228
微软认证
相关试题推荐
[A]Marketforglasscraftsisgrowing[B]Dependenceofcomputerdevelopmentonglass[C]Behindtheadaptabilityofglass[D]
School-agechildrenshouldparticipatein60minutesormoreofmoderatetovigorousphysicalactivitydaily,accordingtoanex
Writeanessayof160~200wordsbasedonthefollowingdrawing.Inyouressay,youshould1)describethedrawingbriefly,
EricHansenwritesabouttravelasaparticipatingenthusiastratherthanamereobserver.【T1】Itgivesthesenineessays,based
EricHansenwritesabouttravelasaparticipatingenthusiastratherthanamereobserver.【T1】Itgivesthesenineessays,based
In2016,manyshoppersoptedtoavoidthefreneticcrowdsanddotheirholidayshoppingfromthecomfortoftheircomputer.But
Evenintraditionaloffices,"thelinguafrancaofcorporateAmericahasgottenmuchmoreemotionalandmuchmoreright-brained
Adealisadeal—except,apparently,whenEntergyisinvolved.Thecompany,amajorenergysupplierinNewEngland,provokedjus
Notlongago,chiefexecutiveofficers(CEOS)werelandedasheroes,leadersofthebraveneweconomy.BillGatesandJackWelchw
Whateverhappenedtothedeathofnewspaper?Ayearagotheendseemednear.Therecessionthreatenedtoremovetheadvertising
随机试题
背景材料某饮料厂预购买固体橘汁饮料的生产技术与设备,当时能提供这种技术与设备的有甲、乙、丙三个厂家。该饮料厂经过初步调查,得知甲厂的报价最低,于是该饮料厂就决定与甲厂进行谈判。经过几轮谈判,该饮料厂发现他们的很多要求不能得到满足,如果想改变条件满足自己的
班同的《汉书》代表着两汉时期散文的最高成就。()
"其两侧自腋而下,至肋骨之尽处,统名为胁",一语见于
男性,40岁,患十二指肠溃疡,择期经上腹正中切口行胃大部切除术,并置切口内乳胶片引流。一般拔除引流片的时间为
表示减毒配伍关系是
下列关于全国人民代表大会的预算管理职权的表述中,不正确的是()。
关于用人单位与劳动者建立劳动关系时间的说法,正确的是()。
杰出的数学家陈景润研究哥德巴赫猜想,昼夜不舍,数十年如一日。著名的作家姚雪垠,每天凌晨3时起床,奋笔写作,下午则读书,研究史料,每天工作十多个小时。女青年张文淑,把全部业余时间用于中国医学、中医学、皮肤科病的自学,成了医治皮肤病的专家。这段话直接支
HeinzHi-ProteinNutritiousCereal,thequalityproduct,ismadetothestandardsoftheH.J.HeinzCompany,oneoftheleading
EducationinCanadaEducationinCanadaisprovided,fundedandoverseenbyfederal,provincial,andlocalgovernments.Iti
最新回复
(
0
)