Hi,
I'm using EF4 with dotConnect for Oracle (7.7.267.0) and I experience low performance with queries resulting many objects.
If I debug my code the ToArray call on my query executes longer than 5 minutes when the result is around 5000 objects.
I've checked the db activity with dbMonitor and it shows that the query prepare and execute times are less than 1 sec (although this duration times don't look realistic).
When I run the same query in SQL developer (also from remote computer) it also execute in less than 10 seconds.
I've checked the network activity with a network analyzer and it shown that there are 2-3 times more TCP packets during this query execution than the number of resulting rows.
For me it looks like the cause of the low performance is the big network overhead of around 15000 network packets.
Is this normal? Can I change this behaviour somehow?
Any ideas in which direction should I proceed with the investigation?
Thx in advance!
One query - many TCP packets
Re: One query - many TCP packets
1 sec is a time of server response (it can be "error" or "success, wait for result set").pogib wrote:If I debug my code the ToArray call on my query executes longer than 5 minutes when the result is around 5000 objects.
I've checked the db activity with dbMonitor and it shows that the query prepare and execute times are less than 1 sec (although this duration times don't look realistic).
5 minutes is a time of starting Entity Framework infrastructure, preparing EF model, fetching and materializing.
Does it fetch all the records? It may display only first, for example, 100 records. The rest will be fetched step by step when you scroll the grid.pogib wrote:When I run the same query in SQL developer (also from remote computer) it also execute in less than 10 seconds.
Additionally, SQL Developer has less overhead expenses comparing to retrieving data via any ORM.
Please try using OCI connection (via Oracle client). Is there a significant difference between Direct Mode and OCI in your environment?pogib wrote:I've checked the network activity with a network analyzer and it shown that there are 2-3 times more TCP packets during this query execution than the number of resulting rows.
Usage of any ORM usually leads to performance losses. In case of Entity Framework, there are a number of ways for optimization: http://msdn.microsoft.com/en-us/library/cc853327.aspx.pogib wrote:Any ideas in which direction should I proceed with the investigation?
Re: One query - many TCP packets
Hi Shalex,
Thx for the reply!
So I'm still curious: Is this number of TCP packets normal? Or any other hints?
Thx for the reply!
I run the query with F5 in Sql Developer - in this case it fetches all results.Does it fetch all the records? It may display only first, for example, 100 records. The rest will be fetched step by step when you scroll the grid.
Additionally, SQL Developer has less overhead expenses comparing to retrieving data via any ORM.
I've just tried - no significant differences. Execution of that query (5000 rows) is still around 5 minutes - in SQL Developer less than 10 seconds. Network analyzer shows the same activity (appr. 20 thousand TCP packets).Please try using OCI connection (via Oracle client). Is there a significant difference between Direct Mode and OCI in your environment?
I understand that difference, but 5 minutes vs. 10 seconds seems too much for me... Tracking is switched off and the query joins only two tables with 5-15 columns without any big binary or string data.Usage of any ORM usually leads to performance losses. In case of Entity Framework, there are a number of ways for optimization: http://msdn.microsoft.com/en-us/library/cc853327.aspx.
So I'm still curious: Is this number of TCP packets normal? Or any other hints?
Re: One query - many TCP packets
Please try suggestions from http://msdn.microsoft.com/en-us/library/cc853327.aspx.