0

I'm using Entity Framework 6 Code First with an Oracle database (using Devart DotConnect for Oracle)

With a specific query with a SingleOrDefault, the query takes 1 second to complete :

-- Executing at 08.10.2019 10:04:04 +02:00
-- Completed in 1211 ms with result: d

But then, nothing happen during 3,5 minutes, until the connection is closed :

Closed connection at 08.10.2019 10:07:41 +02:00

During this time, the CPU of the database server goes up to 100%.

If I execute the same query directly on the database, it is very fast.

if I slightly modify my LINQ query, it is also very fast.

Do you have an idea what I can try to find the source of the problem and why there is a difference between executing the query directly or from Entity Framework ?

Gab
  • 1,861
  • 5
  • 26
  • 39

1 Answers1

1

  1. Enable dbMonitor and specify SQL generated by EF provider.

  2. Check the Parameters window in dbMonitor and make sure that the datatypes of parameters used in WHERE condition are the same as the datatypes of the corresponding columns.

  3. Tell us the version and edition of your Oracle Server.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Thank you. The parameters type is "Varchar" and the column type is "varchar2(4000)". The DB version is Oracle 11g - 11.2.0.3.0 – Gab Oct 09 '19 at 07:43
  • 1. For test purposes, add "Pooling=false;" to your connection string. Does the issue persist in this case? – Devart Oct 12 '19 at 17:11
  • 2. Is the problem specific to a particular table? Or it occurs with all tables and all queries? – Devart Oct 12 '19 at 17:12
  • 1. Yes, the problem persist. 2. The problem is specific to a particular table , and even to a particual query, on a particular customer database (happen twice already). Removing a JOIN or a WHERE condition, the query returns instantly... It could be complicated to find the source of the problem. What I'm trying to understand is why the query is fast if I run it directly on the database. Maybe you have an idea? – Gab Oct 14 '19 at 07:35
  • 1. Give us your LINQ query. 2. Turn on dbMonitor and specify the generated SQL statement and the datatypes of its parameters. 3. Run the same SQL query via OracleCommand, is the performance issue the same as via EF LINQ query? – Devart Oct 14 '19 at 12:31
  • I was finally able to reproduce the problem with OracleCommand and directly on the database... So Devart is not the problem, sorry. – Gab Oct 14 '19 at 14:19