Promlem with SQL-Statement

[ALBOS]

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

[StanislavK]

Could you please specify the version of dotConnect for Oracle you are using? If possible, please send us a test project and scripts needed to create the database objects.

I've tried to reproduce the situation with a simple model, but the script generated (checked it with dbMonitor) was

Code: Select all

SELECT t1.DEPTNO, t1.DNAME, t1.LOC
FROM DEPT t1
WHERE (t1.DEPTNO = :p0) AND (t1.DNAME = :p1)

The Dept table is defined as

Code: Select all

CREATE TABLE DEPT 
(
  DEPTNO NUMBER(4),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
)

[ALBOS]

Thank's for your reply. My Datasource is an Interface from another produkt. I only can use views there. The view I'm using has no primary key. Could this be the problem? Can you check this out with a view on your table? I check the SQL-statemement using LOG cause db-monitor did not work on my machine (asp webservice), but I think it's my problem.

Thanks Jürgen

[StanislavK]

I've created the following view:

Code: Select all

CREATE OR REPLACE VIEW DEPTVIEW (
  DNAME,
  LOC)
AS SELECT d.dname, d.loc FROM dept d;

and tried the same LINQ query on it. The generated SQL command was

Code: Select all

SELECT t1.DNAME, t1.LOC
FROM DEPTVIEW t1
WHERE (t1.DNAME = :p0) AND (t1.LOC = :p1)

I used the latest 5.70.140 build of dotConnect for Oracle, please specify whether you are using it as well. If no, could you please update to it? Otherwise, please send us a sample project or specify the DDL scripts needed to create database objects with which the issue can be reproduced.

JIC: it is possible to use dbMonitor in web applications. To do so, you can add an OracleMonitor instance to the Application collection:

Code: Select all

if (Application["mon"] == null)
	Application.Add("mon", new Devart.Data.Oracle.OracleMonitor() {IsActive = true});

Also, you can use an OracleMonitor object at run-time without using dbMonitor application; for details, please refer to
http://www.devart.com/dotconnect/oracle ... nt_EV.html