Improving performance of a query.

Question

Post reply

Improving performance of a query.

Jim Covington

Old Hand

Points: 348
More actions
May 27, 2016 at 1:20 pm

#319046

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Look up Index View in Books Online.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 2

Jim Covington (5/27/2016)
Is there anything I can do to improve the performance of my query?

You need to create indexes.

Alex Suprun

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

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.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001