Database Reference
In-Depth Information
Table 12-1.
Execution time when the application was run locally (in milliseconds)
Separate
inserts
Element-Centric XML
Attribute-Centric XML
SQLBulkCopy
Table-valued
parameters
1,000 rows
356
571
377
83
58
5,000 rows
1,710
2,305
1,312
109
95
10,000 rows
3,409
4,303
2,560
200
202
100,000 rows
29,881
38,603
21,121
2,151
2,210
Table 12-2.
Execution time when the application was run remotely (in milliseconds)
Separate
inserts
Element-Centric XML
Attribute-Centric XML
SQLBulkCopy
Table-valued
parameters
1,000 rows
1,834
710
492
103
71
5,000 rows
8,150
2,520
1,378
262
248
10,000 rows
15,902
4,618
2,661
371
385
100,000 rows
143,881
39,101
21,755
2,290
2,312
Performance of the separate insert statements greatly depends on network speed. This approach introduces
a lot of network activity, and it does not perform well, especially with slow networks. The performance of the other
methods do not depend greatly on the network.
As expected, the performance of attribute-centric XML is better than element-centric XML. It will also
outperform separate inserts with the large batches—even with no network overhead involved. It is worth mentioning
that the performance of XML implementations greatly depends on the data schema. Every XML element adds another
operator to the execution plan, which slows XML parsing.
SQLBulkCopy and table-valued parameters are by far the fastest methods. Table-valued parameters are slightly
more efficient with smaller batches, while SqlBulkCopy is slightly faster with the large ones. Nonetheless, the variation
in performance is negligible.
When you work with table-valued parameters in the client code, you need to assign a
DataTable
object to a
corresponding
SqlParameter
object. The
DataTable
object should match the corresponding table type definition
from both the schema and data standpoints. The
DataTable
object should have the same number of columns, and
these columns should have the same names and be in the same order as the table type defined in the database. They
also need to support type conversions between the corresponding .Net and SQL data types.
Data in the table needs to conform to the table type primary and unique constraints, and it should not exceed the
defined column sizes and T-SQL data type's domain values.
Finally, table types should not have
sql_variant
columns. Unfortunately, the .Net SQL client does not work with
them correctly, and it raises exceptions during the call when the table-valued type has a
sql_variant
column defined.
Regular Tables in tempdb
You can create regular tables in tempdb, either directly or through the model database. User tables in tempdb are
visible in all sessions.
Tempdb is recreated every time SQL Server restarts and, because of this, it does not need to support crash
recovery. As a result, Tempdb uses the
SIMPLE
recovery model, and it has some additional logging optimizations,
which make it more efficient than logging in the user's databases.
