Using a View in aa query is slower than using the table directly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Using a View in aa query is slower than using...

Using a View in aa query is slower than using the table directly

Rate Topic

Display Mode

Topic Options

Author

Message

dazmaul

Posted Friday, January 11, 2013 9:32 AM

Forum 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

SQLRNNR

Posted Friday, January 11, 2013 9:41 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Have you checked the execution plan for both methods?

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

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

dazmaul

Posted Friday, January 11, 2013 10:20 AM

Forum 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

SQLRNNR

Posted Friday, January 11, 2013 10:31 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

It depends on how it is being used in the query. Also, is it possible that it is a multistatement instead of an iTVF?

Post #1406173

Bhuvnesh

Posted Monday, January 14, 2013 3:41 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

post the definition of all related queries along with view

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1406639

« Prev Topic | Next Topic »

Permissions