Geeks With Blogs

@neh123us
  • neh123us T4 sould be your outsourcing strategy https://t.co/1ZceCUKWKE T4 - The Insource Code Monkey about 503 days ago
  • neh123us Had a need for Dynamic Views in MVC today. Wanted to use a Partial view on two different views with different ViewModels about 551 days ago

News Google

Nick Harrison Blog<Nick>.Next()

Let's continue exploring some of the SQL Smells from Phil's list. He has been putting together.

Datatype mis-matches in predicates that rely on implicit conversion.(Plamen Ratchev)

This is a great example poking holes in the whole theory of "If it works it's not broken" Queries will this probably will generally work and give the correct response. In fact, without careful analysis, you probably may be completely oblivious that there is even a problem. This subtle little problem will needlessly complicate queries and slow them down regardless of the indexes applied.

Consider this example:

CREATE TABLE [dbo].[Page](

    [PageId] [int] IDENTITY(1,1) NOT NULL,

    [Title] [varchar](75) NOT NULL,

    [Sequence] [int] NOT NULL,

    [ThemeId] [int] NOT NULL,

    [CustomCss] [text] NOT NULL,

    [CustomScript] [text] NOT NULL,

    [PageGroupId] [int] NOT NULL;

 

CREATE PROCEDURE PageSelectBySequence

(

@sequenceMin smallint ,

@sequenceMax smallint

)

AS

BEGIN

SELECT [PageId] ,

[Title] ,

[Sequence] ,

[ThemeId] ,

[CustomCss] ,

[CustomScript] ,

[PageGroupId]

FROM [CMS].[dbo].[Page]

WHERE Sequence BETWEEN @sequenceMin AND @SequenceMax

END

 

Note that the Sequence column is defined as int while the sequence parameter is defined as a small int. The problem is that the database may have to do a lot of type conversions to evaluate the query. In some cases, this may even negate the indexes that you have in place.

Using Correlated subqueries instead of a join   (Dave_Levy/ Plamen Ratchev)

There are two main problems here. The first is a little subjective, since this is a non-standard way of expressing the query, it is harder to understand.

The other problem is much more objective and potentially problematic. You are taking much of the control away from the optimizer. Written properly, such a query may well out perform a corresponding query written with traditional joins. More likely than not, performance will degrade.

Whenever you assume that you know better than the optimizer, you will most likely be wrong. This is the fundmental problem with any hint.

Consider a query like this:

 

SELECT Page.Title ,

Page.Sequence ,

Page.ThemeId ,

Page.CustomCss ,

Page.CustomScript ,

PageEffectParams.Name ,

PageEffectParams.Value ,

( SELECT EffectName

FROM dbo.Effect

WHERE EffectId = dbo.PageEffects.EffectId

) AS EffectName

FROM Page

INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId

INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId

 

This can and should be written as:

 

SELECT Page.Title ,

Page.Sequence ,

Page.ThemeId ,

Page.CustomCss ,

Page.CustomScript ,

PageEffectParams.Name ,

PageEffectParams.Value ,

EffectName

FROM Page

INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId

INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId

INNER JOIN dbo.Effect ON dbo.Effects.EffectId = dbo.PageEffects.EffectId

 

The correlated query may just as easily show up in the where clause. It's not a good idea in the select clause or the where clause.

Few or No comments.

This one is a bit more complicated and controversial. All comments are not created equal. Some comments are helpful and need to be included. Other comments are not necessary and may indicate a problem.

I tend to follow the rule of thumb that comments that explain why are good. Comments that explain how are bad. Many people may be shocked to hear the idea of a bad comment, but hear me out.

If a comment is needed to explain what is going on or how it works, the logic is too complex and needs to be simplified.

Comments that explain why are good. Comments may explain why the sql is needed are good. Comments that explain where the sql is used are good. Comments that explain how tables are related should not be needed if the sql is well written. If they are needed, you need to consider reworking the sql or simplify your data model.

Use of functions in a WHERE clause. (Anil Das)

Calling a function in the where clause will often negate the indexing strategy. The function will be called for every record considered. This will often a force a full table scan on the tables affected.

Calling a function will not guarantee that there is a full table scan, but there is a good chance that it will. If you find that you often need to write queries using a particular function, you may need to add a column to the table that has the function already applied.

Posted on Tuesday, December 14, 2010 10:00 PM | Back to top


Comments on this post: More SQL Smells

comments powered by Disqus

Copyright © Nick Harrison | Powered by: GeeksWithBlogs.net | Join free