insertion process is very slow.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insertion process is very slow.

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-13 : 04:51:19

I need to insert 100000 records in one of my table. while i am trying to insert 100 records at first. the query is taking 1 min to run. how to fast up the query?

WITH x AS
(
select c1,c2,RowNum=ROW_NUMBER() over(order by RowID) from
t1
)
INSERT INTO t2
(c1,c2)
SELECT c1,c2 FROM x Where x.RowNum between 1 and 100

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-13 : 05:55:02

Hi subhaoviya,
I am not sure but why are u using the ROW_NUMBER() function in ur query ,I beleive u have RowID as distinct column in table.
If u want to put 100 records u can either use Top 100
or u can use while loop for inserting in batches.

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-13 : 07:08:34

that RowID is not an unigue column. the table have column level duplicates also.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 07:23:10

It may be the CTE that takes the time rather than the insert itself. But, I am only guessing - it could be that T2 has lot indexes or something that is causing the inserts to be slow.

In any case, you can do the following experiment. Create at temp table with an identity column and insert all the data into that table. If that goes relatively smooth, then you can insert from the temp table to T2, all at once, or even in chunks easily because in the temp table you have the identity column that will tell you which rows to insert.

I must admit that I am shooting from the hip, so please feel free me to shoot me down

CREATE TABLE #tmp
(	c1 INT, c2 INT, rowID int, 
	OrderingId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
);

INSERT INTO 
	#tmp 
SELECT 
	c1,c2,rowId FROM t1
ORDER BY RowId;

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-13 : 07:42:31

quote:
Originally posted by subhaoviya

that RowID is not an unigue column. the table have column level duplicates also.

That doesn't matter, the way you specified ROW_NUMBER() won't distinguish between them any more or less than TOP would:

INSERT INTO t2(c1,c2)
SELECT TOP 100 c1,c2 FROM t1 ORDER BY RowID

If RowID is indexed this should be quicker as it will be limited to 100 rows, where the original version may have materialized more than that and then filtered it. Worst case should be no slower than the CTE. Can't say how it will scale up to 100K rows.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-13 : 07:53:06

Rob, I think subhaoviya is trying to test this so he can repeat it for rows 101-200, 201-300 and so on.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-13 : 08:57:31

I believe this should work

INSERT INTO newtablename (c1,c2)

Select c1,c2 from
(select c1,c2,
row_number() over
(partition by <All columns which u want to find distinct from > order by <columnname>)
FROM tablename) as cnt
WHERE cnt=1

-- This would give u only distinct records ( make sure partition by should be used properly)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-07-13 : 08:59:04

Does table t2 have any triggers on it?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources