jwcolby
jwcolby at colbyconsulting.com
Wed Jun 20 08:02:06 CDT 2012
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 > >