|
70--229 question № 33 по TestKing 3.2
|
|||
---|---|---|---|
#18+
Кто знает почему ответ D а не E? QUESTION NO: 33 You are a database developer for an insurance company. You are tuning the performance of queries in SQL Query Analyzer. In the query pane, you create the following query: SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P JOIN AgentPolicy AS AP ON (P.PolicyNumber = AP.PolicyNumber) WHERE IssueState = ‘IL’ AND PolicyDate BETWEEN ‘1/1/2000’ AND ‘3/1/2000’ AND FaceAmount > 1000000 You choose “Display estimated execution plan” from the query menu and execute the query. The query execution plan that is generated is shown in the estimated execution plan exhibit. ... What should you do? A. Rewrite the query to eliminate BETWEEN keyword. B. Add a join hint that includes the HASH option to the query. C. Add the WITH (INDEX(0)) table hint to the Policy table. D. Update statistics on the Policy table. E. Execute the DBCC DBREINDEX statement on the Policy table. Answer: D. Explanation: Updating the statistics in the Policy table could make the Query optimizer use the indexes in a better way. This would increase performance. Note 1: Unexpectedly long-lasting queries and updates can be caused by: • Slow network communication. • Inadequate memory in the server computer. • Lack of useful statistics. • Out-of-date statistics. • Lack of useful indexes. • Lack of useful data striping. Note 2: An index scan is used. An index seek would be faster. Reference: BOL, Query Tuning INF: Troubleshooting Slow-Running Queries on SQL Server 7.0 or Later (Q243589) BOL, Understanding Hash Joins BOL, Table hints Incorrect answers: A: The syntax of the BETWEEN clause is correct and will return values that are true for the test expression, i.e. policy dates that are greater than or equal to 1/1/2000 and less than or equal to 3/1/2000. There is no way to rewrite the query, keep the result set, and improve the performance. Note: The BETWEEN operator specifies a range to test. The result of this test is returned as a Boolean value, i.e. TRUE, FALSE or UNKNOWN. The value returned id TRUE if the test expression is greater than or equal to the first expression and less than or equal second expression, and UNKNOWN if any input to the BETWEEN predicate is NULL. The test expression, the first expression and the second expression must all be of the same data type. The correct syntax for the BETWEEN operator is: <test expression> BETWEEN <first expression> AND <second expression> B: The exhibit indicates that a nested loop is used to join the tables, and that a clustered index scan is on the AgentPolicy table. A hash join could improve the performance of the join and eliminate the clustered index scan. The query could run faster. C: The estimated execution plan shows that a clustered index scan is going to be used, not a seek. It would be better if the query used an index seek instead. We could try to force an index seek with a table hint. However, we should use the hint index(1), not the hint index(0). Index(0): If a clustered index exists, INDEX(0) forces a clustered index scan. But the query is already using a clustered index scan, so this would be no improvement. Index(1): If a clustered index exists, INDEX(1) forces a clustered index scan or seek. This could improve the performance, but it is not listed as an option. E: Rebuilding an index could improve performance of a query that uses that index. This is particularly true if the index is fragmented. Note: DBCC DBREINDEX rebuilds all indexes defined for a table dynamically. In so doing, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and recreate those constraints. This means an index can be rebuilt without knowing the table’s structure or constraints, which could occur after a bulk copy of data into the table. DBCC DBREINDEX can also rebuild all of the indexes for a table in one statement, which is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is done by one statement, DBCC DBREINDEX is automatically atomic, while individual DROP INDEX and CREATE INDEX statements would have to be put in a transaction to be atomic. Furthermore, DBCC DBREINDEX has a greater level of optimization than can be achieved with individual DROP INDEX and CREATE INDEX statements. ... |
|||
:
Нравится:
Не нравится:
|
|||
31.03.2004, 12:51 |
|
70--229 question № 33 по TestKing 3.2
|
|||
---|---|---|---|
#18+
Потому что в estimated execution plan видно что по кластерному индексу идет операция Scan. А при наличии нормальной статистики(обновленной, адекватной...) по клавтерному индексу идет операция Seek(Поиск нужной строки). К тому же написано же для ответа Е: Rebuilding an index could improve performance of a query that uses that index. This is particularly true if the index is fragmented. ... |
|||
:
Нравится:
Не нравится:
|
|||
31.03.2004, 14:25 |
|
|
start [/forum/topic.php?fid=34&msg=32463949&tid=1551965]: |
0ms |
get settings: |
11ms |
get forum list: |
14ms |
check forum access: |
4ms |
check topic access: |
4ms |
track hit: |
177ms |
get topic data: |
12ms |
get forum data: |
3ms |
get page messages: |
49ms |
get tp. blocked users: |
2ms |
others: | 241ms |
total: | 517ms |
0 / 0 |