CODEWITHSUNDEEP
sqlsql-serverasp.net-mvcdatabase-connectionazure-sql-database
Neeraj Mehta
Neeraj Mehta

Reputation: 1705

How to optimise SQL query consuming high CPU resources

I am using LINQ and MVC6 platform and hosted on the Microsoft Azure cloud. The following query is consuming high CPU resources. Here is the query in SQL format

(@p__linq__0 nvarchar(4000))SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[DevNews] AS [Extent1]
    WHERE (1 = [Extent1].[AdminCheck]) AND ([Extent1].[Tags] LIKE @p__linq__0 ESCAPE N'~') AND ([Extent1].[NewsLabels] IS NULL) AND ([Extent1].[Source] IN (N'PTI',N'IANS',N'Reuters'))
)  AS [GroupBy1]

Upvotes: 0

Views: 205

Answers (3)

El.Hum
El.Hum

Reputation: 1485

If there are Indexes suggested in query execution plan, create them for helping sql to scan large tables faster and better.
If no, then use a resource governor using SSRS to limit the resource for your login then run your query (this will reduce execution performance)

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You query is essentially:

SELECT COUNT(1) AS [C1]
FROM [dbo].[DevNews] AS [Extent1]
WHERE 1 = [Extent1].[AdminCheck] AND 
     [Extent1].[Tags] LIKE @p__linq__0 ESCAPE N'~' AND
     [Extent1].[NewsLabels] IS NULL AND
     [Extent1].[Source] IN (N'PTI', N'IANS', N'Reuters')

Removing the subquery should have no impact on performance in SQL Server. However, an index will. I would recommend a covering index with the following columns: (AdminCheck, NewsLabels, Source, Tags).

You need to be sure that Source is stored as NCHAR or NVARChAR. Type conversion can slow the query.

Finally, a column called Tags is suspicious. If this is a list of tags stored as a string, then you should revisit your data structure. That is not the best way to store lists in SQL.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

just a suggestion .. looking to your sample you could avoid the subquery and unuseful () around each where condition 

 SELECT COUNT(1) AS [C1]
    FROM [dbo].[DevNews] AS [Extent1]
    WHERE 1 = [Extent1].[AdminCheck] 
    AND [Extent1].[Tags] LIKE @p__linq__0 ESCAPE N'~' 
    AND [Extent1].[NewsLabels] IS NULL
    AND [Extent1].[Source] IN (N'PTI',N'IANS',N'Reuters')

this should avoid the build of a temp table related to the subquery

Upvotes: 1

Related Questions