July 5, 2017 at 5:21 am
I have the following setup
2 linked servers - on one server (sql2014) is rather complicated view - 5400 rows
the view is stacked it takes data from views and tables to pull data into one place
If I ask the view to return all the rows it takes 4 to 6mins - a workable number
Every 15 minutes the other sever picks up the view - dumps the contents into a temporary table
then refreshes another table that I'm running reports from.
Up until last Thursday - this worked - I had a stored procedure doing the legwork
Fri day morning it started timing out - due to the length of the time the query was taking (10 mins)
No one tells me till yesterday when of course it's urgent
I increased the timeout to 30 mins - still never finishes
I spent 5 hours last night going round in circles
In the end, I've excluded the linked server and created a newDB and I'm
truncate table newDB
insert into newDB
(loads of fields)
select * from myview
order by code, levc, sesc
If I run select * from myview
it returns all the rows in 6mins
If I run the insert query - It's still running after 45mins
I'm looking for ideas
Thanks in advance
Martyn
July 5, 2017 at 5:28 am
Try insert without ORDER BY
_____________
Code for TallyGenerator
July 5, 2017 at 5:42 am
You mention that you have 2 linked servers.
Where's newDB, myview and its underlying tables? Where are you running the code from?
July 5, 2017 at 6:19 am
somone already mentioned that the order by , when inserting into a table is an extra cost with no benefit;(a table has no implicit order...you have to use an order by from the table )
here's my immediate suggestion :don't use four part names on linked servers. ie MyLinkedServer.DatabaseName.dbo.MyView.
if you do that, the query of the view is converted to the underlying view definition with all the joins, and then the underlying tables inside the view are pulled into your local tempdb, ...and then the join is occurring, then any filtering in the view is applied , and finally results are returned...you are bringing over a suite of tables behind the scenes, which is why it is slow.
What you want and expect is to let the remote server do the work, and pull over only the actual results.
to do that, you instead use openquery and get just the results:
truncate table newDB
insert into newDB (loads of fields)
SELECT * FROM OPENQUERY([MyLinkedServer],'select (loads of fields) from Databasename.dbo.myview')
Lowell
July 5, 2017 at 7:35 am
I've majorly simplified things - I'm doing all on one server now
Below is the sql
If I run - sipr.dbo.grid_view_dataset it returns the rows in 2mins 40 secs
I just cancelled the query below when it had run for 1 3/4 hours
Removed the order by
Martyn
--clear the actual table
truncate table gridtemp.[dbo].[grid_view_dataset_holding]
--populate the actual table
insert into gridtemp.[dbo].[grid_view_dataset_holding] (
<snip>
lots of field names removed to make it easier to read
<snip>
)
select * from sipr.dbo.grid_view_dataset
--order by spr_code, smr_levc, smr_sesc
--update the refresh date/time
Update gridtemp.[dbo].[grid_view_dataset_holding]
set LastRefresh = getdate()
July 5, 2017 at 8:05 am
You have it simplified, but what about the linked server? Is that still a part of the equation? You haven't provided any detail on that....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 8:12 am
sorry - I thought it might be the server link - It's not - it all happens on the same server 2 databases
1 with the view and the other being the target
Martyn
July 5, 2017 at 8:32 am
July 5, 2017 at 8:39 am
martyn.smith-863492 - Wednesday, July 5, 2017 8:12 AMsorry - I thought it might be the server link - It's not - it all happens on the same server 2 databases
1 with the view and the other being the target
Martyn
Then I wonder if you have something else going on at the same time that may be blocking your query from running. How about updating statistics on the underlying tables? Another potential source of grief could be severely fragmented indexes, but that should have started being noticeable earlier;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 8:51 am
I'd look at the indexing and SQL code in the view; it could be using non-Sargable WHERE statements or join criteria, inline scalar functions, or just not have any indexing that support the view;
The actual execution plan for the select * from view would tell us volumes.
You also mentioned views that select form other views...that's most likely a critical pain point to look at as well, eliminating the sub-views.
Lowell
July 5, 2017 at 9:18 am
sgmunson - Wednesday, July 5, 2017 8:39 AMThen I wonder if you have something else going on at the same time that may be blocking your query from running. How about updating statistics on the underlying tables? Another potential source of grief could be severely fragmented indexes, but that should have started being noticeable earlier;
the bit that is blowing my mind is if I do a select on the view - it returns all the rows in under 3 mins
but if I take that select and try to insert it elsewhere it doesn't complete
I've also tried Lowell's
select * from OPENQUERY([server], 'select * from sipr.dbo.grid_view_dataset')
It times out after 30mins (the time out I set)
Martyn
July 5, 2017 at 9:46 am
execution plan attached
July 5, 2017 at 9:47 am
martyn.smith-863492 - Wednesday, July 5, 2017 9:18 AMsgmunson - Wednesday, July 5, 2017 8:39 AMThen I wonder if you have something else going on at the same time that may be blocking your query from running. How about updating statistics on the underlying tables? Another potential source of grief could be severely fragmented indexes, but that should have started being noticeable earlier;
the bit that is blowing my mind is if I do a select on the view - it returns all the rows in under 3 mins
but if I take that select and try to insert it elsewhere it doesn't completeI've also tried Lowell's
select * from OPENQUERY([server], 'select * from sipr.dbo.grid_view_dataset')
It times out after 30mins (the time out I set)Martyn
If OPENQUERY can be used, then you are going across a linked server, which you earlier indicated was not the case. You should NOT be using a linked server if the source and destination databases are on the same server and same SQL instance. How many rows are in the sipr.dbo.grid_view_dataset table?
P.S. Your post is missing its attachment.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 9:55 am
OK,
try
SELECT *
INTO #Table
FROM View
WHERE 1=0
_____________
Code for TallyGenerator
July 5, 2017 at 10:05 am
0 rows effected straight away
straight sqlplan is too large so zipped version attached
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply