Performance issue with Select into or Insert Into

[FC2003]

Hi,

I'm doing select statements on large tables (over 4 millions rec.) so performance is a big factor.

I have two select statement who are doing the same thing except the first one is not permitting me to update an existing table, it has to create it each time.

The second one is slower but permit me to update an existing table.

I always Truncate the table before lauching those select in a DTS package.

The first one wich is much faster is:

select top 100 PERCENT C.MANDT, C.MATNR, C.DATBI,C.DATAB, C.KNUMH into SAP_Repl.dbo.A980_test
from sbrosql1.SAP_Repl.dbo.MARA as B

inner join sbrosap2.PRD.dbo.A980 as C
on B.MATNR = C.MATNR
Where C.MANDT='100' and C.KAPPL='V' and C.KSCHL='Z020' and C.DATBI > '2004'

The second one wich permit me to update the table is:

INSERT INTO SAP_Repl.dbo.A980_test
(MANDT, MATNR, DATBI, DATAB, KNUMH)

select top 100 PERCENT C.MANDT, C.MATNR, C.DATBI,C.DATAB, C.KNUMH
from sbrosql1.SAP_Repl.dbo.MARA as B
inner join sbrosap2.PRD.dbo.A980 as C
on B.MATNR = C.MATNR
Where C.MANDT='100' and C.KAPPL='V' and C.KSCHL='Z020' and C.DATBI > '2004'

What is the performance difference with those 2 select statements and is it possible to use the first one without having to drop the table each time ?

 

[JohnDTampaBay]

SELECT...INTO is a non-logged command whereas INSERT...SELECT is a logged command. The first query is equivalent to a BULK INSERT. In the second query, every row written to the SAP_Repl.dbo.A980_test table is also written to the transaction log.

Incidentally, do you really need TOP 100 PERCENT in your query? IMHO, I don't believe it will make a difference in the examples given. Just my two cents. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[FC2003]

Hi John,

thanks for the info, No I dont really need the 100 PERCENT, I will remove it.

Non log operations will be better for me because we refresh this table every day so it is not really necessary to log it all, if it fails we will resynchronise it.

But is there a way to built the select into in a way that it will fill an already created table (wich has been trucated-no record in it). Because when I launch it the second time it failed because it says that there is already a database object named A980_test.

Thanks

 

[JohnDTampaBay]

Nope, that's just the way those commands work. You can DROP the SAP_Repl.dbo.A980_test table rather than TRUNCATE the table before running the package. Or include the following line just prior to your SELECT...INTO:

IF EXISTS(SELECT * FROM SAP_Repl.Information_Schema.Tables
WHERE Table_Name = 'A980_test')
DROP TABLE SAP_Repl.dbo.A980_test


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)