ansaurus

Question

Answer 1

A:

There's nothing in there that looks "bad" per-se. Looking at it, it seems scary but after doing some reformatting and removing the extraneous brackets, it's not that bad. I've never liked nested JOINs, and I'd look at cleaning that up, but that's a personal preference: I don't think it'll do anything for performance.

So ... if taking out the WHERE clause produces a speed up, I'd be looking at indexes and implicit conversions. The first is self-explanatory; the second I've been burnt by before. Both can be detected by analysing the execution plan.

Effectively, an implicit conversion is bad when SQL Server converts the data in the database column rather than converting the parameter that is being compared with a database column. This can happen when a VARCHAR database column is being compared to an NVARCHAR paramater: SQL Server can't do a straight comparison because VARCHAR != NVARCHAR, so it promotes the VARCHAR data in the table column to NVARCHAR before doing a comparison. The result is a full index scan rather than an index seek which can slaughter performance for large tables.

I'd look at the execution plan, see if you have any index seeks and if you do, look to see if there's any implicit conversions of database columns happening behind them.

Chris J 2009-10-26 22:34:58

Answer 2

+1 A:

Are there indexes on any of the useful columns in the WHERE clause (ListingMiscellaneousCode, MiscellaneousCodeID, DwellingCodeID, AreaCodeID? Have you considered passing a single string for the parameter list, instead of having 100+ individual parameters? Typically you want the opposite, but in this case I think it may be justified. First I would create a numbers table, 500 rows is probably sufficient:

SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 500;

    WITH n AS
    (
        SELECT
            x = ROW_NUMBER() OVER
            (ORDER BY s1.[object_id])
        FROM       [master].sys.columns AS s1
        CROSS JOIN [master].sys.columns AS s2
    )
    SELECT [Number] = x
      INTO dbo.Numbers
      FROM n
      WHERE x BETWEEN 1 AND @UpperLimit;
    GO
    CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);
    GO

Now create a function that can parse a list of strings:

CREATE FUNCTION dbo.SplitINTs
(
    @List       VARCHAR(MAX),
    @Delimiter  NVARCHAR(10)
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT DISTINCT
            [Value] = CONVERT(INT, LTRIM(RTRIM(
                SUBSTRING(@List, [Number],
                CHARINDEX
                (
                  @Delimiter, @List + @Delimiter, [Number]
                ) - [Number]))))
        FROM
            dbo.Numbers
        WHERE
            Number <= LEN(@List)
            AND SUBSTRING
            (
              @Delimiter + @List, [Number], LEN(@Delimiter)
            ) = @Delimiter
    );
GO

Now your query can say:

DECLARE @MiscCodeIDs VARCHAR(MAX), @AreaCodeIDs VARCHAR(MAX);
SELECT @MiscCodeIDs = '1,2,3,4,5...', @AreaCodeIDs = '6,7,8,9,10...';

SELECT <obnoxiously large column list>
FROM
...
INNER JOIN dbo.AreaCodes AS t1
ON ...
INNER JOIN dbo.SplitInts(@AreaCodeIDs, N',') AS acs
ON t1.AreaCodeID = acs.[Value]
...

AND 
(
    EXISTS
    (
              SELECT 1
         FROM [dbo].[ListingMiscellaneousCode] AS [t8]
      INNER JOIN dbo.SplitInts(@MiscCodeIDs, N',') AS m
      ON m.[Value] = t8.MiscellaneousCodeID
      AND ([t8].[ListingID] = [t0].[ID])
    )
)
...

I'm assuming these IDs are INTs. If they are strings, just take out the CONVERT(INT) in the function (and you may want to use NVARCHAR in case you need to support Unicode).

Aaron Bertrand 2009-10-26 22:38:17

Answer 3

A:

First, I think you have a bug in there... the SELECT TOP 100 will pull back a random 100 and then the ORDER BY [t9].[DateCreated] DESC will sort them. This doesn't give you the last 100 created.

You can't actually need to return 59 columns? Limit this.

I think

([t0].[AreaCodeID] IN (@...

should be

[t1].[ID] IN (@...

And there should be a unique index on [dbo].[AreaCode].ID

Given that indexes perform better with a BETWEEN ran rather than all of the values spelled out I would also see if I could collapse the 100 values to something more like: [t1].[ID] BETWEEN @p1 and @p2 and [t1].[ID] in (@p3.... That might be some coding on your part.

But I would really look at where the 100 area codes came from.... you have the concept of AreaCodeGroup, but it doesn't look like it is being used.

JBrooks 2009-10-26 23:21:14

Hi JBrooks,You're right about the bug. Thanks for that.I just included the full linq query for ease. Even selecting ONLY the ID of the first table results in a long query due to the filters. I've provided the 3 things that ultimately fixed the performance below.Thanks.

Scott 2009-10-27 21:52:43

ansaurus

tags:

views:

answers:

SQLServer Query optimization question

related questions