Query taking much longer than it should

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
xolartek
Posts: 4
Joined: Fri 14 Oct 2011 16:24

Query taking much longer than it should

Post by xolartek » Fri 14 Oct 2011 16:35

We are running a query against an Oracle datasource that when run on a SQL developer only takes 0.04 seconds. This test query only fetches 20 rows. Running a mock test in Visual Studio the same query is running more than 1.5 seconds. The web browsers are also all clocking at around 1.5 seconds. In a production environment, in which we expect thousands of records, this query will take much longer. Please recommend a solution. Thank you.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 17 Oct 2011 15:12

Please describe the query with which the performance problem occurs and the table(s) being queried, so that we are able to analyze the situation in more details. If possible, please send us a small test project with which it can be reproduced.

Generally, LinqConnect performs the following when executing a query:
- translates it into SQL statements;
- executes it on the server;
- materializes the result.
Provided that the SQL command is the same, the problem should be caused by either the first or the latter step. For example, materialization may be slow if the result set contains a column with large binary data; in this case, you can enable deferred loading for this column to better the performance.

xolartek
Posts: 4
Joined: Fri 14 Oct 2011 16:24

Post by xolartek » Mon 17 Oct 2011 21:24

The following is a unit test that takes 3.76 seconds:

[TestMethod]
public void GetParts_Should_Return_Parts()
{
// Arrange
var monitor = new OracleMonitor { IsActive = true };
var anyPager = new Pager { PageIndex = 1, ItemsPerPage = 20};
var sorterWithValidSortedBy = new Sorter() {SortedBy = "partnumber", Direction = SortDirection.Descending};
var partsList = new List {
new Part{PartNumber = "xxxxxx", Description = "Part Number 1"},
new Part{PartNumber = "xxxxxx", Description = "Part Number 2"},
new Part{PartNumber = "xxxxxx", Description = "Part Number 3"},
};

var failure = sut.Get(anyPager, sorterWithValidSortedBy);

var failedItems = failure.ToList();

// Act
Assert.IsTrue(failedItems.Count > 0, "GetParts did not return any partToBeUpdated");
}

xolartek
Posts: 4
Joined: Fri 14 Oct 2011 16:24

Post by xolartek » Mon 17 Oct 2011 21:27

The following is the generated SQL statement:

SELECT
"Project1".SS_COUNT AS SS_COUNT,
"Project1".INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
"Project1".PART_NUMBER AS PART_NUMBER,
"Project1".DESCRIPTION AS DESCRIPTION,
"Project1".ORDER_BY_DATE AS ORDER_BY_DATE,
"Project1".LIST_PRICE AS LIST_PRICE,
"Project1".ITEM_COST AS ITEM_COST,
"Project1".INTERNAL_COMMENTS AS INTERNAL_COMMENTS,
"Project1".CM_COMMENTS_TO_MKTG AS CM_COMMENTS_TO_MKTG,
"Project1".LAST_UPDATE_DATE AS LAST_UPDATE_DATE,
"Project1".LAST_UPDATED_BY AS LAST_UPDATED_BY,
"Project1".CREATION_DATE AS CREATION_DATE,
"Project1".C1 AS C1,
"Project1".MKTG_COMMENTS_TO_CM AS MKTG_COMMENTS_TO_CM
FROM (
SELECT
"Project1".INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
"Project1".SS_COUNT AS SS_COUNT,
"Project1".PART_NUMBER AS PART_NUMBER,
"Project1".DESCRIPTION AS DESCRIPTION,
"Project1".LIST_PRICE AS LIST_PRICE,
"Project1".ITEM_COST AS ITEM_COST,
"Project1".ORDER_BY_DATE AS ORDER_BY_DATE,
"Project1".CREATION_DATE AS CREATION_DATE,
"Project1".LAST_UPDATE_DATE AS LAST_UPDATE_DATE,
"Project1".LAST_UPDATED_BY AS LAST_UPDATED_BY,
"Project1".INTERNAL_COMMENTS AS INTERNAL_COMMENTS,
"Project1".CM_COMMENTS_TO_MKTG AS CM_COMMENTS_TO_MKTG,
"Project1".MKTG_COMMENTS_TO_CM AS MKTG_COMMENTS_TO_CM,
"Project1".C1 AS C1, row_number() OVER (ORDER BY "Project1".PART_NUMBER DESC) AS "row_number"
FROM ( SELECT
"Extent1".INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
"Extent1".SS_COUNT AS SS_COUNT,
"Extent1".PART_NUMBER AS PART_NUMBER,
"Extent1".DESCRIPTION AS DESCRIPTION,
"Extent1".LIST_PRICE AS LIST_PRICE,
"Extent1".ITEM_COST AS ITEM_COST,
"Extent1".ORDER_BY_DATE AS ORDER_BY_DATE,
"Extent1".CREATION_DATE AS CREATION_DATE,
"Extent1".LAST_UPDATE_DATE AS LAST_UPDATE_DATE,
"Extent1".LAST_UPDATED_BY AS LAST_UPDATED_BY,
"Extent1".INTERNAL_COMMENTS AS INTERNAL_COMMENTS,
"Extent1".CM_COMMENTS_TO_MKTG AS CM_COMMENTS_TO_MKTG,
"Extent1".MKTG_COMMENTS_TO_CM AS MKTG_COMMENTS_TO_CM,
"Extent1".EXPECTED_DATE AS C1
FROM APPS.XX_MAS_CM_QUEUE_DETAILS_V "Extent1"
) "Project1"
ORDER BY "Project1".PART_NUMBER DESC
) "Project1"
WHERE "Project1"."row_number" > 0 AND ROWNUM <= 20
ORDER BY "Project1".PART_NUMBER DESC

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

Post by Shalex » Wed 19 Oct 2011 16:50

Please refer to the Performance Considerations (Entity Framework) article (http://msdn.microsoft.com/en-us/library/cc853327.aspx) to learn the stages of query execution. This explains why EF query can take more time comparing to plain SQL execution.

Post Reply