Performance issues with complicated SQLs

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Performance issues with complicated SQLs

Post by motuzko » Mon 25 Apr 2016 21:30

Dear support,

Our project involves a creation of dynamic SQLs. Sometimes, to just create the SQL (not the DB execution time) it takes more than a minute (sometimes two). During the SQL creation, the CPU is heavily utilized.

Attaching a http://motuzko.com/obsilf/Downloads/Dot ... rmance.rar 6-7 second per SQL project. If needed, can try to make it even more CPU-hungry.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Wed 27 Apr 2016 17:24

Is anyone looking at it?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Wed 27 Apr 2016 17:31

We are investigating your project. We will notify you about the results as soon as possible.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Thu 28 Apr 2016 18:45

Thanks,
Appreciate it.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Thu 05 May 2016 18:09

Any Questions? Progress? Estimations?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Tue 10 May 2016 13:19

The investigation is in progress. As soon as we have any results, we will let you know.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Thu 19 May 2016 15:03

Is there a hope at least? :)

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Thu 26 May 2016 08:44

Your model is huge, the queries are too complicated. We have analysed your project: there are no particular bottlenecks in our code which lead to the performance loses. This is a resource demanding task which requires redesigning LinqConnect ORM framework.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Fri 27 May 2016 16:19

What does it mean for me?
No luck?
Can I try to get your code snapshot to profile?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Wed 01 Jun 2016 10:18

motuzko wrote:What does it mean for me?
No luck?
There is no timeframe for this task.
motuzko wrote:Can I try to get your code snapshot to profile?
You are interested in dotConnect for Oracle Source Edition, aren't you? It has the same features as the Professional Edition and includes the source code for most of the runtime classes and features. For the quote please contact us.
If that is not what you mean, please explain your question.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Sat 22 Apr 2017 16:45

Can you please guide me how to address it?
Should I change the way I query?
If I switch from LinqConnect to EF, would it perform better?
In other words, are there any ways to improve performance?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Tue 25 Apr 2017 17:09

motuzko wrote:Should I change the way I query?
If possible, try to simplify your queries.
motuzko wrote:If I switch from LinqConnect to EF, would it perform better?
There is no guarantee that EF will be faster.

If you decide to switch to EF, please take into account the following points:
1. General information about performance considerations with Entity Framework: https://msdn.microsoft.com/en-us/library/cc853327.aspx.
2. Using Oracle Optimizer Hints in Entity Framework: http://blog.devart.com/using-oracle-opt ... ework.html.
3. Entity Developer allows to implement:
* large model splitting (http://blog.devart.com/working-with-lar ... html#split)
* pregenerated views (via the View Generation property of EntityContextModel)
* the DbContext template allows to create a detailed complete mapping (http://blog.devart.com/entity-developer ... plate.html)

JIC: the fastest way of reading data is usage of OracleDataReader.

motuzko
Posts: 54
Joined: Tue 08 Sep 2009 18:02
Contact:

Re: Performance issues with complicated SQLs

Post by motuzko » Mon 13 Nov 2017 16:01

Shalex wrote:Your model is huge, the queries are too complicated. We have analysed your project: there are no particular bottlenecks in our code which lead to the performance loses. This is a resource demanding task which requires redesigning LinqConnect ORM framework.
Sorry for resurrecting and old beast, but you said "Your model is huge, the queries are too complicated". How can I make the model simpler? What is the problem? Number of tables? Number of relationships between them? Number of fields?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Fri 17 Nov 2017 15:03

motuzko wrote:What is the problem? Number of tables? Number of relationships between them? Number of fields?
All of the reasons listed complicate the model.

I have forwared your request concerning Source Edition to our Sales department.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Post by Shalex » Fri 24 Nov 2017 11:51

The performance of generating complex queries is improved: viewtopic.php?f=1&t=36257.

Post Reply