1
\$\begingroup\$

I have written the below to populate/create tables (t_) from all of the corresponding views (v_) in my database. I converted a previous script from using a cursor to improve running speed. Is there anything else I can do to make this more performant.

Any help is appreciated.

Please see code below.

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[P_populate_tables_from_views]    Script Date: 22/11/2016 16:15:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[P_populate_tables_from_views] 

@dropTables BIT

AS

DECLARE @viewName NVARCHAR(100);
DECLARE @formattedTableName NVARCHAR(100);
DECLARE @dropTableSql NVARCHAR(MAX);

DECLARE @viewId int

SET @viewId = (SELECT MIN(object_id) 
FROM sys.views)


WHILE @viewId IS NOT NULL
BEGIN

SET @viewName = (SELECT name FROM sys.views where object_id = @viewId)

PRINT 'Time: ' + CONVERT(varchar, SYSDATETIME(), 121)
PRINT('Processing ' + @viewName)
SET @formattedTableName = REPLACE(@viewName,'v_','t_');


IF @dropTables = 1
BEGIN
    SET @dropTableSql = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@formattedTableName+''')) DROP TABLE '+@formattedTableName
    EXEC (@dropTableSql);
END

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @formattedTableName))
    BEGIN
        EXEC('TRUNCATE TABLE '+@formattedTableName)
        EXEC('INSERT INTO '+@formattedTableName +' SELECT * FROM '+ @viewName) 
    END
    ELSE
    BEGIN
        EXEC ('SELECT * INTO '+@formattedTableName+' FROM '+@viewName);
    END
SET @viewId = (SELECT MIN(object_id) 
FROM sys.views WHERE object_id > @viewId)
END
\$\endgroup\$
  • \$\begingroup\$ EXEC() won't help SP performance, AFAIK (can't say how much in this case without a decent benchmark). Why don't you drop it? \$\endgroup\$ – Adriano Repetti Nov 23 '16 at 10:13
  • \$\begingroup\$ @AdrianoRepetti Supposedly truncate table is better for performance than fully dropping tables - or so I have read. Just wondered if there was anything syntax wise that could be obviously improved. \$\endgroup\$ – IRGUY Nov 23 '16 at 10:21
  • \$\begingroup\$ I had to use formatting, I mean why don't you drop EXEC, I didn't mean to use DROP (yes, TRUNCATE when applicable is much faster.) BTW I assume you do not have to worry about permissions (you may not even need to give exec permission to this procedure) \$\endgroup\$ – Adriano Repetti Nov 23 '16 at 10:26
  • \$\begingroup\$ @AdrianoRepetti apologies misread that. There shouldn't be any issue with permissions. So you would recommend dropping the exec to increase speed? \$\endgroup\$ – IRGUY Nov 23 '16 at 10:35
  • \$\begingroup\$ Yes, it should because (AFAIK but I'm not SQL Server expert) EXEC() won't reuse execution plans. Now, in this case I suppose that saved time is negligible then it may (or it may not) be advantageous. In this regard also take a look to sp_executesql on MSDN (simple drop-in). Side note: when possible it's always nice to give as little permissions as possible to each procedure (to avoid catastrophic error, unwanted side effects and/or security issues if, in future, you will add more parameters...) \$\endgroup\$ – Adriano Repetti Nov 23 '16 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.