May 27, 2016 at 1:20 pm
I have a view that is on top of a dozen tables. The tables are yearly files. The view is a Union All of each table.
I query this view and have a where xxx to get a subset of the rows. My present query is slow.
In the IBM OS world, there is a view called a logical file. With a Logical file you can derive a subset of records and sort in the order you need. This Logical file is basically an index of a physical file that is maintained dynamically by the OS. In short, when the logical is opened it is quick because it doesn't have to process the query.
Is there a similar object or concept like this in Microsoft SQL?
Is there anything I can do to improve the performance of my query?
Jim
May 27, 2016 at 1:29 pm
Look up Index View in Books Online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 27, 2016 at 3:21 pm
Jim Covington (5/27/2016)
Is there anything I can do to improve the performance of my query?
You need to create indexes.
May 27, 2016 at 8:19 pm
Jim Covington (5/27/2016)
In the IBM OS world, there is a view called a logical file. With a Logical file you can derive a subset of records and sort in the order you need. This Logical file is basically an index of a physical file that is maintained dynamically by the OS. In short, when the logical is opened it is quick because it doesn't have to process the query.Is there a similar object or concept like this in Microsoft SQL?
What you are describing is, in the Microsoft world, an ldexed view. It's a physical index created against view instead of the underlying SQL tables. Unfortunately you cannot use UNION ALL in an indexed view so that is not an option.
Is there anything I can do to improve the performance of my query?
The way the query is usually processed in SQL Server is: the SQL engine executes the queries that are unioned first then uses a concatenation operator to concatenate the result sets of each query. The concatenation is very fast, it's one or more of the unioned queries that's slowing you down. Post the actual execution plan here and we can help get to the bottom of what's slowing you down.
The tables are yearly files.
If the yearly data for previous years does not change then you could create a SQL job or ETL process that queries that data using UNION ALL and copies that result set into a new table. Then index that table as needed. Just a thought - there's not enough info to say if this is a good idea.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply