LINQ to SQL for Oracle and bind variable naming

LINQ to SQL for Oracle and bind variable naming

Postby sclegg » Sat 05 Dec 2009 22:03

Hi all,

In C#, when using LINQ to SQL for Oracle database (v5.25.42.0) with ASP.NET MVC 3.5, the bind variable changed on every execution. On the first execution of the LINQ to SQL query with the where clause, the DBMonitor shows the bind variable of :p__linq__1. On the second execution of the same exact LINQ to SQL query during the same session, the DB Monitor shows the bind variable of :p__linq__2. This name should stay the same as :p__linq__1 not :p__linq__2. Otherwise, the Oracle database treats the two sqls as two separate executions. This is a significant performance issue. Is there a way to control the naming of the bind variable? Thanks in advance!

see this article for details: http://www.oracle-base.com/articles/mis ... iables.php
sclegg
 
Posts: 6
Joined: Fri 11 Sep 2009 15:51
Location: Idaho

Postby AndreyR » Mon 07 Dec 2009 12:53

Thank you for the inquiry, we are investigating the issue.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Details on this issue:

Postby sclegg » Fri 18 Dec 2009 15:08

Thank you for checking into this issue. I believe the parsing issue is on the naming for the bind variable. For a web application, I'm using a connection pool with of course closing the connection when completing a transaction or query. Since this is a web based application, I'm not cannot just close the web based application. The following is a sample of a source code where a button on a web page calls this method in C#:

public List fetchQryLINQ(DateTime param1
, DateTime param2)
{
DataSourceModel1.XEntities db = null;

try
{

string sConnStrg = ConfigurationManager.ConnectionStrings["MyConnString1"].ConnectionString;
db = new DataSourceModel1.XEntities(sConnStrg);

var myList = (from a in db.XTxns
where a.EventTimestamp >= param1
&& a.EventTimestamp <= param2
select a).ToList();
return myList;
}
finally
{
db.Connection.Close();
}


}


After clicking on the button twice, the dbMonitor shows the following where clause in two separate timestamp event:
1)
SELECT ....
FROM X_TXN "Extent1"
WHERE ("Extent1".EVENT_TIMESTAMP >= :p__linq__1) AND ("Extent1".EVENT_TIMESTAMP <= :p__linq__2)
-- -1 row(s) affected.

2)
SELECT ....
FROM X_TXN "Extent1"
WHERE ("Extent1".EVENT_TIMESTAMP >= :p__linq__3) AND ("Extent1".EVENT_TIMESTAMP <= :p__linq__4)

-- -1 row(s) affected.

The dbMonitor shows the connection as closed after each above query transaction.

The problem is the unique bind variable. Oracle cannot reused the first query because the variable is different. Do you see the issue?
sclegg
 
Posts: 6
Joined: Fri 11 Sep 2009 15:51
Location: Idaho

Postby AndreyR » Fri 18 Dec 2009 16:18

Thank you for the clarification.
We will let you know as soon as the results are available.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Mon 21 Dec 2009 11:34

These queries are generated by Entity Framework and we don't have technical possibility to influence this process.
I recommend you to create CompiledQuery and call it in case if you need better performance.
More information about compiled queries is available here:
http://msdn.microsoft.com/en-us/library ... query.aspx
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to LinqConnect (LINQ to SQL support)