[RESOLVED] VIEW is slow - the query itself runs fast

[szlamany]

This query runs instantly

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

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

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?

[FunkyDexter]

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-

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.

[digitalShaman]

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.

[szlamany]

Execution plain of direct query

[szlamany]

And now the VIEW

[szlamany]

Using a TVF works - nicely - runs instantly

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')

[FunkyDexter]

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.

[szlamany]

This is the TVF execution plan. I'm not really believing the TABLE SCAN...

[edit] btw - I just looked at the ROW COUNT stats on the BAD query - it touched 1.8 million rows.

The TABLE SCAN in this posted image only touched 30 rows.[/edit]