SQL Server 2017, 2012 Express Edition Insert Select Performance Issue with very less data

Transact-SQL
1

Command 1: (Get Count from View1)

Select Count(*) From View1

Results:
-----------
183

(1 row affected)
Execution time = 11 seconds

Command 2: (store the count in a table called Temp)

Drop Table Temp

Create Table Temp
(
  C Int
)

Insert Temp
Select Count(*) From View1

(1 row affected)
Execution time = 29 sec`

Command 3: (Get all data from View1)

Select * From View1

Results:
-----------
All Data from the view.

(183 row affected)
Execution time = 11 seconds

Command 4: (store the View1 data in a table called Temp1)

Drop Table Temp1

Create Table Temp1
(
  Columns same as View1
)

Insert Temp1
Select * From View1

(183 row affected)
Execution time = 29 sec

My Question

The SELECT in Command 1 and Command 3 is taking 11 seconds, why is INSERT SELECT in Command 2 and Command 4 taking 29 seconds, 18 seconds extra is way too much?

I have disabled Anti-Virus and Firewall. Closed Browser and all other apps. No download going on. Only SSMS is running. Only one instance of SQL Server is running. This computer is standalone, Windows 10 64 bit, 4 GB RAM. Enough disk space is free.

2

execution plan should explain you the cost.

3

I have compared the execution plan of Command 1 and Command 2. Only difference is Table Insert operator. In Command 2 there is extra Table Insert operator, all other operators are same. I think that makes sense. First build result set from SELECT and then store in Temp (Table insert operator). But problem is its taking 18 seconds extra to store just a single value.

4

hammering in 10 nails takes longer than counting the hammered in nails :wink:
inserr by nature is expensive.
also add primary key and see what happens in insert. etc

5

Add memory to the machine and your performance will improve greatly.

6

Hi Joe Terro on SQL Server Central.

10 MB data fits coolly in 4 GB RAM.

Dont' you think so?

7

It depends on what else is running. These days 4GB of ram is pretty low to be running SQL Server. There's definitely something else going on though to make it that slow. What else is running in Task Manager?

8

Nothing much.

Just SSMS and Brower, no other apps.