I have a very simple linq to sql query in C#:
int acctNum = 12345;
var query = from p in db.table
where p.ACCT_NO == acctNum
select p;
This generates the following SQL:
exec sp_executesql N'SELECT [t0].field1, [t0].field2, [t0].ACCT_NO
FROM [dbo].[table] AS [t0]
WHERE [t0].[ACCT_NO] = @p0', N'@p0 decimal(29,0)', @p0 = 12345
For some reason, this is taking an incredibly long time to run (several minutes). If I run an equivalent query in management studio (select * from table where acct_no = 12345), it takes less than a second on a very large table (~7MM rows). After some digging with the SQL profiler, I found that linq is passing the acctNum parameter as a Decimal(29,0) while the field is stored in the database as a Numeric(18,0). If I take the generated SQL and just change the parameter type from decimal to numeric, it runs in less than a second. In the profiler, I can see that the linq version uses almost 2 million reads versus about 400 for the numeric parameter query. How can I force linq to pass this parameter as numeric instead of decimal?
Most likely the problem lies with the type of p.ACCT_NO
(in other words it was probably generated as a floating-point numeric type). Make sure that this property is also typed as an int
and it should work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With