July 5, 2017 at 10:32 am
There's a LOT of stuff going on in that view. The execution plan shows the following for just a single FILTER operation, which appears to be doing a lot of work just to eliminate a single condition. I suspect that the view itself is problematic, or that the difference in execution time is due to a linked server issue. As you report that you get reasonable execution time on the view via SSMS, consider whether or not you're executing that query directly on the server itself. We need more information on exactly how the linked server plays into this scenario. It would also be handy to have the definition of that view, as it appears that it might be able to be simplified... and another possibility is that the view might just reference something across a linked server. I'm doubtful we can do much more with this without seeing the actual view definition.
CASE
WHEN CONVERT(decimal(4,1), [Expr1478], 0) >= (360)
AND (CONVERT(decimal(4,1), [Expr1467], 0) + CONVERT(decimal(4,1), [Expr1473], 0) + CONVERT(decimal(4,1), [Expr1477], 0) +
CONVERT(decimal(4,1), [Expr1469], 0) + CONVERT(decimal(4,1), [Expr1471], 0) + CONVERT(decimal(4,1), [Expr1475], 0)) >= (300.0)
AND (CONVERT(decimal(4,1), [Expr1470], 0) + CONVERT(decimal(4,1), [Expr1474], 0)) >= (225.0)
AND CONVERT(decimal(4,1), [Expr1474], 0) >= (105.0)
AND CONVERT(decimal(4,1), [Expr1475], 0) >= (90.0)
AND CONVERT(decimal(4,1), [Expr1495], 0) <= (45.0) THEN 'Degree'
ELSE
CASE
WHEN (CONVERT(decimal(4,1), [Expr1467], 0) + CONVERT(decimal(4,1), [Expr1473], 0) + CONVERT(decimal(4,1), [Expr1477], 0) +
CONVERT(decimal(4,1), [Expr1469], 0) + CONVERT(decimal(4,1), [Expr1471], 0) + CONVERT(decimal(4,1), [Expr1475], 0)) >= (240.0)
AND (CONVERT(decimal(4,1), [Expr1471], 0) + CONVERT(decimal(4,1), [Expr1475], 0)) >= (90.0) THEN 'Diploma of Higher Education'
ELSE
CASE
WHEN (CONVERT(decimal(4,1), [Expr1467], 0) + CONVERT(decimal(4,1), [Expr1473], 0) + CONVERT(decimal(4,1), [Expr1477], 0) +
CONVERT(decimal(4,1), [Expr1469], 0) + CONVERT(decimal(4,1), [Expr1471], 0) + CONVERT(decimal(4,1), [Expr1475], 0)) >= (120.0) THEN 'Certificate of Higher Education'
ELSE
CASE
WHEN CONVERT(decimal(4,1), [Expr1478], 0) < (360) THEN 'Incomplete'
ELSE 'Uh-oh'
END
END
END
END <> 'Incomplete'
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 10:40 am
The view definition is dreadful - it pulls al sorts of information from multiple tables.
But I still don't understand - I run a select on the view - quick response
I run a select on a view and try to feed it into another table - it never finishes
It's not due to the linked server issue - I've moved closer - no linked server involved any more
I'm away from by desk now so I can't place the view definition
Martyn
July 5, 2017 at 10:51 am
Until we see that definition, there's very little we can do with it. I still suspect you have a linked server issue that you may simply not know exists. The view definition might be referring to a linked server, which might well be a part of the problem. We also don't know exactly how you are connecting to the server from SSMS. Are you remoting into the server and running SSMS directly on the server? Or are you just connecting via SSMS on your workstation to the server? Turning that query into an insert is causing a problem, but we won't know what exactly is going on until we know exactly how you're connected to the server when you run it, as well as exactly what this view is doing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 11:05 am
yeah now that we see the actual execution plan, we can see the impact of the nested views; there's a couple of tables like [ins_smr] and [ins_mod] that are joined into the query at least ten times, i stopped looking for it after finding both included in four different plan tree branches.
i'd consider peeling the onion back,and eliminating the views piece by piece , i can see it's not a small job though.
SQL Sentry Plan Explorer makes it a bit easier to see the plan, consider grabbing that free product and looking at it on your side as well.
Lowell
July 5, 2017 at 11:34 am
martyn.smith-863492 - Wednesday, July 5, 2017 10:05 AM0 rows effected straight away
straight sqlplan is too large so zipped version attached
Since Sergiy's suggestion worked rather quickly, I am wondering if it is not entirely the view's fault for the slow execution.
Biulding on Sergiy's suggestion, what about if you do:SELECT *
INTO #Table
FROM View
I am curious if you have some triggers or constraints that are giving you a bit of headache on that grid_view_dataset_holding table?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 5, 2017 at 11:41 am
There's an off chance the slowness has to do with log space, particularly if not in tempdb.
How much total space is the new table? If it's large, make sure the log file has enough pre-allocated space to handle the full insert. This is far less likely to be an issue in tempdb, although there's still some chance it could be. If there is not enough log space, having to allocate and pre-format new log space during the INSERT would drastically slow it down. Similarly, if the log is set to grow at some very small amount, say 1-2MB, you might have the same symptoms.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 6, 2017 at 9:31 am
SELECT
*
INTO gridtemp
FROM sipr.dbo.grid_view_dataset
I cancelled after 15 mins
SELECT * FROM [sipr].[dbo].[grid_view_dataset]
runs in 2 mins 34 seconds
I'm doing both of these running them on a copy of sql management studio logged in as sa.
I am connect to the server running Microsoft remote desktop
I can start taking apart the query - but is runs in a reasonable time in the query window on it's own
but when I use it for an insert it takes forever
Is it going to be a waste of time
Martyn
July 6, 2017 at 11:27 am
Just took a look at the query plan. The degree of parallelism is 10, 411 parallel operations, 70 sort operations, 22 hash match operations, 47 clustered index scans, 23 index scans, 51 nested loop operations. Cardinality issues due to convert and implicit convert usage.
Something tells me that this view is a mess.
July 6, 2017 at 11:39 am
Lynn Pettis - Thursday, July 6, 2017 11:27 AMJust took a look at the query plan. The degree of parallelism is 10, 411 parallel operations, 70 sort operations, 22 hash match operations, 47 clustered index scans, 23 index scans, 51 nested loop operations. Cardinality issues due to convert and implicit convert usage.Something tells me that this view is a mess.
On that note, the plan is for the plain SELECT, not the INSERT INTO...SELECT or SELECT...INTO.
Those couldn't use parallelism until 2014 for the latter and 2016 (under specific conditions) for the former.
If the SELECT is making good use of that available degree of parallelism, then I'd expect the serial SELECT...INTO or INSERT INTO...SELECT to take substantially longer.
Could you post even the estimated plan for the SELECT...INTO or INSERT INTO...SELECT?
Cheers!
July 6, 2017 at 11:46 am
file attached
July 6, 2017 at 12:04 pm
Estimated number of rows drops to 126 from 67,000+, 53 sort operations, 30 clustered index scans, 12 hash match operations, 8 index scans, 86 nested loop operations, 15 table spool operations. Being an estimate not seeing any tempdb spills where the actual for the select and several (maybe more).
July 6, 2017 at 12:18 pm
Also note that the plan is no longer a parallel plan.
If the original SELECT were making good use of those 10 threads, then running serially could well result in a substantial slowdown by itself.
Try running the plain SELECT with a MAXDOP 1 hint to see how it runs then. That should give us a rough idea how much of the slowdown is from the parallel/not-parallel difference.
Also, in the plain SELECT, is that column list all the columns? That's another potential difference, if the SELECT * INTO is pulling more columns than the other, that could affect other aspects of the query plan.
Finally, are you running each query in the same database?
I ask because the SELECT...INTO is using a different version of the CE than the plain SELECT, suggesting there are different compatibility levels at play.
Cheers!
July 6, 2017 at 5:31 pm
It's not a disk /table size problem - loads of space
MAXDOP 1 - made very little difference
yes I'm returning all the columns
I have also been trying so other stuff - the data is student results
I changed the view so it only look at one student
16 rows returned from select * from view in 43 seconds
16 rows added to table in 44 seconds using select into
I then went up to a department and a year - 1419 rows in 2:25
insert - cancelled at 13 mins.
I'm still really confused the select works but the insert doesn't
Martyn
July 7, 2017 at 8:23 am
how many indexes do you have on the table?
If there are a lot, maybe try disabling the indexes before you do the insert. It may even be faster to drop the indexes and rebuild them after the insert (we do that with our data warehouse and it shaved off 15 minutes of the data load).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 7, 2017 at 11:21 am
Of course, the one thing we haven't seen is the DDL for the view itself.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply