0

We have a new production line in our factory which has a bunch of testers on. These testers are running LabView and are logging their data to an SQL database, which was generated by TestStand.

I have written a program in c# and using Entity FrameWork 6 to retrieve data based on a couple of filters i.e. date and tester number.

I am finding that querys that require retrieving data from multiple tables are running very very slow.

Here is an example of a query

using (var db = new LoggingEntities())
        {
            db.Configuration.AutoDetectChangesEnabled = false;
            db.Configuration.LazyLoadingEnabled = false;
            db.Configuration.ProxyCreationEnabled = false;
            db.Database.CommandTimeout = 5000;

            var Results = db.UUT_RESULT.Include("STEP_RESULT")
                .Include("STEP_RESULT.STEP_NUMERICLIMIT1")                    .Include("STEP_RESULT.STEP_NUMERICLIMIT1.STEP_NUMERICLIMIT2")
                .Include("STEP_RESULT.PROP_RESULT")
                .AsNoTracking()
                .Where(x => x.STATION_ID == SelectedTester && x.START_DATE_TIME.Value >= StartFilterDate && x.START_DATE_TIME.Value <= endDate).ToList();
        }

At the time of writing this, the above query has been running for 40 minutes. I estimate that in total, across all 5 tables, this is retrieving 336,000 rows.

If I run the above without the includes, the query is completed in a few seconds with 500 Rows retrieved.

Just some extra info.

SQL is running on SQLEXPRESS - Yes i know this is not great and we are planning to move this onto our SQL server.

All Primary keys in the tables are GUID data type and so are the Relational Columns.

So based on the above, is this expected? especially with SQLEXPRESS? or does anyone suspect something else is wrong?

Based on what I have to work wit, is there a better was to do this, performance wise?

EDIT: SQL Profiler Trace

exec sp_executesql N'SELECT 
[Project4].[C1] AS [C1], 
[Project4].[ID] AS [ID], 
[Project4].[STATION_ID] AS [STATION_ID], 
[Project4].[BATCH_SERIAL_NUMBER] AS [BATCH_SERIAL_NUMBER], 
[Project4].[TEST_SOCKET_INDEX] AS [TEST_SOCKET_INDEX], 
[Project4].[UUT_SERIAL_NUMBER] AS [UUT_SERIAL_NUMBER], 
[Project4].[USER_LOGIN_NAME] AS [USER_LOGIN_NAME], 
[Project4].[START_DATE_TIME] AS [START_DATE_TIME], 
[Project4].[EXECUTION_TIME] AS [EXECUTION_TIME], 
[Project4].[UUT_STATUS] AS [UUT_STATUS], 
[Project4].[UUT_ERROR_CODE] AS [UUT_ERROR_CODE], 
[Project4].[UUT_ERROR_MESSAGE] AS [UUT_ERROR_MESSAGE], 
[Project4].[PART_NUMBER] AS [PART_NUMBER], 
[Project4].[TSR_FILE_NAME] AS [TSR_FILE_NAME], 
[Project4].[TSR_FILE_ID] AS [TSR_FILE_ID], 
[Project4].[TSR_FILE_CLOSED] AS [TSR_FILE_CLOSED], 
[Project4].[StationType] AS [StationType], 
[Project4].[C57] AS [C2], 
[Project4].[C3] AS [C3], 
[Project4].[C4] AS [C4], 
[Project4].[C5] AS [C5], 
[Project4].[C6] AS [C6], 
[Project4].[C7] AS [C7], 
[Project4].[C8] AS [C8], 
[Project4].[C9] AS [C9], 
[Project4].[C10] AS [C10], 
[Project4].[C11] AS [C11], 
[Project4].[C12] AS [C12], 
[Project4].[C13] AS [C13], 
[Project4].[C14] AS [C14], 
[Project4].[C15] AS [C15], 
[Project4].[C16] AS [C16], 
[Project4].[C17] AS [C17], 
[Project4].[C18] AS [C18], 
[Project4].[C19] AS [C19], 
[Project4].[C20] AS [C20], 
[Project4].[C21] AS [C21], 
[Project4].[C22] AS [C22], 
[Project4].[C23] AS [C23], 
[Project4].[C24] AS [C24], 
[Project4].[C25] AS [C25], 
[Project4].[C2] AS [C26], 
[Project4].[C26] AS [C27], 
[Project4].[C27] AS [C28], 
[Project4].[C28] AS [C29], 
[Project4].[C29] AS [C30], 
[Project4].[C30] AS [C31], 
[Project4].[C31] AS [C32], 
[Project4].[C32] AS [C33], 
[Project4].[C33] AS [C34], 
[Project4].[C34] AS [C35], 
[Project4].[C35] AS [C36], 
[Project4].[C36] AS [C37], 
[Project4].[C37] AS [C38], 
[Project4].[C38] AS [C39], 
[Project4].[C39] AS [C40], 
[Project4].[C40] AS [C41], 
[Project4].[C41] AS [C42], 
[Project4].[C42] AS [C43], 
[Project4].[C43] AS [C44], 
[Project4].[C44] AS [C45], 
[Project4].[C45] AS [C46], 
[Project4].[C46] AS [C47], 
[Project4].[C47] AS [C48], 
[Project4].[C48] AS [C49], 
[Project4].[C49] AS [C50], 
[Project4].[C50] AS [C51], 
[Project4].[C51] AS [C52], 
[Project4].[C52] AS [C53], 
[Project4].[C53] AS [C54], 
[Project4].[C54] AS [C55], 
[Project4].[C55] AS [C56], 
[Project4].[C56] AS [C57]
FROM ( SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[STATION_ID] AS [STATION_ID], 
    [Project1].[BATCH_SERIAL_NUMBER] AS [BATCH_SERIAL_NUMBER], 
    [Project1].[TEST_SOCKET_INDEX] AS [TEST_SOCKET_INDEX], 
    [Project1].[UUT_SERIAL_NUMBER] AS [UUT_SERIAL_NUMBER], 
    [Project1].[USER_LOGIN_NAME] AS [USER_LOGIN_NAME], 
    [Project1].[START_DATE_TIME] AS [START_DATE_TIME], 
    [Project1].[EXECUTION_TIME] AS [EXECUTION_TIME], 
    [Project1].[UUT_STATUS] AS [UUT_STATUS], 
    [Project1].[UUT_ERROR_CODE] AS [UUT_ERROR_CODE], 
    [Project1].[UUT_ERROR_MESSAGE] AS [UUT_ERROR_MESSAGE], 
    [Project1].[PART_NUMBER] AS [PART_NUMBER], 
    [Project1].[TSR_FILE_NAME] AS [TSR_FILE_NAME], 
    [Project1].[TSR_FILE_ID] AS [TSR_FILE_ID], 
    [Project1].[TSR_FILE_CLOSED] AS [TSR_FILE_CLOSED], 
    [Project1].[StationType] AS [StationType], 
    [Project1].[C1] AS [C1], 
    [UnionAll1].[C1] AS [C2], 
    [UnionAll1].[C2] AS [C3], 
    [UnionAll1].[ID] AS [C4], 
    [UnionAll1].[UUT_RESULT] AS [C5], 
    [UnionAll1].[STEP_PARENT] AS [C6], 
    [UnionAll1].[ORDER_NUMBER] AS [C7], 
    [UnionAll1].[STEP_NAME] AS [C8], 
    [UnionAll1].[STEP_TYPE] AS [C9], 
    [UnionAll1].[STEP_GROUP] AS [C10], 
    [UnionAll1].[STEP_INDEX] AS [C11], 
    [UnionAll1].[STEP_ID] AS [C12], 
    [UnionAll1].[STATUS] AS [C13], 
    [UnionAll1].[REPORT_TEXT] AS [C14], 
    [UnionAll1].[ERROR_CODE] AS [C15], 
    [UnionAll1].[ERROR_MESSAGE] AS [C16], 
    [UnionAll1].[CAUSED_SEQFAIL] AS [C17], 
    [UnionAll1].[MODULE_TIME] AS [C18], 
    [UnionAll1].[TOTAL_TIME] AS [C19], 
    [UnionAll1].[NUM_LOOPS] AS [C20], 
    [UnionAll1].[NUM_PASSED] AS [C21], 
    [UnionAll1].[NUM_FAILED] AS [C22], 
    [UnionAll1].[ENDING_LOOP_INDEX] AS [C23], 
    [UnionAll1].[LOOP_INDEX] AS [C24], 
    [UnionAll1].[INTERACTIVE_EXENUM] AS [C25], 
    [UnionAll1].[C3] AS [C26], 
    [UnionAll1].[ID1] AS [C27], 
    [UnionAll1].[STEP_RESULT] AS [C28], 
    [UnionAll1].[PROP_PARENT] AS [C29], 
    [UnionAll1].[ORDER_NUMBER1] AS [C30], 
    [UnionAll1].[NAME] AS [C31], 
    [UnionAll1].[PATH] AS [C32], 
    [UnionAll1].[CATEGORY] AS [C33], 
    [UnionAll1].[TYPE_VALUE] AS [C34], 
    [UnionAll1].[TYPE_NAME] AS [C35], 
    [UnionAll1].[DISPLAY_FORMAT] AS [C36], 
    [UnionAll1].[DATA] AS [C37], 
    [UnionAll1].[C4] AS [C38], 
    [UnionAll1].[ID2] AS [C39], 
    [UnionAll1].[PROP_RESULT] AS [C40], 
    [UnionAll1].[COMP_OPERATOR] AS [C41], 
    [UnionAll1].[HIGH_LIMIT] AS [C42], 
    [UnionAll1].[LOW_LIMIT] AS [C43], 
    [UnionAll1].[UNITS] AS [C44], 
    [UnionAll1].[STATUS1] AS [C45], 
    [UnionAll1].[C5] AS [C46], 
    [UnionAll1].[C6] AS [C47], 
    [UnionAll1].[C7] AS [C48], 
    [UnionAll1].[C8] AS [C49], 
    [UnionAll1].[C9] AS [C50], 
    [UnionAll1].[C10] AS [C51], 
    [UnionAll1].[C11] AS [C52], 
    [UnionAll1].[C12] AS [C53], 
    [UnionAll1].[C13] AS [C54], 
    [UnionAll1].[C14] AS [C55], 
    [UnionAll1].[C15] AS [C56], 
    CASE WHEN ([UnionAll1].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C57]
    FROM   (SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[STATION_ID] AS [STATION_ID], 
        [Extent1].[BATCH_SERIAL_NUMBER] AS [BATCH_SERIAL_NUMBER], 
        [Extent1].[TEST_SOCKET_INDEX] AS [TEST_SOCKET_INDEX], 
        [Extent1].[UUT_SERIAL_NUMBER] AS [UUT_SERIAL_NUMBER], 
        [Extent1].[USER_LOGIN_NAME] AS [USER_LOGIN_NAME], 
        [Extent1].[START_DATE_TIME] AS [START_DATE_TIME], 
        [Extent1].[EXECUTION_TIME] AS [EXECUTION_TIME], 
        [Extent1].[UUT_STATUS] AS [UUT_STATUS], 
        [Extent1].[UUT_ERROR_CODE] AS [UUT_ERROR_CODE], 
        [Extent1].[UUT_ERROR_MESSAGE] AS [UUT_ERROR_MESSAGE], 
        [Extent1].[PART_NUMBER] AS [PART_NUMBER], 
        [Extent1].[TSR_FILE_NAME] AS [TSR_FILE_NAME], 
        [Extent1].[TSR_FILE_ID] AS [TSR_FILE_ID], 
        [Extent1].[TSR_FILE_CLOSED] AS [TSR_FILE_CLOSED], 
        [Extent1].[StationType] AS [StationType], 
        1 AS [C1]
        FROM [dbo].[UUT_RESULT] AS [Extent1]
        WHERE (([Extent1].[STATION_ID] = @p__linq__0) OR (([Extent1].[STATION_ID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent1].[START_DATE_TIME] >= @p__linq__1) AND ([Extent1].[START_DATE_TIME] <= @p__linq__2) ) AS [Project1]
    OUTER APPLY  (SELECT 
        CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        1 AS [C2], 
        [Extent2].[ID] AS [ID], 
        [Extent2].[UUT_RESULT] AS [UUT_RESULT], 
        [Extent2].[STEP_PARENT] AS [STEP_PARENT], 
        [Extent2].[ORDER_NUMBER] AS [ORDER_NUMBER], 
        [Extent2].[STEP_NAME] AS [STEP_NAME], 
        [Extent2].[STEP_TYPE] AS [STEP_TYPE], 
        [Extent2].[STEP_GROUP] AS [STEP_GROUP], 
        [Extent2].[STEP_INDEX] AS [STEP_INDEX], 
        [Extent2].[STEP_ID] AS [STEP_ID], 
        [Extent2].[STATUS] AS [STATUS], 
        [Extent2].[REPORT_TEXT] AS [REPORT_TEXT], 
        [Extent2].[ERROR_CODE] AS [ERROR_CODE], 
        [Extent2].[ERROR_MESSAGE] AS [ERROR_MESSAGE], 
        [Extent2].[CAUSED_SEQFAIL] AS [CAUSED_SEQFAIL], 
        [Extent2].[MODULE_TIME] AS [MODULE_TIME], 
        [Extent2].[TOTAL_TIME] AS [TOTAL_TIME], 
        [Extent2].[NUM_LOOPS] AS [NUM_LOOPS], 
        [Extent2].[NUM_PASSED] AS [NUM_PASSED], 
        [Extent2].[NUM_FAILED] AS [NUM_FAILED], 
        [Extent2].[ENDING_LOOP_INDEX] AS [ENDING_LOOP_INDEX], 
        [Extent2].[LOOP_INDEX] AS [LOOP_INDEX], 
        [Extent2].[INTERACTIVE_EXENUM] AS [INTERACTIVE_EXENUM], 
        CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3], 
        [Join1].[ID1] AS [ID1], 
        [Join1].[STEP_RESULT] AS [STEP_RESULT], 
        [Join1].[PROP_PARENT] AS [PROP_PARENT], 
        [Join1].[ORDER_NUMBER] AS [ORDER_NUMBER1], 
        [Join1].[NAME] AS [NAME], 
        [Join1].[PATH] AS [PATH], 
        [Join1].[CATEGORY] AS [CATEGORY], 
        [Join1].[TYPE_VALUE] AS [TYPE_VALUE], 
        [Join1].[TYPE_NAME] AS [TYPE_NAME], 
        [Join1].[DISPLAY_FORMAT] AS [DISPLAY_FORMAT], 
        [Join1].[DATA] AS [DATA], 
        CASE WHEN ([Join1].[ID1] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[ID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C4], 
        [Join1].[ID2] AS [ID2], 
        [Join1].[PROP_RESULT] AS [PROP_RESULT], 
        [Join1].[COMP_OPERATOR] AS [COMP_OPERATOR], 
        [Join1].[HIGH_LIMIT] AS [HIGH_LIMIT], 
        [Join1].[LOW_LIMIT] AS [LOW_LIMIT], 
        [Join1].[UNITS] AS [UNITS], 
        [Join1].[STATUS] AS [STATUS1], 
        CAST(NULL AS uniqueidentifier) AS [C5], 
        CAST(NULL AS uniqueidentifier) AS [C6], 
        CAST(NULL AS uniqueidentifier) AS [C7], 
        CAST(NULL AS int) AS [C8], 
        CAST(NULL AS varchar(1)) AS [C9], 
        CAST(NULL AS varchar(1)) AS [C10], 
        CAST(NULL AS int) AS [C11], 
        CAST(NULL AS int) AS [C12], 
        CAST(NULL AS varchar(1)) AS [C13], 
        CAST(NULL AS varchar(1)) AS [C14], 
        CAST(NULL AS varchar(1)) AS [C15]
        FROM  [dbo].[STEP_RESULT] AS [Extent2]
        LEFT OUTER JOIN  (SELECT [Extent3].[ID] AS [ID1], [Extent3].[STEP_RESULT] AS [STEP_RESULT], [Extent3].[PROP_PARENT] AS [PROP_PARENT], [Extent3].[ORDER_NUMBER] AS [ORDER_NUMBER], [Extent3].[NAME] AS [NAME], [Extent3].[PATH] AS [PATH], [Extent3].[CATEGORY] AS [CATEGORY], [Extent3].[TYPE_VALUE] AS [TYPE_VALUE], [Extent3].[TYPE_NAME] AS [TYPE_NAME], [Extent3].[DISPLAY_FORMAT] AS [DISPLAY_FORMAT], [Extent3].[DATA] AS [DATA], [Extent4].[ID] AS [ID2], [Extent4].[PROP_RESULT] AS [PROP_RESULT], [Extent4].[COMP_OPERATOR] AS [COMP_OPERATOR], [Extent4].[HIGH_LIMIT] AS [HIGH_LIMIT], [Extent4].[LOW_LIMIT] AS [LOW_LIMIT], [Extent4].[UNITS] AS [UNITS], [Extent4].[STATUS] AS [STATUS]
            FROM  [dbo].[STEP_NUMERICLIMIT1] AS [Extent3]
            LEFT OUTER JOIN [dbo].[STEP_NUMERICLIMIT2] AS [Extent4] ON [Extent3].[ID] = [Extent4].[PROP_RESULT] ) AS [Join1] ON [Extent2].[ID] = [Join1].[STEP_RESULT]
        WHERE [Project1].[ID] = [Extent2].[UUT_RESULT]
    UNION ALL
        SELECT 
        2 AS [C1], 
        2 AS [C2], 
        [Extent5].[ID] AS [ID], 
        [Extent5].[UUT_RESULT] AS [UUT_RESULT], 
        [Extent5].[STEP_PARENT] AS [STEP_PARENT], 
        [Extent5].[ORDER_NUMBER] AS [ORDER_NUMBER], 
        [Extent5].[STEP_NAME] AS [STEP_NAME], 
        [Extent5].[STEP_TYPE] AS [STEP_TYPE], 
        [Extent5].[STEP_GROUP] AS [STEP_GROUP], 
        [Extent5].[STEP_INDEX] AS [STEP_INDEX], 
        [Extent5].[STEP_ID] AS [STEP_ID], 
        [Extent5].[STATUS] AS [STATUS], 
        [Extent5].[REPORT_TEXT] AS [REPORT_TEXT], 
        [Extent5].[ERROR_CODE] AS [ERROR_CODE], 
        [Extent5].[ERROR_MESSAGE] AS [ERROR_MESSAGE], 
        [Extent5].[CAUSED_SEQFAIL] AS [CAUSED_SEQFAIL], 
        [Extent5].[MODULE_TIME] AS [MODULE_TIME], 
        [Extent5].[TOTAL_TIME] AS [TOTAL_TIME], 
        [Extent5].[NUM_LOOPS] AS [NUM_LOOPS], 
        [Extent5].[NUM_PASSED] AS [NUM_PASSED], 
        [Extent5].[NUM_FAILED] AS [NUM_FAILED], 
        [Extent5].[ENDING_LOOP_INDEX] AS [ENDING_LOOP_INDEX], 
        [Extent5].[LOOP_INDEX] AS [LOOP_INDEX], 
        [Extent5].[INTERACTIVE_EXENUM] AS [INTERACTIVE_EXENUM], 
        CAST(NULL AS int) AS [C3], 
        CAST(NULL AS uniqueidentifier) AS [C4], 
        CAST(NULL AS uniqueidentifier) AS [C5], 
        CAST(NULL AS uniqueidentifier) AS [C6], 
        CAST(NULL AS int) AS [C7], 
        CAST(NULL AS varchar(1)) AS [C8], 
        CAST(NULL AS varchar(1)) AS [C9], 
        CAST(NULL AS int) AS [C10], 
        CAST(NULL AS int) AS [C11], 
        CAST(NULL AS varchar(1)) AS [C12], 
        CAST(NULL AS varchar(1)) AS [C13], 
        CAST(NULL AS float) AS [C14], 
        CAST(NULL AS int) AS [C15], 
        CAST(NULL AS uniqueidentifier) AS [C16], 
        CAST(NULL AS uniqueidentifier) AS [C17], 
        CAST(NULL AS varchar(1)) AS [C18], 
        CAST(NULL AS float) AS [C19], 
        CAST(NULL AS float) AS [C20], 
        CAST(NULL AS varchar(1)) AS [C21], 
        CAST(NULL AS varchar(1)) AS [C22], 
        [Extent6].[ID] AS [ID1], 
        [Extent6].[STEP_RESULT] AS [STEP_RESULT], 
        [Extent6].[PROP_PARENT] AS [PROP_PARENT], 
        [Extent6].[ORDER_NUMBER] AS [ORDER_NUMBER1], 
        [Extent6].[NAME] AS [NAME], 
        [Extent6].[PATH] AS [PATH], 
        [Extent6].[CATEGORY] AS [CATEGORY], 
        [Extent6].[TYPE_VALUE] AS [TYPE_VALUE], 
        [Extent6].[TYPE_NAME] AS [TYPE_NAME], 
        [Extent6].[DISPLAY_FORMAT] AS [DISPLAY_FORMAT], 
        [Extent6].[DATA] AS [DATA]
        FROM  [dbo].[STEP_RESULT] AS [Extent5]
        INNER JOIN [dbo].[PROP_RESULT] AS [Extent6] ON [Extent5].[ID] = [Extent6].[STEP_RESULT]
        WHERE [Project1].[ID] = [Extent5].[UUT_RESULT]) AS [UnionAll1]
)  AS [Project4]
ORDER BY [Project4].[ID] ASC, [Project4].[C57] ASC, [Project4].[C4] ASC, [Project4].[C2] ASC, [Project4].[C27] ASC, [Project4].[C38] ASC',N'@p__linq__0 varchar(8000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',@p__linq__0='SIDEPCBA',@p__linq__1='2017-08-03 00:00:00',@p__linq__2='2017-08-03 23:59:00'
Gaz83
  • 2,293
  • 4
  • 32
  • 57
  • Can you post here the query generated by your code? – sepupic Aug 04 '17 at 10:34
  • @sepupic added, hope it helps – Gaz83 Aug 04 '17 at 11:06
  • This is not the whole query. Can you post the actual executionplan aswell? – Peter Aug 04 '17 at 11:10
  • This is not a query passed to server. The inner most SELECT has no FROM. – sepupic Aug 04 '17 at 11:11
  • oh sorry, where can I get this from? – Gaz83 Aug 04 '17 at 11:14
  • can you use Profiler? – sepupic Aug 04 '17 at 11:17
  • @sepupic Just tried that. I started a trace, pressed the button to run my query but I am not seeing anything in the trace other than this. -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed – Gaz83 Aug 04 '17 at 11:26
  • Did you select events RPCStarted/StatementStarted? – sepupic Aug 04 '17 at 11:29
  • That might help. Which line do you want? RPC:Starting or sp:StmtStarting – Gaz83 Aug 04 '17 at 11:49
  • Did you check for missing indexes on tables? Looks like a pretty blown-up query for what should be a couple of joins.. – martennis Aug 04 '17 at 12:25
  • @Gaz83, is it slow when you do not specify STATION_ID? – sepupic Aug 04 '17 at 13:06
  • @sepupic I just tried and still slow. I suspect there would be a lot more data as removing STATION_ID would result in almost quadrupling the returned row count. – Gaz83 Aug 04 '17 at 13:48
  • your problem is in parameter sniffing. You have 2 cases totally different: or output all the stations, or only one. So I think the plan was cached for one specific station_id, and the plan uses Nested Loops. When you pass in NULL as station_id or another id that produces more rows, that plan is not good. – sepupic Aug 04 '17 at 13:51
  • Did you also check if your Extent1 is indexed by [START_DATE_TIME]? – sepupic Aug 04 '17 at 13:52
  • If you can catch the plan with profiler (you should select it as event, it's not selected by default) and paste the plan here it would be better. Or you can launch the code that you captured in profiler, change the last line providing values for the parameters, and catch the actual plan in SSMS – sepupic Aug 04 '17 at 13:55
  • I tried getting the Plan in SSMS but it requires the query to complete. This is not happening as it takes too long and I end up stopping it to try other ideas. – Gaz83 Aug 04 '17 at 14:07
  • Ok, you can get an ESTIMATED execution plan without qury execution.Just to have an idea of estimations made and indexes presented – sepupic Aug 04 '17 at 14:42
  • Anyway, the best thing to fix this issue is just to rewrite the query without using any framework – sepupic Aug 04 '17 at 14:44

1 Answers1

0

If you haven't already done it - check what indexes that could/should be created for your specific query. The deault NI SQL database is not indexed or optimized in any way. Run your slow query - and then this query to check what could be done.

-- check what could be indexed
SELECT mig.*, statement AS table_name,
column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;
Anders Brink
  • 1
  • 1