I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an
INSERT INTO is taking like 20 minutes while doing a
SELECT INTO is taking seconds. The DB statistics are up to date and we are running daily maintenance plans so not sure what is going on. I know that
SELECT INTO bypasses the logs which would make it faster, but I as not assuming that the
INSERT INTO would be so much worse.
EDIT: Based on comments adding some more details
Why would the INSERT INTO be taking so much longer than the
SELECT INTO for a temp table.
Query example below. I am shredding XML and inserting into a temp Table I have both the
INSERT INTO and
SELECT INTO commented out. The
INSERT INTO for just 100 records is showing 382,000+ logical reads while the
SELECT INTO is showing none.
;with cteScen AS ( SELECT DENSE_RANK() OVER ( ORDER BY scenario_node ) AS scenario , RANK() OVER ( PARTITION BY scenario_node ORDER BY scen_val ) AS level_order , scen_val.value('text-value', 'varchar(100)') AS 'scen_value' , scen_val.value('(distribution/@id)', 'int') AS 'dist' FROM @scenarioXML.nodes('/scenarios/scenario') AS scenario ( scenario_node ) CROSS APPLY scenario.scenario_node.nodes('./values/value') AS x ( scen_val )) --INSERT INTO #tmpTbl(Scenario, Level_Order, Scenario_Value, Distribution_ID) Select top 100 * --INTO #tmpTbl From cteScen