One query - many TCP packets

[pogib]

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!

[Shalex]

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).

1 sec is a time of server response (it can be "error" or "success, wait for result set").
5 minutes is a time of starting Entity Framework infrastructure, preparing EF model, fetching and materializing.

When I run the same query in SQL developer (also from remote computer) it also execute in less than 10 seconds.

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 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.

Please try using OCI connection (via Oracle client). Is there a significant difference between Direct Mode and OCI in your environment?

Any ideas in which direction should I proceed with the investigation?

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]

Hi Shalex,

Thx for the reply!

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 run the query with F5 in Sql Developer - in this case it fetches all results.

Please try using OCI connection (via Oracle client). Is there a significant difference between Direct Mode and OCI in your environment?

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).

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.

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.

So I'm still curious: Is this number of TCP packets normal? Or any other hints?

[Shalex]

Please try suggestions from http://msdn.microsoft.com/en-us/library/cc853327.aspx.