Performance issues with complicated SQLs

Performance issues with complicated SQLs

Postby 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/DotConnectPerformance.rar 6-7 second per SQL project. If needed, can try to make it even more CPU-hungry.
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby motuzko » Wed 27 Apr 2016 17:24

Is anyone looking at it?
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby Shalex » Wed 27 Apr 2016 17:31

We are investigating your project. We will notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Postby motuzko » Thu 28 Apr 2016 18:45

Thanks,
Appreciate it.
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby motuzko » Thu 05 May 2016 18:09

Any Questions? Progress? Estimations?
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby Shalex » Tue 10 May 2016 13:19

The investigation is in progress. As soon as we have any results, we will let you know.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Postby motuzko » Thu 19 May 2016 15:03

Is there a hope at least? :)
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Postby 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?
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Performance issues with complicated SQLs

Postby 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?
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Re: Performance issues with complicated SQLs

Postby 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-optimizer-hints-in-entity-framework.html.
3. Entity Developer allows to implement:
* large model splitting (http://blog.devart.com/working-with-large-models-and-oracle-autoincrement-fields-in-new-version-of-entity-developer.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-ef-code-first-dbcontext-template.html)

JIC: the fastest way of reading data is usage of OracleDataReader.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle