Query Takes a Very Long Time to Execute

Query takes a very long time to execute

To make the query faster in your case there have three things you can do.
Don't know it will help you or not but According to SQL optimizing query concepts, it must help.

1. Create CLUSTERED INDEX for all your tables. Cluster index makes the SELECT query faster at 30:70 of ratio.
2. You should make column list instead of STAR statement (avoid SELECT *). Extra columns make BUFFER-POOL heavy.
3. You can use VIEW instead of the query. Because VIEW during JOIN statements is better than the normal query.

From above all of three, you must try first option (CLUSTER INDEX), which will really improve performance.
Hope this will be helpful.

Slow Query Execution: Takes long time to execute => Very Strange Why?

As pointed out by the posts, adding an index (on the date field) immediately fix the problem. I will monitor this but for now marking this post as "Answered".

Why SQL query takes too long time to execute?

As a tip towards the way of thinking how to solve this issue.

select t.id, t.title, l.*
from test t
cross apply (
  select 
    p.cnt [Level-Count], 
    ISNULL(p.[Level-1], '') [Level-1], 
    ISNULL(p.[Level-2], '') [Level-2],
    ISNULL(p.[Level-3], '') [Level-3],
    ISNULL(p.[Level-4], '') [Level-4]
  from (
    select 
      s.value, 
      'Level-' + cast(row_number() over (order by (select 1)) as varchar(10)) rn,
      count(*) over () - CASE WHEN t.parent_list = '' THEN 1 ELSE 0 END cnt
    from STRING_SPLIT(
      t.parent_list 
      + CASE WHEN t.parent_list = '' THEN '' ELSE '+' END 
      + cast(t.id as varchar(10))
      + CASE WHEN t.parent_list = '' THEN '+' ELSE '' END
      , '+'
    ) s
  ) s
  pivot ( 
    max(s.value) for s.rn in (
      [Level-1], 
      [Level-2], 
      [Level-3], 
      [Level-4]) 
  ) p
) l

Something like that could replace the whole script you've posted.
Some loop may still remain for the purpose of processing these data in portions. But the most part of code is unnecessary (temp tables and so on).

| id |        title | Level-Count | Level-1 | Level-2 | Level-3 | Level-4 |
|----|--------------|-------------|---------|---------|---------|---------|
|  1 |       item 1 |           1 |       1 |         |         |         |
|  2 |     item 1/2 |           2 |       1 |       2 |         |         |
|  3 |       item 3 |           1 |       3 |         |         |         |
|  4 |     item 3/4 |           2 |       3 |       4 |         |         |
|  5 |   item 3/4/5 |           3 |       3 |       4 |       5 |         |
|  6 | item 3/4/5/6 |           4 |       3 |       4 |       5 |       6 |
|  7 |     item 2/7 |           3 |       1 |       2 |       7 |         |
|  8 |       item 8 |           1 |       8 |         |         |         |

http://sqlfiddle.com/#!18/7aa71/31

SQL Server query taking long time to execute

I'm assuming m and k are views, and potentially rather complex ones and/or having GROUP BY statements.

I'm guessing that when you look at the execution plans and/or statistics, it will actually be running at least one of the views many times - e.g., one for each row in the other view.

Quick fix

If you are able to (e.g., it's in a stored procedure) I suggest the following process

• Creating temporary tables with the same structures as m and k (or with relevant columns for this purpose). Include columns for the modified versions of ckt (e.g., ints for both). Consider a PK (or at least clustered index) of sourcename and ckt (the int) for both temporary tables - it may help, or may not.
• Run m and k views independently, storing results in these temporary tables. Use filtering (e.g., WHERE clause on timestampserverlocal) and potentially the relevant GROUP BY clauses to reduce the number of rows created.
• Run the original SQL but using the temporary tables rather than the views, and without the need for the WHERE clause. It may still need the GROUP BY.

Longer fix

I suggest doing the quick fix first to confirm that the running-views-many-times is the problem.

If it does (and the issue is that the views are being run many times) one longer fix is to stop using the views in the FROMs, and instead putting it all together in one query and then trying to simplify the code.

SQL query takes too long to execute. Need to improve performance of query

First of all you have so many Includes() and ThenIncludes(), this if really bad for your performance. Is there a way by any chance you can reduce these includes -> only use the necessary one's.

Then i would that you execute the .ToList() query at the end of the statement (befor your return)

Down below is an example how i've done it in the past (with pagination & filtration):

            var context = _context.People
                .Include(x => x.Parents)
                .ThenInclude(x => x.Adress)
                .Include(x => x.Job)
                .Include(x => x.Hobbies);

            var items = string.IsNullOrWhiteSpace(query)
                ? context
                : context.Where(x => x.Name.Contains(query));

            var filters = new List<int>();
            if (filter != null)
            {
                filters = filter.Split(',').Select(int.Parse).ToList();
                items = items.Where(x => x.Parents.Select(s => s.AdressId).Any(z => filters.Any(y => y == z)));
            }

            return await items.Skip(page * take).Take(take).ToListAsync();

SQL Server: SQL taking very long time to execute

It seems from your query that the base table i.e. Raw_Data contains a lot of records. If that is true, then you could try the steps mentioned below to optimize your scenario.

1. you need to make sure that an index ontDate column is there for the table Raw_Data and furthermore this index has included columns of SiteID, tTime, tMI (sample query for this is mentioned at end of this answer)
2. create an index on SiteID column
3. Move the filter on tDate to inside the main query

Query with filters inside the main query

    select *
    from 
    (   select SiteID, tDate, tTime,
          (CASE WHEN IsNumeric(tMI)=1 then cast(tMI   as float) 
              ELSE null END) AS value
          from Raw_Data 
          where 
         (tDate>='20151201' and tDate<='20151205') 
      ) src
     pivot
     (
       max(value) for SiteID in ([CA0021], [CA0022], [CA0059])
      ) piv    
    order by tDate

Creating index with included columns

create NONCLUSTERED index index1 on Raw_Data
(
  tDate ASC
)
include ( SiteID, tTime, tMI )

ANOTHER APPROACH

Also, another approach you could take is to run your query in SQL Server Management Studio with the query option checked for Include Actual Execution Plan.

If the execution plan has a recommendation for creating an index ( this normally shows in green font), then you should create the recommended index and see if that helps your query speed. I have observed from my experience that execution plan recommendations always work and result in a great improvement.

Select query takes long time even for 10 records from a very big records table

Now that we have your real query we can see that it appears that you have no index on your column deviceDataId; meaning that the entire table needs to be scanned. As such, even though you only want 10 rows, all 6.5M rows need to be scanned and the value of deviceDataId checked.

If you create an index on your column deviceDataId and minimally INCLUDE the others in your query, then you'll have a covering index which'll greatly help. This also assumes id is the column your CLUSTERED INDEX is ordered on.

CREATE NONCLUSTERED INDEX IX_Table_DeviceTableID_Cols1_5
    ON dbo.[table] (deviceDataId)
    INCLUDE ([Column1], [Column2], [Column3], [Column4], [Column5]);

Also, as I note in the comments, if deviceDataId is an int, use an int value in your WHERE clause. Don't wrap numerical values in single quotes, that is for literal strings.

WHERE deviceDataId = 640

---

Related Topics