-
May 19th, 2011, 06:18 AM
#1
Thread Starter
Fanatic Member
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
-
May 19th, 2011, 06:33 AM
#2
Thread Starter
Fanatic Member
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
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
-
May 19th, 2011, 07:18 AM
#3
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.
-
May 19th, 2011, 08:09 AM
#4
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 + ''
-
May 19th, 2011, 09:59 AM
#5
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|