Promlem with SQL-Statement

Promlem with SQL-Statement

Postby ALBOS » 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
ALBOS
 
Posts: 5
Joined: Mon 18 Jan 2010 16:59

Postby StanislavK » Tue 29 Jun 2010 17:21

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)
)
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby ALBOS » Fri 02 Jul 2010 06:50

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
ALBOS
 
Posts: 5
Joined: Mon 18 Jan 2010 16:59

Postby StanislavK » Fri 02 Jul 2010 11:40

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/docs/Devart.Data~Devart.Common.DbMonitor~TraceEvent_EV.html
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle