quote:

---

One of the easiest ways to avoid slowdowns with LEFT JOINs is to design the database around them as much as possible. For example, let's assume that a product may or may not have a category. If the product table stores the ID of its category and there was no category for a particular product, you could store a NULL value in the field. Then you would have to perform a LEFT JOIN to get all of the products and their categories. You could create a category with the value of "No Category" and thus specify the foreign key relationship to disallow NULL values. By doing this, you can now use an INNER JOIN to retrieve all products and their categories. While this may seem like a workaround with extra data, this can be a valuable technique as it can eliminate costly LEFT JOINs in SQL batches. Using this concept across the board in a database can save you lots of processing time. Remember, even a few seconds means a lot to your users, and those seconds really add up when you have many users accessing an online database application.

---

DECLARE @dtStartDate DATETIME, 
    @dtEndDate DATETIME,
    @dtDate DATETIME

SET @dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) 
    AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + ' 23:59:59' AS DATETIME))
SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)


SELECT
    CAST(YEAR(cx.dt) AS VARCHAR(4)) + '-' +
        CASE 
        WHEN MONTH(cx.dt) < 10 
        THEN '0' + CAST(MONTH(cx.dt) AS VARCHAR(2))
        ELSE CAST(MONTH(cx.dt) AS VARCHAR(2))
    END AS sMonth,
    cx.CustomerID,
    cx.CompanyName,
    cx.ContactName,
    sum(isnull(od.quantity,0) * isnull(od.unitprice,0)) as msales
from
    (select
        months.dt,
        customers.CustomerID,
        customers.CompanyName,
        customers.ContactName
     FROM
        (select dateadd(mm,-1,@dtEndDate) as dt union
         select dateadd(mm,-2,@dtEndDate) union
         select dateadd(mm,-3,@dtEndDate) union
         select dateadd(mm,-4,@dtEndDate) union
         select dateadd(mm,-5,@dtEndDate) union
         select dateadd(mm,-6,@dtEndDate) union
         select dateadd(mm,-7,@dtEndDate) union
         select dateadd(mm,-8,@dtEndDate) union
         select dateadd(mm,-9,@dtEndDate) union
         select dateadd(mm,-10,@dtEndDate) union
         select dateadd(mm,-11,@dtEndDate) union
         select dateadd(mm,-12,@dtEndDate)) as Months
      cross join
      (select distinct
         c.CustomerID,
         c.CompanyName,
         c.ContactName
      from
         Customers c
         INNER JOIN Orders o ON c.CustomerID = o.CustomerID
      WHERE    o.OrderDate BETWEEN @dtStartDate AND @dtEndDate ) Customers )cx
    left join Orders o on (cx.CustomerID = o.CustomerID and 
                           datediff(mm,o.OrderDate,cx.dt) = 0 and 
                           o.OrderDate BETWEEN @dtStartDate AND @dtEndDate)
    left join [Order Details] od on o.OrderID = od.OrderID
group by
    CAST(YEAR(cx.dt) AS VARCHAR(4)) + '-' +
        CASE 
        WHEN MONTH(cx.dt) < 10 
        THEN '0' + CAST(MONTH(cx.dt) AS VARCHAR(2))
        ELSE CAST(MONTH(cx.dt) AS VARCHAR(2))
    END,
    cx.CustomerID,
    cx.CompanyName,
    cx.ContactName
order by
    cx.CompanyName,
    msales

quote:

---

I have two reactions to this quite common attitude in the industry (which led to the title of this article). I wonder if bridge builders, for example, would reject the laws of physics as too complicated for building complex bridges? Or the builders of atomic reactors would reject quantum physics as too complex to rely on? Once in a blue moon we do have individuals or institutions who do that, but they are the rare exceptions, not the rule, and they disappear quite fast. In the database field, however, long and successful careers and expert reputations can be built on the systematic dismissal of theory as either impractical, or too complex.

What makes this even worse, is that not adhering to theory actually makes database management much more complex, not simpler.
Fabian Pascal, Against the Grain

---

No worries Monkeybite; its Papa and the editors at MSDN that should be considering your 'resolved' more than anyone.

So I didn't write my counter-article this weekend as promised. It was far to nice weather and there are too many twisty roads out there where I can wack open the throttle after a late-apex and loft the front wheel on corner exit . . . Not to mention MSDN still hasn't contacted me about the payment schedule....hehehehe.

<O>

Edited by - Page47 on 07/08/2002 12:57:44

quote:

---

Realizing what you don't know, in my opinion, is far more powerful than knowing how at the expense of understanding why...

---