May 21, 2014 at 7:34 am
Original View
CREATE VIEW TestView
AS
SELECT
Name,
Account Number,
ProcessDate,
Amount
FROM TABLE1
The above was working fine,
Modified View
CREATE VIEW TestView
AS
SELECT
Name,
Account Number,
ProcessDate,
Amount
FROM TABLE1
UNION ALL
SELECT
Name,
Account Number,
ProcessDate,
Amount
FROM TABLE2
After the modification the view is performing very poorly unexpected behavior.
Issue: When I try to select the information using the views, Which is taking long time to produce the results(30+mins) using the UNION ALL on the view, without Union All and the second table the results are coming quickly.
Below are approaches taken to improve the view:
Verified the Indexes, Both tables are having Clustered Index on ProcessDate and Non Clustered Index on Account_Number and ProcessDate.
To use the proper indexes during the data retrieval, I have created the separate views for both table and combined both and derived the new view. Then used the combined view, there is no improvements.
Checked the execution plan of the existing view(TestView), Clustered Index Scan is appearing on the TABLE1 with cost of 99% and ProcessDate clustered index is used scan all the rows.
Applied the Update Statistics to the Table1.
Instructed the view to use the indexes forcibly using index hints.
Now I am struck to improve the performance of the view and defrag percentage of the table is morethan 30% on TABLE1 and it has 80+ million records
Please let me know your suggestions to improve the view.
May 21, 2014 at 7:41 am
1) You have no filter in the views. Are you using one in the query? If so, what is your code?
2) What is the plan that is chosen for each run?
3) ANY time you add in more data to a query like you are doing you could be doing (MUCH) more work.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2014 at 1:06 pm
The same question was posted over on AskSSC. The answer is the same here. There's no filtering of any kind, so you're guaranteed to get scans. If you're running some type of WHERE clause against the view when you call it, how are you applying that WHERE clause, to which columns, etc.? Can you capture an execution plan in order to understand what's happening on your server?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 21, 2014 at 11:53 pm
if its scan going on your query that means you can further look for indexes to create. plus also update the statistics.
also try tuning adviser recommendation for whatever query you are running (not blindly) it will help u.
May 22, 2014 at 6:58 am
srikantmeister (5/21/2014)
if its scan going on your query that means you can further look for indexes to create. plus also update the statistics.also try tuning adviser recommendation for whatever query you are running (not blindly) it will help u.
With no WHERE clause to filter on, creating more indexes won't do a thing for improving performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 22, 2014 at 8:14 am
After the modification the view is performing very poorly unexpected behavior.
So this view was implemented to facilitate some partitioning strategy. There are many scenarios where partitioning, especially paritioned views referencing multiple tables, results in poor performance. If this was an attempt at performance optimization, then reconsider this decision to partition the tables.
Issue: When I try to select the information using the views, Which is taking long time to produce the results(30+mins) using the UNION ALL on the view, without Union All and the second table the results are coming quickly.
Understand the difference between UNION versus UNION ALL.
Verified the Indexes, Both tables are having Clustered Index on ProcessDate and Non Clustered Index on Account_Number and ProcessDate.
Those indexes won't help the performance of this view.
To use the proper indexes during the data retrieval, I have created the separate views for both table and combined both and derived the new view. Then used the combined view, there is no improvements.
Splitting the view into seperate views won't buy you anything in terms of performance and will probably make it worse.
TABLE1 and it has 80+ million records
If you must keep this data partitioned into seperate tables, then the only index that might help is a non-clustered index on the following:
Name,
Account Number,
ProcessDate,
Amount
This would be called a "covering index" and may perform better than a table scan, if there are a lot of other columns in this table.
But really the best decision may be to fall back to having your data in one table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 22, 2014 at 11:38 pm
i guess information is limited thats why everyone is guessing.
can you please post the actual way you are running the query for view.
view structure is given, we want query which you are executing and the table structure to help in this case further.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply