Re: Joins and Scability

From: David Browne (meat_at_hotmail.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 09:10:26 -0500

"Amol" <apk@nospam.cbord.com> wrote in message
news:OX0ZWN3tEHA.2300@TK2MSFTNGP09.phx.gbl...
> Hi All,
> We have a client server application where a lot of business logic is
> executed in stored procedures in SQL Server. Our procedures use a lot of
> complex joins between 3-4 tables or more. While I dont have specific
> examples to show, I was hoping for some general pointers on how joins
> affect positively and negatively on scability. What are the dos and donts.
> Any articles on this will also be very helpful.
>

When you start joining tables you introduce a new area of concern for your
application's performance.

The good news is that joins on properly indexed tables are cheap, and the
better news is that executing business logic in SQL is generally much faster
and cheaper than doing it in client code.

The bad news is that you need to learn about query excution plans, logical
vs physical io, indexing strategies, profiling, server workloads,
parallelism, SQL performance do's and don'ts. You'll find that many of the
so-called rules for database design are really just rules-of-thumb to
protect developers from having to learn too much about the database. "Joins
are slow" is one of those so-called rules.

Some quick pointers for joins
  DO build an index for every foreign key
  DONT use expressions in a where-clause
  DO write your queries by hand in QueryAnalyzer (query builders only hide
things you really need to know)
  DO examine the execution plan of your queries when you are writing them
  DO write views which join tables together in handy ways

David

Relevant Pages

  • Re: Business Rules & Referential Integrity
    ... With regards to my stored procedures and the inherent "White Elephant", ... fully appreciate your comments and will use Declared Referential Integrity ... in SQL Server and capture any named constraint errors/exceptions this way. ... >> about application design and implementation using DotNet. ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... server compares the parameter values to the date of the creation of the SP, ... > 1) you can set the record source to the name of a SP, ... > Sylvain Lafontaine, ing. ... >> I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Permissions???
    ... > Server: SQL Server 2000. ... > the user could Write/Read data from server and execute stored procedures. ... > The connection was made with DSNs. ... ADO gives the following message when using stored ...
    (microsoft.public.data.ado)
  • Re: Help!!!
    ... Ensure your stored procedures include 'SET NOCOUNT ON'. ... > Server: SQL Server 2000. ... > I had an ADO conection to the from a VB Application, ... > The connection was made with DSNs. ...
    (microsoft.public.sqlserver.security)
  • Re: Debug privileges
    ... application in the server. ... If a process executing in the workstation can use ... So is it necessary for the protected application to ensure that workstations ... Except if the person starting a process is an employee and the actual ...
    (microsoft.public.platformsdk.security)