jwcolby
jwcolby at colbyconsulting.com
Wed Dec 16 08:37:18 CST 2009
Mark, > Are you really using all columns to search on? I have not used every one, with 584 columns it would be difficult to do so. However the client calls and says "I need a count where" and rattles off a list of columns. He has a spreadsheet with every single column listed and he uses these to perform very targeted searches. So I have used a ton of them already and could use any of them on a moment's notice. >In the case where you have an order for a specific column(s), can you not programmatically create the indexes on those columns... And if it does not take too long, I would run the scripts that the maintenance wizard create afterwards every time also, in the hope that it keeps the database intact. Yes, except that you are talking about 50 million records. You don't just "bang out an index". > Again, this is just instinct, but I feel you will have other integrity / file size / performance /de-fragmentation / torn pages etc etc with such enormous data and enormous numbers of indexes. On any other database table I would probably agree, however you must always remember that this table is completely static. No inserts, updates or deletes EVER. It is not a relational database, the table does in fact have a one to one with the name / address table but it is that other table which gets updated. Thus I don't see how such issues could occur. To be honest I am such an amateur in SQL Server that I cannot possibly know whether this is the right thing to do. What I do know is that when I started, my searches and sorts would take 20 minutes to two hours - basically SQL Server was doing all table scans. Once I discovered "cover indexes", where I did as you suggested and indexed a group of related fields, I got my searches down to anywhere from 2-10 minutes. With this "every field" indexed paradigm I got a two minute search down to 30 seconds, and went from a bunch of index scans to a bunch of index seeks. All I can say is that it sure seems to be another gigantic leap forward. When you spend as much time waiting on this stuff as I do, you are constantly looking for that next gigantic leap. I truly don't much care about the file size if the results are worthwhile. This database / table is the center of the client's universe. It is THE table that he does selects against. As such I will do whatever I can to make it fast. In fact some day soon I will be placing this file on a Flash drive. You know how much those cost, but again, if I can cut the times in 1/2 or 1/4 then it all becomes worthwhile. When that day comes I will be looking to place these indexes out in a separate file so that only these indexes have to go on flash. John W. Colby www.ColbyConsulting.com Mark Breen wrote: > Hello John, > > It is really going to be the best solution to index every column? > > Are you really using all columns to search on? > > I instinctively feel that if you place an index on every column, you will > loose the benefits of having an index in the first place, or at least you > loose some of the benefits that a neat and quick index delivers. I am > imagining Encyclopedia Brittanica if it supplied 25 different indexes, the > indexes would be larger that the books. > > I always imagined we should have as few indexes as you can get away with but > as many as we need. In the case where you have an order for a specific > column(s), can you not programatically create the indexes on those columns, > then run your queries and perhaps even go the final step and drop those > indexes you just created after the order has been delivered. And if it does > not take too long, I would run the scripts that the maintenance wizard > create afterwards everytime also, in the hope that it keeps the database > intact. > > Again, this is just instinct, but I feel you will have other integrity / > file size / performance /de-fragmentation / torn pages etc etc with such > enormous data and enormous numbers of indexes. > > What do you think? > > Mark