Union all in View Performance issue in SQL Server

Question

I have 40 Tables - Table1, Table2 , Table3 ... Table40, all having same columns . These tables contains distinct data and to identify each table's data , I have a column Reporting_Type which have different value for each table.

For example: column Reporting_Type of Table1 has value Reportin_Type1 and so on.

Please note that each table contains 2-3 million records.

I need to create a view which combines the data from all the tables.

I have simply applied UNION ALL and put the following query :

CREATE VIEW ALL DATA 
AS
   SELECT 
       COLUMN1, COLUMN2, COLUMN3, REPORTING_TYPE ..., COLUMN 40 
   FROM 
       TABLE1

   UNION ALL

   SELECT 
       COLUMN1, COLUMN2, COLUMN3, REPORTING_TYPE ..., COLUMN 40 
   FROM 
       TABLE2

   UNION ALL 

   SELECT 
       COLUMN1, COLUMN2, COLUMN3, REPORTING_TYPE ..., COLUMN 40 
   FROM 
       TABLE3

   ....

   SELECT 
       COLUMN1, COLUMN2, COLUMN3, REPORTING_TYPE ..., COLUMN 40 
   FROM 
       TABLE40

The above query takes a lot of time to fetch the data from all the tables.

Could any please suggest any performance tuned query or any other way to create this view in SQL Server 2012?

Apologies if any one find this question too Naive. I am new to database. Kindly let me know if more information is required.

That might be a problem. Consider to add indexes. Also `dirty reads` might help. Try using dirty reads with table hint `WITH (NOLOCK)` (f.e. `FROM TABLE40 WITH (NOLOCK)`) or adding `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` when running view. Also take a look at indexed views ([msdn](https://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx)) — gofr1, May 14 '17 at 15:08
Take a look to this question/answers https://stackoverflow.com/questions/10025569/improve-sql-server-query-performance-on-large-tables — gofr1, May 14 '17 at 15:14
Thanks Alot for the answer Sir. But I do not understand how indexes on table help getting the data in view faster as there is no 'where'clause I am using.It is just plan . Select * from table_name query. — Harsh, May 15 '17 at 16:49
If you got no clustered index on a table there will be table scan, if you got clustered index - there will be clustered index scan that is much faster on big tables (f.e. 1 million rows). Not matter with or without WHERE. Make two dummy tables with 2 million rows in each, one with clustered index, another without and run SELECT all from each. Take a look on the execution plans and compare execution time. — gofr1, May 15 '17 at 18:42
And another point: why do you need this view? You will run SELECT...WHERE on it? If so, consider using table-valued function with unions and WHERE somecolumn=@somevaroable, instead of view. Indexes will do much help on this one. And if you need to show this data in some app, consider of reading tables by parts. At first return top 1000, then if user need more data show next 1000 records etc. — gofr1, May 15 '17 at 20:00
Thanks for taking your time out and helping me @gofr1 . Kindly note that I am not including any where clause in this . All 40 queries are just simple 'select * from table(n)'. I think View would be the best option for it . — Harsh, May 16 '17 at 09:32

score 0 · Answer 1 · answered Aug 13 '17 at 18:22

I kindly recommend Clustered Indexes on those table, so should have primary key in all of the tables, if there is a primary key with those tables, try to rebuild or reorganize indexes,to rebuild and reorganize you could also refer below script. Without any cluster index and where condition it would be scan but with Clustered index you would get better performance.

-- Make Sure you have write USE <databasename> statement before executing statement.  
-- USE <databasename>
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(500);   
DECLARE @objectname nvarchar(500);   
DECLARE @indexname nvarchar(500);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

        -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  OR @frag >= 5.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0 OR @frag < 5.0 
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

I also recommend to use cross apply, which is explained in StackOverflow's another thread

Union all in View Performance issue in SQL Server

1 Answers1