1

I have two queries. First One is:

SELECT  
       Tbl.Col.value('ID[1]','INT') AS ID,   
       Tbl.Col.value('SPACE_CODE[1]', 'VARCHAR(100)') AS SPACE_CODE,  
       Tbl.Col.value('SPACE_TYPES[1]', 'INT') AS SPACE_TYPES,  
       Tbl.Col.value('IS_CORPORATE[1]', 'BIT') AS IS_CORPORATE,
       Tbl.Col.value('IS_HOTELLING[1]', 'BIT') AS IS_HOTELLING,
       Tbl.Col.value('AREA_NAME[1]', 'VARCHAR(50)') AS AREA_NAME,
       Tbl.Col.value('OPERATION[1]', 'varchar(20)') AS OPERATION,
       Tbl.Col.value('REMARKS[1]', 'varchar(200)') AS REMARKS,
       CAST('' AS VARCHAR(20)) AS RESULT,
       CAST('' AS VARCHAR(200)) AS COMMENTS
       INTO #temp_space
FROM   @XML_Data.nodes('//row') Tbl(Col)

Another query is:

CREATE TABLE #temp_space
    (
    id int identity(1,1),
    AREA_NAME VARCHAR(20),
    IS_CORPORATE BIT,
    IS_HOTELLING BIT,
    OPERATION VARCHAR(20),
    REMARKS VARCHAR(200),
    SPACE_CODE VARCHAR(100),
    SPACE_TYPES INT,
    RESULT VARCHAR(20),
    COMMENTS VARCHAR(100)
    )
 INSERT INTO #temp_space(SPACE_CODE ,SPACE_TYPES ,IS_CORPORATE ,IS_HOTELLING,AREA_NAME ,OPERATION ,REMARKS )
SELECT  
       Tbl.Col.value('ID[1]','INT') AS ID,   
       Tbl.Col.value('SPACE_CODE[1]', 'VARCHAR(100)') AS SPACE_CODE,  
       Tbl.Col.value('SPACE_TYPES[1]', 'INT') AS SPACE_TYPES,  
       Tbl.Col.value('IS_CORPORATE[1]', 'BIT') AS IS_CORPORATE,
       Tbl.Col.value('IS_HOTELLING[1]', 'BIT') AS IS_HOTELLING,
       Tbl.Col.value('AREA_NAME[1]', 'VARCHAR(50)') AS AREA_NAME,
       Tbl.Col.value('OPERATION[1]', 'varchar(20)') AS OPERATION,
       Tbl.Col.value('REMARKS[1]', 'varchar(200)') AS REMARKS,
       CAST('' AS VARCHAR(20)) AS RESULT,
       CAST('' AS VARCHAR(200)) AS COMMENTS
FROM   @XML_Data.nodes('//row') Tbl(Col)

First query is taking around 5 minutes to execute while second one is taking 3 seconds for the same number of records(around 2500).Can you please tell me why there is a difference in both the queries .

I know where to use both the queries,Just curious why the first one is taking too much time.

I need to validate each record from table table. Is there any alternative to loops and cursors.

gkarya42
  • 429
  • 6
  • 22
  • indexes on existing table? – Mitch Wheat Aug 01 '16 at 08:34
  • Actually i want to know to reason why first query is taking more time.Its not duplicate question , i have already gone through that question. – gkarya42 Aug 01 '16 at 08:42
  • post execution plan for both queries,Ideally select into should take some time more since it has create metadata all the stuff – TheGameiswar Aug 01 '16 at 08:57
  • If you switch order of execution of these queries - same result? – Ivan Starostin Aug 01 '16 at 08:59
  • With a SELECT INTO the resultset can possibly become the table automatically, because all selected data has to be valid in the table, with INSERT INTO every row has to be checked for validity to be inserted - that's just a theory for discussion. – Cato Aug 01 '16 at 09:26
  • @ Andrew Deighton: Thanks for your comment. But still not satisfied with the reason. – gkarya42 Aug 01 '16 at 09:42

1 Answers1

0

I assume the performance difference because of the loggings and table configurations.

Select INTO: The select into creates a table with minimal configuration leaving the indexes and triggers (if any) and copy the information to the target table.

Insert Into: The insert into is applicable for a table which is already existing. The table may not be as simple as Select Into configuration.

Bapaiah Malasani
  • 66
  • 1
  • 2