Ygthkb

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

What species should be used for storage of human minds?

Why does 0.-5 evaluate to -5?

How can find the 2D Voronoi cell area distribution?

XOR-free sets: Maximum density?

Kernel and image of matrix: What are they? Why do they exist?

Is there a way to pause a running process on Linux systems and resume later?

What's the reason that we have a different number of days each month?

When using Volatility with a memory image, what is the Kernel version?

Count repetitions of an array

If I tried and failed to start my own business, how do I apply for a job without job experience?

What does からか mean?

What would be some possible ways of escaping higher gravity planets?

How to deal with an underperforming subordinate?

What does an unprocessed RAW file look like?

How bad is a Computer Science course that doesn't teach Design Patterns?

Why didn't Tom Riddle take the presence of Fawkes and the Sorting Hat as more of a threat?

Why is Shelob considered evil?

Is it legal to point a domain to someone else's ip (website)?

Identical projects by students at two different colleges: still plagiarism?

How do dictionaries source attestation?

Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?

If angels and devils are the same species, why would their mortal offspring appear physically different?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Single-row INSERT…SELECT much slower than separate SELECT

0

Given the following heap table with 400 rows numbered from 1 to 400:

DROP TABLE IF EXISTS dbo.N;

GO

SELECT 

    SV.number

INTO dbo.N 

FROM master.dbo.spt_values AS SV

WHERE 

    SV.[type] = N'P'

    AND SV.number BETWEEN 1 AND 400;

and the following settings:

SET NOCOUNT ON;

SET STATISTICS IO, TIME OFF;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following SELECT statement completes in around 6 seconds (demo, plan):

DECLARE @n integer = 400;



SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

When the single-row output is written to a table, it takes 19 seconds (demo, plan):

DECLARE @T table (c bigint NOT NULL);



DECLARE @n integer = 400;



INSERT @T

    (c)

SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

The execution plans appear identical aside from the insert of one row.

All the extra time seems to be consumed by CPU usage.

Why is the INSERT statement so much slower?

sql-server query-performance execution-plan

share

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

add a comment | 

0

Given the following heap table with 400 rows numbered from 1 to 400:

DROP TABLE IF EXISTS dbo.N;

GO

SELECT 

    SV.number

INTO dbo.N 

FROM master.dbo.spt_values AS SV

WHERE 

    SV.[type] = N'P'

    AND SV.number BETWEEN 1 AND 400;

and the following settings:

SET NOCOUNT ON;

SET STATISTICS IO, TIME OFF;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following SELECT statement completes in around 6 seconds (demo, plan):

DECLARE @n integer = 400;



SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

When the single-row output is written to a table, it takes 19 seconds (demo, plan):

DECLARE @T table (c bigint NOT NULL);



DECLARE @n integer = 400;



INSERT @T

    (c)

SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

The execution plans appear identical aside from the insert of one row.

All the extra time seems to be consumed by CPU usage.

Why is the INSERT statement so much slower?

sql-server query-performance execution-plan

share

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

add a comment | 

Given the following heap table with 400 rows numbered from 1 to 400:

DROP TABLE IF EXISTS dbo.N;

GO

SELECT 

    SV.number

INTO dbo.N 

FROM master.dbo.spt_values AS SV

WHERE 

    SV.[type] = N'P'

    AND SV.number BETWEEN 1 AND 400;

and the following settings:

SET NOCOUNT ON;

SET STATISTICS IO, TIME OFF;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following SELECT statement completes in around 6 seconds (demo, plan):

DECLARE @n integer = 400;



SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

When the single-row output is written to a table, it takes 19 seconds (demo, plan):

DECLARE @T table (c bigint NOT NULL);



DECLARE @n integer = 400;



INSERT @T

    (c)

SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

The execution plans appear identical aside from the insert of one row.

All the extra time seems to be consumed by CPU usage.

Why is the INSERT statement so much slower?

sql-server query-performance execution-plan

share

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

DROP TABLE IF EXISTS dbo.N;

GO

SELECT 

    SV.number

INTO dbo.N 

FROM master.dbo.spt_values AS SV

WHERE 

    SV.[type] = N'P'

    AND SV.number BETWEEN 1 AND 400;

SET NOCOUNT ON;

SET STATISTICS IO, TIME OFF;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @n integer = 400;



SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

DECLARE @T table (c bigint NOT NULL);



DECLARE @n integer = 400;



INSERT @T

    (c)

SELECT

    c = COUNT_BIG(*) 

FROM dbo.N AS N

CROSS JOIN dbo.N AS N2

CROSS JOIN dbo.N AS N3

WHERE 

    N.number <= @n

    AND N2.number <= @n

    AND N3.number <= @n

OPTION

    (OPTIMIZE FOR (@n = 1));

share

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

share

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

asked 3 mins ago

Paul White♦Paul White

52.2k14279452

asked 3 mins ago

Paul White♦Paul White

52.2k14279452