Prev: Restore using MDF and LDF
Next: Is there a way to find when was a DTS Pacakge last modified
From: JOH on 4 Feb 2010 15:23
A delete command for table_T was moving excruciatingly slowly. Yes, an
index helped, but the surprise was that it wasn't an index for
table_T.

More info: there is a view_V that pulls from two tables, table_T and
table_S. The view was created with VIEW_METADATA.

Once I put an index on table_S, the delete on table_T ran like a
champ.

Is it possible that view_V was being treated by the engine as if it
were materialized ?

Anyone have any insight? Is this the intended behavior?
From: Erland Sommarskog on 4 Feb 2010 17:41
JOH (joholloway(a)gmail.com) writes:
> A delete command for table_T was moving excruciatingly slowly. Yes, an
> index helped, but the surprise was that it wasn't an index for
> table_T.
>
> More info: there is a view_V that pulls from two tables, table_T and
> table_S. The view was created with VIEW_METADATA.
>
> Once I put an index on table_S, the delete on table_T ran like a
> champ.
>
> Is it possible that view_V was being treated by the engine as if it
> were materialized ?
>
> Anyone have any insight? Is this the intended behavior?

I don't think the view has anything to do with it. My guess is that
the column you indexed in table_S is a foreign-key column that refers to
table_T. To be able to delete a row in table_T, SQL Server has to check
whether there is a reference in table_S. And if there is no index, SQL
Server has to scan the table.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Restore using MDF and LDF
Next: Is there a way to find when was a DTS Pacakge last modified