[dba-SQLServer] Index on view

jwcolby jwcolby at colbyconsulting.com
Wed Jun 20 11:10:56 CDT 2012


One thing I have discovered, if you make changes to the query it knocks off the indexes.

I added a state table joined to the state field in one of the tables in the view.  I did not 
actually pull any data out of that table, just did an inner join to force only records in the state 
table to be used in the view (we don't use fleet post office, guam etc).

When I tried to save the query with this additional table joined in it came back with a "timeout" 
kind of error message.  However when I looked at the query it did have the new table joined in, but 
the indexes I had just created were missing.



John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it


On 6/20/2012 9:02 AM, jwcolby wrote:
> I am creating a pair of indexes now.  I apparently have to create a clustered index and then
> whatever other indexes I need.  Why I am not sure but it refuses to create non-clustered indexes
> until the clustered index exists.
>
> I also use compression everywhere.  Even though I have 64 gigs of memory, compression allows much
> more to fit into memory, and with 16 cores available, there are spare cores available to perform the
> decompression as data is used.
>
>  > I try never to join on views, I have always found that to be terribly slow.   I prefer to write
> one long SQL statement with nest joins.
>
> According to my reading, when this happens, SQL Server simply extracts all of the view metadata and
> builds up the equivalent of your "one long sql statement with nested joins".  Of course there is the
> time required to analyze and perform that sql statement build so it would be somewhat slower on the
> front end.  One would expect the actual query execution to be the same speed.
>
>
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
>
> On 6/20/2012 3:56 AM, Mark Breen wrote:
>> Hello John,
>>
>> I have no idea, but I would like to hear what your experiences are.
>>
>> I accidentally created an index on a view recently and I cannot report on
>> performance, but I notice that it takes up space in the database.
>>
>> I try never to join on views, I have always found that to be terribly slow.
>>   I prefer to write one long SQL statement with nest joins.
>>
>> Do you ever use the Database Engine Tuning Advisor?  Does it collapse when
>> it sees your database?  I used it recently and it suggested I create a
>> bunch of compound indexes.  It gave me some performance increases.
>>
>> Mark
>>
>>
>> On 19 June 2012 21:34, jwcolby <jwcolby at colbyconsulting.com> wrote:
>>
>>> I have a pair of tables.  Both tables have a clustered index on an integer
>>> PK.  TableA the PK is an autonumber.  TableB the PK is the PK from TableA,
>>> i.e. it is 1 to 1.
>>>
>>> TableA is updated monthly, TableA and TableB both get records added
>>> occasionally.  I just added about 180 million records to both tables.  I do
>>> occasionally delete records from both as the data ages.
>>>
>>> For providing counts to my client I need to join the two tables on the PK,
>>> then pull a single field from TableA and a handful of fields from TableB.
>>>   From there I will be doing count / group by kind of things.
>>>
>>> I created a view to pull all of the fields used in these counts.  I
>>> thought I would create an index on the view.
>>>
>>> Is this a good idea?  I have never indexed a view but I know it is
>>> possible.
>>>
>>> --
>>> John W. Colby
>>> Colby Consulting
>>>
>>> Reality is what refuses to go away
>>> when you do not believe in it
>>>
>>>
>>> ______________________________**_________________
>>> dba-SQLServer mailing list
>>> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
>>> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
>>>
>>> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>>>
>>>
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>




More information about the dba-SQLServer mailing list