Linq to SQL, SQL Server 2008 and optimization

Question

I'm designing a large scale web application with about 30 tables, more than 2/3 of which are related to each other. I'm using ASP.NET MVC, Linq to SQL with SQL Server 2008. These tables are meant to hold thousands of records.

As a programmer, what should I focus on to help optimize the database and the queries to and from Linq?

Do you guys have a recommendation on a book that would generally cover the topics I need to focus on while I build this application or some articles that would touch on the basic topics?

I know about SQL Profiler (is there a free version?) and have read briefly on indexes, is there more to it?

Also, are there "best practices" to designing a large scale database driven application when it comes to counting or querying multiple rows, searching "text" fields in rows?

Appreciate the help

Answer 1

There is hardly anything special about LINQ to tell you. Common architecture and database optimizations apply.

Check out for example this excellent question: What are your most common sql optimizations?

Answer 2

As an old IT saying goes: do not prematurely optimize, though! First write your code and make sure it's solid and works - and only when you see and identify bottlenecks, then go in, measure, tweak, measure again, optimize, measure yet again.

But if you do need to optimize, or want to avoid bottlenecks in the first place, I totally agree with "new in town" - the database is the foundation, if you screw that up, no degree of "optimizing" your LINQ or app on top of that will really help much.

Get your foundation straight and proper:

• make sure to use the appropriate types
• don't make columns unnecessarily large
• get the right indices in place
  • primary key on every table
  • don't make a GUID column your clustering key under any circumstances
  • foreign key relationships where they make sense
  • put an index on your foreign key columns)
• avoid any data duplication

and so on - the basics of solid database design.

Only with that in place can you even start to think about optimizing your application and data-access code.

Marc

Answer 3

I know about SQL Profiler (is there a free version?)

Profiler for Microsoft SQL Server 2005/2008 Express Edition is an open source replacement of Profiler