January 11, 2013 at 9:32 am
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
January 11, 2013 at 9:41 am
Have you checked the execution plan for both methods?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 11, 2013 at 10:20 am
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?
January 11, 2013 at 10:31 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2013 at 3:41 am
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;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply