HELP copying from TEmp tables - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

HELP copying from TEmp tables

Author

Topic

chikku6
Starting Member

9 Posts

Posted - 2014-04-02 : 10:42:22

we are trying to copy data from a temporary table to a new table. before copying data we delete all the data thats common to both the tables and reinsert the new values from the new table.
below is the stored procedure we use to do that ? can someone point out a better way to do it as its taking way too long.

DECLARE @whereClause VARCHAR(1024)
DECLARE @columns VARCHAR(1024)
DECLARE @i INT
SET @whereClause = ''
SET @columns = 'state'
SET @i = 0
WHILE (@i < @dimensions)
BEGIN
IF @i <> 0
BEGIN
SET @whereClause = @whereClause + ' AND '
END
SET @whereClause = @whereClause + '[dbo].[' + @tempTable + '].[d' + CAST(@i AS varchar) + '] = [dbo].[Data].[d' + CAST(@i AS varchar) + ']'
SET @columns = @columns + ', '
SET @columns = @columns + 'd' + CAST(@i AS varchar)
SET @i = @i + 1
END

/* Set up SQL commands */
DECLARE @delCmd VARCHAR(1024)
SET @delCmd = 'DELETE [dbo].[Data] FROM [dbo].[Data] INNER JOIN [dbo].[' + @tempTable + '] ON ' + @whereClause
DECLARE @insertCmd VARCHAR(1024)
SET @insertCmd = 'INSERT INTO [dbo].[Data] (' + @columns + ', data) SELECT '+ @columns +', data FROM [dbo].[' + @tempTable + ']'

we execute the delcmd and insertcmd.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-02 : 11:33:06

What is taking a long time: The delete or the insert?

What indexes do you have on your temp table(s)? What indexes do you have on your Data table? More specifically, do you you have indexes on the joining columns? How much data are we talking about: Millions, 100's if Millions or Billions of rows?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-02 : 11:33:37

One think crosses my mind: MERGE stmt.

Question is about the @whereClause



MERGE [dbo].[Data] as T
 USING (SELECT ID,someOtherColumns FROM TempTable) as S
  ON (T.ID=S.ID)
WHEN MATCHED THEN --you can add extra condition to delete ; see BOL
   DELETE 
WHEN NOT MATCHED BY TARGET THEN
   INSERT ....

sabinWeb MCP

chikku6
Starting Member

9 Posts

Posted - 2014-04-02 : 11:54:11

The data is just a few thousands. It takes about 10 seconds to load about 30000 rows. we use a clustered index. we do have a guid column as well (unique identifier). the primary key is a clustered index of 8 columns.(yes quite complicated). we use the inner join and i guess that will use the primary key in turn. both the tables have the same schema.

Merge would not work as we don have much to do on the matching condition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources