Exhibit: BULK INSERT Ezonexam.dbo.employees FROM ‘tk\africe\fubar512.txt’ WITH DATAFILETYPE = ‘char’, FIELD/TERMINATOR = ‘\t’, R

admin2009-05-19  62

问题 Exhibit:
BULK INSERT Ezonexam.dbo.employees
FROM ‘tk\africe\fubar512.txt’
WITH DATAFILETYPE = ‘char’,
FIELD/TERMINATOR = ‘\t’,
ROWTERMINATOR = ‘\n,’
TABLOCK

You work as database administrator at Ezonexam.com. You handle one SQL Server 2000 computer.
How should you improve the performance of the query in the exhibit above?

A. Extend the BULK INSERT statement with the BATCHSIZE option set it to equal to 5 percent of the number of rows to be loaded.
B. Extend the BULK INSERT statement with the BATCHSIZE option set it to equal to 10 percent of the number of rows to be loaded.
C. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 5 percent of the number of rows to be loaded.
D. Extend the BULK INSERT statement with the ROWS_PER_BATCH option set it to equal to 10 percent of the number of rows to be loaded.
E. Insert a line before the BULK INSERT statement that drops the indexes for the customers table. Insert a line after the BULK INSERT statement that recreates the indexes for the customers table.
F. The TABLOCK option should be removed.
G. The ROWTERMINATOR line should be removed.

选项

答案F

解析 Explanation: The TABLOCK hint increases the number of locks during the adding process. This is the reason why response time are slows down during this process. By removing the TABLOCK hint the default more granular row-level lock will be used. This would decrease the scope of the locks which would result in less waiting jobs and performance would improve.

Note: The BULK INSERT statement is used to copy a data file into a database table or view in a format specified by the user. The BULK INSERT statement accepts the TABLOCK hint, which allows the user to specify the locking behavior that the BULK INSERT statement should use. TABLOCK specifies that a bulk update table-level lock is taken for the duration of the bulk copy. If TABLOCK is not specified, the default uses row-level locks.

Incorrect Answers:
A, B, C, D: Effect would not be large
E: Some improvement, but not the best answer.
G: Would make the batch to stop working.
转载请注明原文地址:https://kaotiyun.com/show/6uhZ777K
0

最新回复(0)