March 11, 2005 at 5:12 pm
Where can I find an excellent resource(s) to learn more about optimizing queries, and other ways to make data access as fast as possible? Any super books or articles that you guys/gals might recommend?
This is a serious, huge concern at my new company. I'm going to be rewriting possibly as many as 500 db procedures form legacy code (many are currently using cursors from the record-based days). It's really important that I learn everything I possibly can.
All suggestions are welcome,
Greg
March 13, 2005 at 2:12 am
Any of the 'Gurus Guide to Transact SQL....' books by Ken Henderson is a must-have.
If you're really serious, you need to get a grip on the internals as well - 'Inside SQL Server 2000' by Kalen Delaney is another must have.
/Kenneth
March 13, 2005 at 1:49 pm
"Book-on-Line" is a great resource, once you learn how to use it and actually take the time to read it. And, I've found that some of the authors that write "other" SQL books are a little over zealous in some of their recommendations or simply don't check out what they've written. For example, one well known author actually wrote (paraphrased) that it's not neccesary to do database size planning and that you should leave the default settings of 1MB start size and 10% growth because the database will take care of itself. I can't think of a better way to cause database fragmentation and fragmentation of the underlying harddisk both, of which, are the most effective ways to kill performance that I can think of (other than plain ol' bad code).
Books-on-Line (BOL for short) has great information on "Optimizing Database Performance", a bunch of neat performance tricks on where files should be stored, and how to "split" the files for performance. It also has some great tips on "Normalization", using and creating indexes, memory configuration, how and when/when not to use JOINS instead of using WHERE [NOT] IN, and the all important database maintenance which is also one of the biggest killers of performance if not done regulary. Most importantly, it tells you how to read "Estimated Execution Plans". Between those and a little performance testing of new code, your code can be relatively speedy even on "dog slow" machines.
The argument against BOL is that it's long... but so is a 1500 page book. The other thing is that most of the examples are not written as performance examples and I haven't found, yet, where they tell you how to convert a cursor to setbased code. That's where the other books might come in handy but, like I said, be careful what you believe. Test everything for both functionality and performance before you put it into production...
--Jeff Moden
March 16, 2005 at 6:27 pm
Thanks a bunch, you guys. I really appreciate the tips. I'm going to buy the recommended books as soon as I get paid.
Muchos Gracias,
Gregorio
March 16, 2005 at 8:01 pm
You're welcome.... just in case you didn't know... Books-on-Line is free and comes with SQL Server... it's the name of the rather voluminous on-line help that comes with SQL Server.
--Jeff Moden
March 19, 2005 at 9:42 pm
Yes, I've been using BooksOnline constantly for the past few weeks. It's a great help, but the examples often leave something to be desired.
March 20, 2005 at 7:55 am
one more suggestion --
SQL Server Query Performance Tuning Distilled
(http://www.curlingstone.com/)
In addition to really good advice - it is well written.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply