July 7, 2017 at 12:14 pm
martyn.smith-863492 - Thursday, July 6, 2017 5:31 PMIt's not a disk /table size problem - loads of space
MAXDOP 1 - made very little difference
yes I'm returning all the columnsI 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 intoI 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
As I said, there are a bunch of differences between the queries.
Could you post the exact MAXDOP 1 query you ran, along with execution plan?
Also, as I said, the two query plans show different versions of the cardinality estimator being used, which suggests you ran each query from a different database. The use of the different estimator versions can also have a substantial impact by itself.
There's a lot going on here, and most of what I'm pointing out is just in direct answer to "Why might these queries perform differently?"
As others have said, in terms of improving performance, tuning the view is probably a very good idea, even if that doesn't directly answer the original question.
Cheers!
July 7, 2017 at 12:33 pm
As another thought, does inserting it into a temporary table have the same slowness as inserting into the a real table?
That is you indicated that this:
SELECT *
INTO gridtemp
FROM sipr.dbo.grid_view_dataset
ran for 15 minutes before you cancelled it. How long does this run for:
SELECT *
INTO #gridtemp
FROM sipr.dbo.grid_view_dataset
I am wondering if you have some issues writing to your database.
Do you have all of your databases and log files that are used in the View and insert sitting on the same physical disk?.
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 8, 2017 at 7:50 am
I'm now even more confused but I have a solution/work around
this works and finishes in under 3 mins :- use sipr
insert into #GridView_Temp
select * from boa.sipr.dbo.grid_view_dataset
where as this never finishes
use gridview
insert into #GridView_Temp
select * from boa.sipr.dbo.grid_view_dataset
eg if I'm not sat in the SIPR table it doesn't work
Does anyone have any idea why ?
Martyn
July 8, 2017 at 10:20 am
Could you post the execution plans (estimated is fine) for the most recent form of the query from each database context?
Most likely they're using different versions of the CE because the DBs have different compatibility levels.
It could end up being something else, I suppose, but that's most likely at this point.
Cheers!
July 8, 2017 at 12:36 pm
Others have mentioned this, but please post the DDL for the view. The DDL for the underlying tables would help immensely.
I looked at the actual plan. Your estimated number of rows is significantly different than the actual number of rows for a bunch of operators. I didn't check anywhere close to all of them, but I checked enough to wonder if your statistics are out of date.
Another thing I noticed the warning in the SELECT operator - the first operator at the top-left position. You have 7 warnings about implicit casts. These can be pretty devastating for performance. Any NCI that exists can't be used because the NCI contains the value, not the converted value. So, the whole column (or table) has to be read, then converted, then the predicate (join or where) can be processed.
In your post above, you say one run takes 3 minutes while another one never finishes. Is anyone else using the database during one run and not the other? Blocking is something to consider after we get the view figured out. If we tune the view to not take so long, it won't block other instances of it.
Again, please post the DDL for the view and underlying tables, including indexes. We can't see what you see.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply