Results 1 to 5 of 5

Thread: SQL2005: Simple query performance on column with nulls

  1. #1

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    SQL2005: Simple query performance on column with nulls

    Hi,

    I'm having performance problems with a query on a table with loads of nulls in a particular column. I've tried changing the nulls to 0 (Zero) and rebuilt the index on the column but it's not really helped.

    The table only has about 350K rows this first query returns in less than a second, the MS_No column is largely populated but is nullable.

    Code:
    	SELECT Corresp_Id
    	FROM Correspondence c 
    	WHERE c.Deleted=0
    	AND c.Jnl_Code=@Jnl_Code
    	--AND c.Mtg_Id=@Mtg_Id
    	AND c.MS_No='MTG2011/0000016'
    	ORDER BY Corresp_Id DESC;
    This query on the same table but accessing a different column takes about 7 seconds.
    Code:
    	SELECT Corresp_Id
    	FROM Correspondence c 
    	WHERE c.Deleted=0
    	AND c.Jnl_Code=@Jnl_Code
    	AND c.Mtg_Id=@Mtg_Id
    	--AND c.MS_No='MTG2011/0000016'
    	ORDER BY Corresp_Id DESC;
    Does anyone have any ideas on how to improve the performance of this query?

    Regards Al

  2. #2

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: SQL2005: Simple query performance on column with nulls

    I've been doing some more testing on this and I've made the following discovery. When I hard code the value of @Mtg_Id the query returns super quick if I use the variable is takes ages?

    Very slow
    Code:
    AND c.Mtg_Id=@Mtg_Id
    Very quick
    Code:
    AND c.Mtg_Id=1
    The var Mtg_No is declared as follows-
    Code:
    DECLARE @Mtg_Id int
    The field in the DB is defined as -
    Code:
    Numeric(18,0), null

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: SQL2005: Simple query performance on column with nulls

    Try changing the variable declaration so that it is the same data type as the field - there is a good chance that you are adding a data type conversion for each record, which is where the time is going.

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL2005: Simple query performance on column with nulls

    Just something to try...enclose the variable in quotes like you do when it is fast:

    AND c.Mtg_Id= '' + @Mtg_Id + ''

  5. #5

    Thread Starter
    Fanatic Member aconybeare's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    772

    Re: SQL2005: Simple query performance on column with nulls

    Si,

    Thanks your pointer helped, I changed my variable declaration from -

    Code:
    DECLARE @Mtg_Id int
    To
    Code:
    DECLARE @Mtg_Id numeric(18,0)
    it's performing much better now.

    Tyson, thanks for your reply my field is definitely a numeric your suggestion generates an error
    Code:
    Error converting data type varchar to numeric.
    You may have been caught out by the fact that the two queries in my initial post referred to different columns MS_No and Mtg_Id.

    Cheers Al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width