Promlem with SQL-Statement
Posted: Thu 24 Jun 2010 08:54
Hello,
I'm using LINQ like this
Dim vElements= (From p In m_DataContext.SCI_TABLE _
Where p.PARAMETER0 = pParamer0 And _
p.PARAMTER1 = pParameter1 _
Select p).ToList
The generated SQL-Text (.LOG) is:
SELECT t1. ...
FROM SCI_TABLE t1
WHERE (COALESCE(
(CASE
WHEN (t1.PARAMTER0 = :p0) AND (t1.PARAMETER1 = :p1) THEN 1
ELSE 0
END),:p2)) 0
ParameterName = p0
DbType = String
Value = 789
ParameterName = p1
DbType = Decimal
Value = 1
ParameterName = p2
DbType = Decimal
Value = False
This statement is very slow on my Database.
I'm not a db specialist, but I think, the following statement should be right:
SELECT t1. ...
FROM SCI_TABLE t1
WHERE t1.PARAMTER0 = :p0 AND t1.PARAMETER1 = :p1
This statement is very fast on my Database. Can you please discribe the reason for your statemnet. How can I optimize my db for speeding up the statement.
Thanks a lot
Juergen
I'm using LINQ like this
Dim vElements= (From p In m_DataContext.SCI_TABLE _
Where p.PARAMETER0 = pParamer0 And _
p.PARAMTER1 = pParameter1 _
Select p).ToList
The generated SQL-Text (.LOG) is:
SELECT t1. ...
FROM SCI_TABLE t1
WHERE (COALESCE(
(CASE
WHEN (t1.PARAMTER0 = :p0) AND (t1.PARAMETER1 = :p1) THEN 1
ELSE 0
END),:p2)) 0
ParameterName = p0
DbType = String
Value = 789
ParameterName = p1
DbType = Decimal
Value = 1
ParameterName = p2
DbType = Decimal
Value = False
This statement is very slow on my Database.
I'm not a db specialist, but I think, the following statement should be right:
SELECT t1. ...
FROM SCI_TABLE t1
WHERE t1.PARAMTER0 = :p0 AND t1.PARAMETER1 = :p1
This statement is very fast on my Database. Can you please discribe the reason for your statemnet. How can I optimize my db for speeding up the statement.
Thanks a lot
Juergen