|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is Northwind database fully optimised ?I am trying to figure out how to optimise a sql server database. For starters I pumped in to the orders table of Northwind database around 120,000 records. I have now queried the database to retrive those records. A simple join query like this SELECT * FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid takes 22 minutes to execute. I know its a Select * from query but its just 120,000 records. I would imagine this number of records to be fairly normal in most applications. Another query SELECT E.* FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid takes over 4 minutes to execute. My applications have started to grow to such large sizes and I want to optimise the reporting tools. What are the suggestions to increase these queries and to optimise the database. It is pretty clear Northwind database supplied by MS is not optimised to handle more than 120,000 records. Index tuning wizard does not recommend any new indexes as well. Thanks for any suggestions. Vk Hi
In today's terms Northwind is a DB that shows you most of the things that are bad practice. It is far from optimized and far from a good model. The model is so old that probably the developer at Microsoft who created it has retired. http://www.sql-server-performance.com/ is a good place to start. -- Show quote-------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "call me VK" <orthopodSPAM74@yahoo.co.uk> wrote in message news:3ofs8pF5pgbcU1@individual.net... > Hi Guys > I am trying to figure out how to optimise a sql server database. For > starters I pumped in to the orders table of Northwind database around > 120,000 records. > > I have now queried the database to retrive those records. > > A simple join query like this > SELECT * FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid > takes 22 minutes to execute. I know its a Select * from query but its just > 120,000 records. I would imagine this number of records to be fairly > normal > in most applications. > > Another query > SELECT E.* > FROM Orders O INNER JOIN Employees E ON E.Employeeid=O.Employeeid > takes over 4 minutes to execute. > > My applications have started to grow to such large sizes and I want to > optimise the reporting tools. > What are the suggestions to increase these queries and to optimise the > database. It is pretty clear Northwind database supplied by MS is not > optimised to handle more than 120,000 records. Index tuning wizard does > not > recommend any new indexes as well. > > Thanks for any suggestions. > Vk > > > Northwind sucks. And so do the other Vendor's sample RDBMS schemas and
sample data (Oracle's Scott/Tiger, Centura's presidents, etc.) Most of the are not normalized, etc. You might want to actually read a book about database design, RDBMS, etc. And you might want to learn that rows are not records. Your whole approach to learning by lookng at bad code is wrong.  |
|||||||||||||||||||||||
Other interesting topics