Home Forums SQL Server 2017 SQL Server 2017 - Development SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data. RE: SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data.

  • Sharat Gupta-482974 - Thursday, October 11, 2018 10:32 PM

    You have quoted, "944k microsecond on the table variable and 1,011k microseconds for the temp table compared to 5 seconds for the real table." As we can see temp table and table variable are close (1,011k microseconds = 1,011,000 microseconds = 1.011 seconds).  Question is, why is real table 5 seconds, approximately 5 times? If we are testing on a standalone machine with database in single user mode, real table should be as fast?

    Because SQL Server is smart enough not to actually persist the table variable/temp table to disk in those cases, because it knows how much available memory it has and that it can avoid the penalty by using spare capacity in RAM for short lived objects. In computing terms, writing to disk is fantastically slow.