dcsimg
Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,762

    Resolved [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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    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

  3. #3
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,273

    Re: VIEW is slow - the query itself runs fast

    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.

  4. #4

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,762

    Re: VIEW is slow - the query itself runs fast

    Execution plain of direct query
    Attached Images Attached Images  

    *** 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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,762

    Re: VIEW is slow - the query itself runs fast

    And now the VIEW
    Attached Images Attached Images  

    *** 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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,762

    Re: VIEW is slow - the query itself runs fast

    Using a TVF works - nicely - runs instantly

    Code:
    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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    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

  8. #8

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,762

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

    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]
    Attached Images Attached Images  
    Last edited by szlamany; Oct 11th, 2018 at 12:53 PM.

    *** 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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width