December 7, 2012 at 4:12 am
Hi, I hope someone can help with the following:
In this example, I have two tables:
Unique codes - which has around 10 million rows of data in.
(id bigint,
Code nvarch(20)
ExternalOderNumber nvarch(200)
)
Unique code status - which has 3 status' in (created, active, destroyed)
(id int,
statuscode nvarchar(20)
)
Our applications team are using Entity Framework for the app and it is generating the following query to check if a particular order, (in this case DV37196), is of an active status. If it is set a bit to true, or set it to false.
SELECT
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[UniqueCodes] AS [Extent1]
INNER JOIN [dbo].[UniqueCodeStatus] AS [Extent2] ON [Extent1].[StatusId] = [Extent2].[Id]
WHERE ([Extent1].[ExternalOrderNumber] = 'DV37196') AND ([Extent2].[StatusCode] = 'Active')
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[UniqueCodes] AS [Extent3]
INNER JOIN [dbo].[UniqueCodeStatus] AS [Extent4] ON [Extent3].[StatusId] = [Extent4].[Id]
WHERE ([Extent3].[ExternalOrderNumber] = 'DV37196') AND ([Extent4].[StatusCode] = 'Active')
)) THEN cast(0 as bit) END AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
There are 658,329 rows of order type 'DV37196' and all are at a non-active status, so the resule should be C1 = 0.
This query is taking over 12 minutes to run and so the application is timing out.
All the orders are set to a Created status, so changing the query to look for the order at a created status, gets a result back after just 12 seconds.
There are indexes on the table for the ExternalOrderNumber and the explain plan shows these are used. Running a similar query which just returns everything from the table where this particular order is 'active' takes, just 10 seconds to complete.
I realise this isn't nearly enough information to give a good answer, but can anyone explain why the entity framework generated query is showing such poor performance for the status of "Active", but execellent performance for a status of "created"?
December 7, 2012 at 4:20 am
Please provide the execution plan of the query along with any indexes which may be on the two tables in question.
December 7, 2012 at 4:20 am
Not without more information, no.
I can tell you that the generated query is awful. Of the top of my head, try this instead: -
SELECT CAST(ISNULL((SELECT TOP 1 1
FROM [dbo].[UniqueCodes] AS [Extent1]
INNER JOIN [dbo].[UniqueCodeStatus] AS [Extent2] ON [Extent1].[StatusId] = [Extent2].[Id]
WHERE [Extent1].[ExternalOrderNumber] = 'DV37196' AND [Extent2].[StatusCode] = 'Active'
),0) AS BIT) AS [C1];
December 7, 2012 at 4:38 am
Thanks all for the replies. It's amazing how explaining something to someone (or a forum!) helps you to see the wood for the trees!
Looking at the explain plan and reviewing what the query does, I realise it is scanning the index of all 658329 rows to see if any are active. For the "created" status, it just is needing to find 1 at this status, which is does on the first go and so returns quickly.
I have added a new multi column index on the statusid and order number columns, of the uniquecodes table. This has meant the index now does a seek rather than a scan and returns the results in less than a second for both types of query!
I still dont like the generated EF query though and wish the dev team werne't using it!!
Thanks again for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply