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

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

---

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.

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

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

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