-1

I have created a stored procedure to get data. In this stored procedure, I have returned above 5+ table and table store the data above 20k+. So right now I have run the stored procedure that time I get the data in above 1 minute time take. I want just with in 1 second get data. I have set also SET NOCOUNT ON; and also create missing index. Still I am getting same time for the get data.

This is my query => 

SET NOCOUNT ON;

DECLARE @CurMon int
DECLARE @year nvarchar(max)
SELECT  @CurMon = month(getdate())
SELECT  @year = year(getdate())

SELECT 
    FORMAT(dateadd(MM, T.i, getdate()), 'MMM-yy') AS DateColumn, 
    CASE 
       WHEN uf.TotalCount IS NULL
          THEN 0  
          ELSE uf.TotalCount 
    END AS TotalCount
FROM 
    (VALUES (12-@CurMon),(11-@CurMon),(10-@CurMon),(9-@CurMon),(8-@CurMon),(7-@CurMon),(6-@CurMon), (5-@CurMon), (4-@CurMon), (3-@CurMon), (2-@CurMon), (1-@CurMon)) AS T(i)
OUTER APPLY
    (SELECT DISTINCT 
         COUNT(datepart(MM, InsertDateTime)) OVER (PARTITION BY datepart(MM, InsertDateTime)) AS TotalCount
     FROM 
         User
     WHERE 
         DATEDIFF(mm, UF.InsertDateTime, DATEADD(mm, T.i, GETDATE())) = 0 
         AND IsLogin = 1) uf
ORDER BY
    DATEPART(MM, CONVERT(DATETIME, FORMAT(dateadd(MM, T.i, GETDATE()), 'MMMM') + '01 ' + @year, 110))

I have like this query like this here please let me how can improve the this sp.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Edit
  • 385
  • 4
  • 24
  • 1
    This is hard to answer without some idea about table structure, existing indexes, explain plans and maybe some example data. – GolezTrol Aug 28 '17 at 10:56
  • Use the query analyser to check what is going on. A execution plan would also help on your problem. Upload one, please – coding Bott Aug 28 '17 at 11:03
  • @BerndOtt but i have create all the missing index after look execution plan. but still i am getting same issue – Edit Aug 28 '17 at 11:06
  • questions seeking performance help should include DDL,DML Of the tables involved along with test data..if your test data is large,try scripting out schema and stats for the table(`right click database->generate scripts->select specific database objects->in next screen select advanced and choose Script statistics)` and paste it in question..With this info any one repro the same issue you are facing.Otherwise it becomes very difficult to answer your question .Pasting server version also helps – TheGameiswar Aug 28 '17 at 11:07

1 Answers1

0

APPLY operator invokes a table valued function for each row returned by outer table expression of a query.

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

https://www.sqlshack.com/sql-server-query-execution-plans-understanding-reading-plans/

  1. Did you try removing ORDER BY from your queries and then check query performance. Why are you formatting so much in ORDER BY if all you need is month number. Can DATEPART after DATEADD not give you that? Using CONVERT and FORMAT over a large collection can decrease performance.

  2. Change DATEDIFF in WHERE clause to MONTH, and instead of DATEADD, get current month in an integer on top and add to T.i

  3. If you want to continue using OUTER APPLY, you can also get data for query inside outer apply in a table variable, create index over table variable and use OUTER APPLY over that, and then apply date comparison.

  4. Can you take things inside VALUES clause into another table variable since you are repeating it for multiple queries.

  5. Again in your select string, you are formatting too much. You created your own values T (i). You could have just put a table variable with 2 columns

9 sep-17

10 oct-17

11 nov-17

Then, you would not have required to format so much.

  1. Instead of CASE statement in SELECT, you can use ISNULL function.

  2. Obviously, then come indexes, actual query execution plan, locks and wait stats. Your table schema is not known.

  3. You may have to re-write queries to use something else instead of OUTER APPLY, may be a CROSS APPLY or a LEFT JOIN, if that is a possibility. They can be faster than OUTER APPLY.

https://dba.stackexchange.com/questions/75048/outer-apply-vs-left-join-performance

SQL View Outer Apply Speed

CROSS APPLY vs OUTER APPLY speed difference

  1. Also, check for any space issues on PC where SQL server is installed. Check for log file clean up. These are basically activities that can be conducted by DBA at your location.
Amit Kumar Singh
  • 4,393
  • 2
  • 9
  • 22
  • can you please give me some hint as your give points on with my query?? – Edit Aug 29 '17 at 06:32
  • i can also remove order by and also set isnull to replace case but still my query take time more. can't any changes see – Edit Aug 29 '17 at 06:33
  • Try point no. 2 & 8. Date comparisons in WHERE clause can take much time. Also, beyond this, you can look for index fragmentation, wait stats for individual queries, and actual query execution plan for all queries individually. Points in the query execution plan where percentages are high may need to re-written. Also, check for any space issues on PC where SQL server is installed. Check for log file clean up. These are basically activities that can be conducted by DBA at your location. – Amit Kumar Singh Aug 29 '17 at 14:44