SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Five Ways to Rev up Your SQL Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/28/2002 :  09:41:40  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Jonathan submitted "From MSDN mag - July 2002. Mostly stuff a lot of people already know."

Article Link.

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/03/2002 :  17:15:20  Show Profile  Reply with Quote
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.




I think this is bad advice. In fact, I cringe at any paragraph that starts “design the database around them”.

First, using sentinel values is a poor architectural choice on many levels. Usually the performance gains are negligible and at the cost of disturbing your data’s meaningfulness. But most of all, the list of bugs whose resolution will be “Added category <> ‘No Category’ to WHERE clause” is gonna be a mile long. You’ll forever need to consider the sentinel and pass down its meaning to future programmers like folklore.

But there is more too it. You probably don’t need a sentinel or even a join (especially in the author’s example). If your lookup values are not going to change then you should implement them as a check constraint. If they are going to change you should implement a table, but you should use the natural key (in this case category) as the primary key. Then in you product table, you’ve got meaningful data: you don’t need to join to figure out the category of a record and NULL means NULL.

Before you scream “SPACE ISSUES”, let me say this. If you have considered the trade-off of performance vs. space and actually quantified each to a measurable amount . . . if you’ve done this and need to conserve the space, then do the IDENITY and surrogate dance. You’ve prolly got a lookup table with millions of rows. Otherwise, do yourself a favor and use the natural key and bask in the performance benefits….


<O>
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/05/2002 :  13:30:34  Show Profile  Reply with Quote
And in an article about SQL Server Performance, why would the author give examples using embedded-SQL?

<O>
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/05/2002 :  13:32:58  Show Profile  Reply with Quote
And the author never explains how the [freaking-horrible] idea of selecting @@IDENTITY in a insert trigger rev's up performance. (It doesn't....that's why)

EDIT: maybe I should qualify "freaking-horrible". First, the DML in this trigger is going to run on every insert statement. Most often, when you are dealing with @@IDENTITY in a stored proc, you are assigning the value to variable, thus duplicating your efforts. And really, its more than duplicating your efforts, if you client is running a proc and gets this unwanted RS back that it has to know to skip over. Additionally, the trigger is not written for multi-row inserts, so it is useless code in this case.

<O>

Edited by - Page47 on 07/05/2002 14:23:56
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/05/2002 :  15:59:50  Show Profile  Reply with Quote
Hum shall I continue, or have I beat this dead horse?

Well, it's Friday.

The second tip suggests using Inline Views Versus Temp Tables. While this is sound advise, the author suggests the performance gain comes from the fact that the temp table will spend "a lot of I/O and disk access". Not quite true, or at least no more than the derived table. The performance gain will come from the utilization of indexes on the base tables and the execution of one DML statement rather than two.

The forth tip is about using a CROSS JOIN to return months with no sales in a customer by month sales cross-tab query. First off, you may have noticed that the query doesn't compile. The population fo the @dtEndDate variable has a syntax error. Additionally, this @dtDate variable is declared but never used?!? Why even bother providing source code in an article if it doesn't work? Do you really need a while loop to figure out the last 12 months? But most importantantly, the author doesn't even take his own advice about derived tables. Instead he creates these table variables and populates them through several dml steps when it could be done with one select statement....I submit my version.



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

 

So, gee Mr. Page47, what's your problem. Let me make it crystal. You get the MSDN mag that TOO many people take as bible....kick tires, buy car. I think that is crap. As a community we need to think for ourselves!! This article says nothing about SQL Server performance, is full of poorly thought out suggestions, vague details, and a complete and utter lack of scientific method. There is not hard data to support these claims. There isn't even any suggestion of why performance may be effected by several of these idea. Its a pathetic excuse industry expertise.

Finally, after my first post on this thread I forwarded the link to Mr. Johnny Papa. He replied enlightening me that "I was entitled to my own opinion". Well, first, anyone who is gonna publish this article in a worldwide mag outta have the stones to defend himself here. Second, in an article about performance, there is no room for opinion, and the suggestion illuminates the complete lack of understanding of the subject matter on the author's part.

<O>
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/05/2002 :  16:36:08  Show Profile  Visit graz's Homepage  Reply with Quote
I was about to ask if you'd written to the author. Looks like you did. After reading this and rereading the article I'm considering taking this link down. I'll leave the thread alone for the curious. Any thoughts?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/05/2002 :  17:41:15  Show Profile  Reply with Quote
Graz, I say leave it up. Infact, I would encourage some of the others on the site to include their comments about this and other articles.

I find so many of the trade mags these days chocked full of flawed suggestions of this kind. And then I am confronted by some callow programmer who brings me this article waving a flag of victory saying, "Nanny-nanny-boo-boo, I was right and you were wrong. Looks its right here in MSDN!!!" Blah. We need to hold these professional authors to a higher standard. The arguement used to hold true: "If you don't like it, don't read it!"....but not anymore. This junk brings the whole industry down and MSDN and Papa should know that.

At a minimum, I see no harm in leaving the thread as is.

<O>
Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 07/05/2002 :  18:00:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
I agree about leaving it up, and I have a suggestion. Provide more examples where Mr. Papa's suggestions DON'T improve performance over another method. Give SHOWPLAN stats too, for both methods (his and others). That would solidify it as more than "your opinion" and bring it into the realm of documented fact. My biggest gripe with the article was that there is no performance comparison statistics; if there were I'd give it more creedence.

And his LEFT JOIN stuff completely boggles me; I don't see how his suggestion will improve performance at all. In fact, I don't understand his assertion that LEFT JOINs perform worse than INNER JOINs, other than the obvious index usage, which only helps if you have a covered index on the tables anyway. Put a column in the SELECT list that's not indexed, boom, there goes your performance improvement; it'd have to do the same I/O as a LEFT JOIN.

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/05/2002 :  18:26:04  Show Profile  Visit graz's Homepage  Reply with Quote
And I should have mentioned you can rate this item (http://www.sqlteam.com/Item.asp?ItemID=9939) although there isn't a great way to see the ratings at this point other than searching or looking in the right topic (http://www.sqlteam.com/FilterTopics.asp?TopicID=103).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/06/2002 :  08:28:00  Show Profile  Reply with Quote
Rob, I agree with you that I need to offer some examples of my own. I must admit the lazy- in me is scream, "the burdon of proof is on the author!!" I'll work on some things this weekend.

<O>
Go to Top of Page

monkeybite
Posting Yak Master

USA
152 Posts

Posted - 07/08/2002 :  12:29:35  Show Profile  Reply with Quote
I need to admit this, I was the one that submitted this link. After seeing this thread, and re-re-visiting the MSDN site, I have a few observations:

1. I really need to read articles carefully.
2. I feel like I accidentally submitted a link to yak-porn on a family website.
3. The scientific method, for the most part, is dead.
4. Papa's reply to Page47 about "opinions" and "entitlement" seems to demonstrate that machismo is alive and well in this industry.

In my opinion, the death of scientific method is responsible for millions of hours in wasted development and bug fixes, as well as poorly designed/architected databases, applications, and so on. I can point the finger at two groups responsible for this, lazy developers (myself included), and management. Who has any freakin time to observe, hypothesize, predict, test, retest, and conclude in their project scope?

I've been a participant in many projects that seem to have minimal hours for basic design and testing, and the hours usually get further cut as development lurches forward and scope creep occurs. Due to time constraints (and laziness), I think that lots of us developers/programmers look to so-called industry experts for quick fixes to our problems and issues, and tend to accept the solutions without testing or challenge. Not challenging an expert that presents a questionable solution without proof gives tacit approval to them to continue in their ways (and seems to give 'em a really thin skin as well). Aiding and abetting. I am guilty as charged.

I have resolved to do a few things:
1. Continue to admit that what I know about SQL is much smaller than what I don’t know about SQL.
2. Swallow my pride when challenged and presented with facts to the contrary
3. Demand more of experts that I give credence to – ask them to provide some proof.

-- monkeybite


Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/08/2002 :  12:57:08  Show Profile  Reply with Quote
Realizing what you don't know, in my opinion, is far more powerful than knowing how at the expense of understanding why...

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
Go to Top of Page

monkeybite
Posting Yak Master

USA
152 Posts

Posted - 07/08/2002 :  13:37:19  Show Profile  Reply with Quote
quote:

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



I wish my math professors had subscribed to this philosophy when handing out grades. Nothing will reduce an out-of-control ego faster than taking an advanced analysis class . . .

-- monkeybite

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/08/2002 :  14:02:09  Show Profile  Visit graz's Homepage  Reply with Quote
Conversations like this are why I have comments attached to articles. People aren't perfect and there is almost always a better way to do something. The goal of the comments was to flush that information out.

Now if we could just get the article's author to visit this discussion :)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000