jwcolby
jwcolby at colbyconsulting.com
Fri Jan 4 10:27:15 CST 2008
249? What a gype. As it turns out I will probably need less than 100 anyway. ATM I have 37 indexes. The odd part of this is that in some cases multi-field covers work well, probably because of the contents of the fields. For example they break the "age of adults" down into bands of 10 years, and inside of that there are multiple values, including values that mean male and female. I created a cover on seven "age of adult" fields (plus PK) and get count times around 15 seconds in a query that asks for three values in two different age bands. Of course each of those age bands also has a "single field" index (includes PKID) and I really don't know which indexes it used to process the query. That is something else I am going to have to go learn in my copious spare time. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Friday, January 04, 2008 10:47 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Large cover index slows things down That's beautiful (index-creation time). Be aware, however, that the maximum number of indexes on a single table is 249 (LOL). Since you have so much on your plate, and I so little, perhaps you might consider farming something my way. :) Oh, incidentally, I am looking for copies of the following software: Adobe PhotoShop Adobe Illustrator Adobe FrameMaker Adobe Acrobat (full -- last version I have is 6.0) Given so much on your plate, I doubt that you have time for such unrelated activities, but I thought I'd inquire anyway. A.0 On 1/4/08, jwcolby <jwcolby at colbyconsulting.com> wrote: > > Arthur, > > I knew these existed but have never used them. Given everything on my > plate and my current state of knowledge or lack thereof, I might just > delete the larger cover indexes back out. the use of the single field > cover indexes has worked wonders on my processing times already. The > client tends to ask for the same select fields over and over. in this > case I had to add 8 new indexes but had three in place already. > Luckily the creation of the cover indexes only takes 2 minutes or so > per. Over time I will end up with every field already indexed and the > process of filling an order will get less time consuming. > > > John W. Colby > Colby Consulting > www.ColbyConsulting.com > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com