[RESOLVED] VIEW is slow - the query itself runs fast
This query runs instantly
Code:
Select SCS.SCS_COURSE_SECTION Collate Database_Default "SCS_COURSE_SECTION"
, SCS_STUDENT Collate Database_Default "SCS_STUDENT"
, STU.FIRST_NAME Collate Database_Default "FIRST_NAME"
, STU.LAST_NAME Collate Database_Default "LAST_NAME"
--, STC_STATUS Collate Database_Default "STC_STATUS"
From [COURSE_SECTIONS_LS] CSLS
Left Join [STUDENT_COURSE_SEC] SCS on SCS.STUDENT_COURSE_SEC_ID=CSLS.SEC_ACTIVE_STUDENTS
Left Join [PERSON] STU ON STU.ID=SCS.SCS_STUDENT
Left Join [COURSE_SECTIONS] CS ON CS.COURSE_SECTIONS_ID=SCS.SCS_COURSE_SECTION
Where SEC_TERM='18/FA' and CSLS.SEC_ACTIVE_STUDENTS is not null
and CSLS.COURSE_SECTIONS_ID='123123'
Note the specific COURSE_SECTIONS_ID of '123123' being picked up.
If I create a VIEW that looks like this - exact same query without that '123123' piece
Code:
Create View cdStuCourseSection_V
as
Select SCS.SCS_COURSE_SECTION Collate Database_Default "SCS_COURSE_SECTION"
, SCS_STUDENT Collate Database_Default "SCS_STUDENT"
, STU.FIRST_NAME Collate Database_Default "FIRST_NAME"
, STU.LAST_NAME Collate Database_Default "LAST_NAME"
--, STC_STATUS Collate Database_Default "STC_STATUS"
From [COURSE_SECTIONS_LS] CSLS
Left Join [STUDENT_COURSE_SEC] SCS on SCS.STUDENT_COURSE_SEC_ID=CSLS.SEC_ACTIVE_STUDENTS
Left Join [PERSON] STU ON STU.ID=SCS.SCS_STUDENT
Left Join [COURSE_SECTIONS] CS ON CS.COURSE_SECTIONS_ID=SCS.SCS_COURSE_SECTION
Where SEC_TERM='18/FA' and CSLS.SEC_ACTIVE_STUDENTS is not null
And then run this SELECT against the VIEW
Code:
Select * From cdStuCourseSection_V Where SCS_COURSE_SECTION='123123'
This takes 5 seconds.
This is like a reporting region database - the actual DB is Unidata.
Should I put this QUERY in a SPROC? Maybe a TABLE VALUED FUNCTION? Will that change the behavior?
Last edited by szlamany; Oct 11th, 2018 at 07:00 AM.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,082
Re: VIEW is slow - the query itself runs fast
Should I put this QUERY in a SPROC? Maybe a TABLE VALUED FUNCTION? Will that change the behavior?
It's worth a punt.
I can't see any obvious reason why the view would be slower though. Maybe check the execution plans and see if they're different. I will hazard a guess but I'm clutching at straws so I'm not really expecting this to be the answer but...
The query has this where clause-
Code:
Where SEC_TERM='18/FA' and CSLS.SEC_ACTIVE_STUDENTS is not null
and CSLS.COURSE_SECTIONS_ID='123123'
In the view the CSLS.COURSE_SECTIONS_ID criteria is being handled separately, in the consuming query.
Is it possible you've got an index that suitable for the combined criteria (i.e. it covers CSLS.SEC_ACTIVE_STUDENTS and CSLS.COURSE_SECTIONS_ID) that's being used in the original query. Maybe the view approach doesn't use that index - or if it does it only uses it to resolve the actual view - and then the consuming query does not have a suitable index to resolve the CSLS.COURSE_SECTIONS_ID criteria.
That's the only thing I can think of. If I'm right then a TVF is likely to help because the engine can in-line the whole thing and treat it as a single query. I'm not sure it can do that with Views.
You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Sounds like an issue with "Parameter sniffing" to me. although usually mentioned in sproc context, i am not 100% sure if that is also valid with views.
comparing the execution plan is a good recommendation. You could try adding an approriate index hint to the view.
Drop Function dbo.cdStuCourseSection_TVF
Go
--Declare @jsonStr varchar(max) Set @jsonStr='{"What": "Column wo/CR", "XPos": 5300, "Column": 10}'
Create Function cdStuCourseSection_TVF(@Term varchar(100), @CrsSec varchar(100))
Returns @cdStuCourseSection Table (SCS_COURSE_SECTION varchar(100), SCS_STUDENT varchar(100), FIRST_NAME varchar(100), LAST_NAME varchar(100))
Begin
Insert into @cdStuCourseSection
Select SCS.SCS_COURSE_SECTION Collate Database_Default "SCS_COURSE_SECTION"
, SCS_STUDENT Collate Database_Default "SCS_STUDENT"
, STU.FIRST_NAME Collate Database_Default "FIRST_NAME"
, STU.LAST_NAME Collate Database_Default "LAST_NAME"
--, STC_STATUS Collate Database_Default "STC_STATUS"
From [COLL-DATA-HOST].[coll18_prod].[dbo].[COURSE_SECTIONS_LS] CSLS
Left Join [COLL-DATA-HOST].[coll18_prod].[dbo].[STUDENT_COURSE_SEC] SCS on SCS.STUDENT_COURSE_SEC_ID=CSLS.SEC_ACTIVE_STUDENTS
Left Join [COLL-DATA-HOST].[coll18_prod].[dbo].[PERSON] STU ON STU.ID=SCS.SCS_STUDENT
Left Join [COLL-DATA-HOST].[coll18_prod].[dbo].[COURSE_SECTIONS] CS ON CS.COURSE_SECTIONS_ID=SCS.SCS_COURSE_SECTION
Where SEC_TERM=@Term and CSLS.SEC_ACTIVE_STUDENTS is not null and CSLS.COURSE_SECTIONS_ID=@CrsSec
Return
End
Go
Select * From dbo.cdStuCourseSection_TVF('18/FA','123123')
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,082
Re: [RESOLVED] VIEW is slow - the query itself runs fast
Looks like the queries are remote - that probably wasn't helping. It also makes it really difficult to see what was going on because those remote queries are just black boxes so no chance to see what indexes were being used. The extra complexity on the view plan implies that that aproach was resulting in the query being resolved in two stages (i.e. resolve the view then filter using a merge join) while the straight query is single stage - that would make a difference.
I'm curious, what execution plan do you get using the TVF? I'm guessing it will look the same as the single queries plan, in which case the engine is "in-lining" the TVF which would give you the best behaviour.
You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
→ The Comprehensive Guide to Cloud Computing
A complete overview of Cloud Computing focused on what you need to know, from selecting a platform to choosing a cloud vendor.