Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a View in aa query is slower than using the table directly Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:29 AM
Points: 6, Visits: 12
Hi all,

I have a query that basically links together three Tables and a View which has started running extremely slowly... it used to be OK but is now tediously slow.

The View is simply a table separation - the table used contains a list of data each with an assigned "ID" of which multiple "ID" entries can exist with a different "Type" column value i.e. Count, Description, Cost. The table has an index which uses both fields. The View just selects ALL entries where the "Type" column value = "Count".

The three tables and the view are linked using LEFT OUTER JOINs on a common field from each table - which runs slow.

BUT if I remove the view and link directly to the table and apply the WHERE [Type]='Count' to the query it runs almost instantly...

My question is how/why is SQL treating the two differently? It's almost like the index from the table used in the view is not used - is this to be expected?

Any comments/help always welcomed.

Regards
dazmaul

Post #1406139
Posted Friday, January 11, 2013 9:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 21,755, Visits: 15,454
Have you checked the execution plan for both methods?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1406143
Posted Friday, January 11, 2013 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:29 AM
Points: 6, Visits: 12
Thanks for the advice SQLRNNR, I have checked the Execution Plans and it appears that the first method using the view generates an extra Hash Table when joining the View to the first table... which I have just noticed is actually an Inline Table Valued Function which uses a parameter to return only entries with a specific ID.

If I take this out and just specify the table referenced directly and apply parameter as a WHERE condition directly it runs quickly even when referencing the view as well...

Again, I though the Inline Table Valued Functions would still operate as a View does OR is that not the case?




Post #1406163
Posted Friday, January 11, 2013 10:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 21,755, Visits: 15,454
It depends on how it is being used in the query. Also, is it possible that it is a multistatement instead of an iTVF?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1406173
Posted Monday, January 14, 2013 3:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
post the definition of all related queries along with view

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1406639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse