CODEWITHSUNDEEP
c#sqlsql-serverlinqentity-framework
Tom
Tom

Reputation: 2230

Query taking too long to execute in LINQ

I've written a query which should take all the rows from one table and do a subquery to a second table pulling only one value from the most recent record. In SQL Server the query takes about 15 seconds to execute and the LINQ query takes close to 2 minutes. Can someone help me with translating the SQL to LINQ, I must have done something wrong along the way.

The SQL:

SELECT  a.isrunning,
        worktype = (
                    SELECT TOP 1
                            w.worktype
                    FROM    dbo.workorder w WITH (NOLOCK)
                    WHERE   w.assetnum = a.assetnum
                    ORDER BY w.statusdate DESC
                   ),
        a.status,
        *
FROM    dbo.asset a WITH (NOLOCK)
WHERE   a.assetnum IN ('list', 'of', 'asset', 'numbers')

The LINQ Query:

(
  from a in db.assets
  let wo = (
            from w in db.workorders
            where w.assetnum == a.assetnum
            orderby w.statusdate descending
            select w).FirstOrDefault()
   where aliasStrings.Contains(a.assetnum)
   select new AssetWithWorkType {
      ...
   }
);

Upvotes: 1

Views: 1360

Answers (3)

StingyJack
StingyJack

Reputation: 19479

You can create a temp table for the correlated subquery results, and then join it later. The syntax is not correct, as I do not have your table schemas or data, but the idea is the same.

CREATE TABLE #workTypes (worktype VARCHAR(X), assetnum VARCHAR(x))

INSERT INTO #workTypes
SELECT TOP 1 worktype, assetnum FROM dbo.workorder ORDER BY statusdate DESC

SELECT  a.isrunning,
        b.worktype, 
        a.status,
        *
FROM    dbo.asset a WITH (NOLOCK)
   INNER JOIN #worktypes b
      ON a.assetnum = b.assetnum
WHERE   a.assetnum IN ('list', 'of', 'asset', 'numbers')

Upvotes: 1

johnjps111
johnjps111

Reputation: 1170

How about:

SELECT  a.isrunning,
        w.worktype,
        cnt = count(*)
FROM       dbo.asset a WITH (NOLOCK)
INNER JOIN dbo workorder w WITH (NOLOCK) on w.assetnum = a.assetnum
WHERE      a.assetnum IN ('list', 'of', 'asset', 'numbers')

This would give you a count of worktypes for each asset and might allow the database server to optimize more efficiently. Also consider adding indices or using a temp table as other answers have suggested.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

It is recommended to have indexes on foreign keys. Also indexes that covers filtering and ordering clauses. So I suggest you to create the following 3 indexes:

CREATE NONCLUSTERED INDEX [IDX_workorder_statusdate] ON dbo.workorder(statusdate)
CREATE NONCLUSTERED INDEX [IDX_workorder_assetnum] ON dbo.workorder(assetnum)

If assetnum column in asset table is not the primary key then additionally:

CREATE NONCLUSTERED INDEX [IDX_asset_assetnum] ON dbo.asset(assetnum)

Upvotes: 1

Related Questions