DELETEALL-SQL Profiler How to optimize high number of Reads

nav_studentnav_student Posts: 224Member
edited 2011-07-01 in SQL Performance
Hi guys,

i have turn SQL Server Profiler to view a why Planning Worsheet was taking about 2 minutes to make calculations to a single item, on a CRONUS database.


I had detect some lines with this parameters
SQL Profiler
CPU - 187
Reads - 66820
Duration - 186

The SQL Statement
DELETE FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need"  WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3))

I turn the Client Monitor and see that C/AL CODE
ProdOrderCapNeed.DELETEALL;
  TransferRouting;

I changed like the Styrk´s sugestion http://dynamicsuser.net/blogs/stryk/archive/2010/10/22/solving-blocking-issues-practical-examples.aspx
IF NOT ProdOrderCapNeed.ISEMPTY THEN
 ProdOrderCapNeed.DELETEALL(TRUE);
 ProdOrderCapNeed.DELETEALL;
 TransferRouting;

but without luck.

I need some help to optimize this.

Can you help me?

Comments

  • krikikriki Posts: 7,865Member, Moderator
    1) check if you have an index starting with those 3 fields.
    2) Did those SQL statements effectivily delete some records?

    BTW: if you only have some of them, it doesn't look as a problem. After all, they are all less then half a second.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • nav_studentnav_student Posts: 224Member
    Kriki,

    thanks for your answers.

    I create a key with the
    Worksheet Template Name,Worksheet Batch Name,Worksheet Line No.

    like i saw on the codeunit code
      ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
     // ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    

    2. I guess SQL is not deleting them.

    So i have to conclude that running a planning worsheet for a single item is taking 2 minutes. For thousands of item´s it´s gonna be a huge time consuming task.
  • nav_studentnav_student Posts: 224Member
    edited 2011-06-20
    Kriki,

    thanks for your answers.

    1. I create a key with the
    Worksheet Template Name,Worksheet Batch Name,Worksheet Line No. flelds

    like i saw on the codeunit code
      ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
     // ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    

    2. I guess SQL is not deleting them.

    So i have to conclude that running a planning worsheet for a single item is taking 2 minutes. For thousands of item´s it´s gonna be a huge time consuming task.
  • nav_studentnav_student Posts: 224Member
    Kriki,

    thanks for your answers.

    I create a key with the
    Worksheet Template Name,Worksheet Batch Name,Worksheet Line No.

    like i saw on the codeunit code
      ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
     // ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    

    2. I guess SQL is not deleting them.

    So i have to conclude that running a planning worsheet for a single item is taking 2 minutes. For thousands of item´s it´s gonna be a huge time consuming task.
  • DenSterDenSter Posts: 8,043Member
      ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
     // ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    

    2. I guess SQL is not deleting them.
    That's because you commented out the line of code that does the actual deleting. All you are doing there is set filters and a sort order.
  • nav_studentnav_student Posts: 224Member
    Denster,

    the error was mine.

    When i put the code i forgot to take out the comments
    ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
      ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    


    The question is that
    Planning Worksheet has taking about 15x more time to make calculations when the (field) Send Ahead Quantity is greater than zero in Item Routing Operations. How can i diagnose the problem?
  • krikikriki Posts: 7,865Member, Moderator
    I think it is because of the field "Send Ahead Quantity" <> 0. NAV probably has to do more work to calculate also this quantity.

    If you want to cheCK IN SQL, I use SQL Profiler to trace what NAV is doing.
    And if I want to make some totals of what's happening, I throw the result in Qure Workload Analyzer.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • nav_studentnav_student Posts: 224Member
    Thanks kriki.

    Great tool.

    On attach i send this picture.

    I follow the Styrk´s book and i see this parameters:

    High Number of Reads >=1000
    High Number of Writes>=50
    Duration >=50

    On this file i exceed this numbers. So what´s the next step? Reproduce the Query´s on Management Studio?


    ( it´s my 1st time doing this :oops: )
    sql_trace.jpg
    167K
    sql_trace.jpg 166.5K
  • krikikriki Posts: 7,865Member, Moderator
    The parameters are for each statement in itself and not for totals. The tool summarizes the data.
    The line you showed has hight read and write but it is very low on duration, so at first sight it is not a problem.
    And the sp_execute is difficult to find out what it does because it doesn't have the SQL statement.

    You need to create 2 traces : 1 with "send Ahead Quantity" <> 0 and one with "send Ahead Quantity" = 0. Then analyse them with Qure Workload Analyzer to see where the differences are. Then we have an idea where to start looking.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • strykstryk Posts: 645Member
    IF NOT ProdOrderCapNeed.ISEMPTY THEN
     ProdOrderCapNeed.DELETEALL(TRUE);
     ProdOrderCapNeed.DELETEALL;
     TransferRouting;
    
    Well, this is actually a mistake ... this should be:
    IF NOT ProdOrderCapNeed.ISEMPTY THEN
       ProdOrderCapNeed.DELETEALL;
     TransferRouting;
    
    The idea is to check If there are any records within the filter before firing the DELETEALL to avoid blocking conflicts. This has minor impact on speed ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • strykstryk Posts: 645Member
    Denster,

    the error was mine.

    When i put the code i forgot to take out the comments
    ProdOrderCapNeed.SETCURRENTKEY(
        "Worksheet Template Name","Worksheet Batch Name","Worksheet Line No.");
      ProdOrderCapNeed.SETRANGE("Worksheet Template Name",ReqLine."Worksheet Template Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Batch Name",ReqLine."Journal Batch Name");
      ProdOrderCapNeed.SETRANGE("Worksheet Line No.",ReqLine."Line No.");
      ProdOrderCapNeed.DELETEALL;
      TransferRouting;
    END;
    


    The question is that
    Planning Worksheet has taking about 15x more time to make calculations when the (field) Send Ahead Quantity is greater than zero in Item Routing Operations. How can i diagnose the problem?
    Uhm ... sorry .. here I got lost ... what has the DELETEALL issue to do with the "Send Ahead Quantity"?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • nav_studentnav_student Posts: 224Member
    Styrk,

    we are trying to calculate the Planning Worksheet.
    We notice when the "Send Ahead Quantity" is greater than zero then the calculate takes a long time.
    I run with SQL Server Profiler and detect that
    DELETE FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need" WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3)) as a duration bigger than the other statements.

    I run the client Monitor and see the statement DELETEALL on CodeUnit 99000809.
  • nav_studentnav_student Posts: 224Member
    I contacted Microsoft and they say that the problem could be on my SQL Instance.

    Microsoft Reccomends ( re-index the database):
    DECLARE @Database VARCHAR(255)   
    DECLARE @Table VARCHAR(255)  
    DECLARE @cmd NVARCHAR(500)  
    DECLARE @fillfactor INT 
    
    SET @fillfactor = 70 
    
    DECLARE DatabaseCursor CURSOR FOR  
    SELECT name FROM MASTER.dbo.sysdatabases   
    WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   
    ORDER BY 1  
    
    OPEN DatabaseCursor  
    
    FETCH NEXT FROM DatabaseCursor INTO @FETCH_STATUS = 0  
    BEGIN  
    
       SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
      table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES 
      WHERE table_type = ''BASE TABLE'''   
    
       -- create table cursor  
       EXEC (@cmd)  
       OPEN TableCursor   
    
       FETCH NEXT FROM TableCursor INTO @MICROSOFTVERSION / POWER(2, 24) >= 9)
           BEGIN
               -- SQL 2005 or higher command 
               SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
               EXEC (@cmd) 
           END
           ELSE
           BEGIN
              -- SQL 2000 command 
              DBCC DBREINDEX(@Table,' ',@fillfactor)  
           END
    
           FETCH NEXT FROM TableCursor INTO @Table   
       END   
    
       CLOSE TableCursor   
       DEALLOCATE TableCursor  
    
       FETCH NEXT FROM DatabaseCursor INTO @Database  
    END  
    CLOSE DatabaseCursor   
    DEALLOCATE DatabaseCursor
    

    and then update the Statistics
    EXEC sp_updatestats
    
  • strykstryk Posts: 645Member
    Styrk,

    we are trying to calculate the Planning Worksheet.
    We notice when the "Send Ahead Quantity" is greater than zero then the calculate takes a long time.
    I run with SQL Server Profiler and detect that
    DELETE FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need" WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3)) as a duration bigger than the other statements.

    I run the client Monitor and see the statement DELETEALL on CodeUnit 99000809.

    Ok, but that would mean that the DELETEALL takes longer because there are more lines to delete (thus created before) if "Send Ahead Quantity" is greater than 0. This would be a logical problem.

    Technically, there's no relation - the filter of the DELETEALL statement does not refer to "Send Ahead Quantity".

    So could you determine how many lines are deleted then? Maybe it's plausible to have such a number of "Reads" and "Duration" ...
    CPU - 187
    Reads - 66820
    Duration - 186

    Have in mind that a DELETE also implicitly modifies all kinds of related statistics and indexes, but also the VSIFT (if existing).

    Sufficient DB Maintenance like Index Defragmentation and Statistic Updates should be obligatory anyway, but I'm not sure if that will solve the problem ...
    Actually I doubt that this DELETE is the core of the problem: you say the whole process takes about 2 minutes, the DELETE only takes 186 milliseconds ... (actually Kriki already highlighted that) ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • nav_studentnav_student Posts: 224Member
    Styrk,
    i had run again the SQL Profiler and open the Qure Workload Analyzer and sort by duration.
    The Query that takes a long time is consuming 3 seconds. ( Prod_ Order Capacity Need).
    This is critical?
    declare @p1 int
    set @p1=1074257957
    declare @p5 int
    set @p5=12290
    declare @p6 int
    set @p6=8194
    exec sp_cursorprepare @p1 output,N'@P1 varchar(10),@P2 varchar(10),@P3 int,@P4 varchar(10),@P5 varchar(10),@P6 varchar(10),@P7 int,@P8 varchar(10),@P9 int,@P10 varchar(20),@P11 tinyint,@P12 varchar(20),@P13 int,@P14 int',N'SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need" WITH (UPDLOCK) WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3)) AND (("Operation No_"=@P4)) AND "Worksheet Template Name"=@P5 AND "Worksheet Batch Name"=@P6 AND "Worksheet Line No_"=@P7 AND "Operation No_"=@P8 AND "Status"=@P9 AND "Prod_ Order No_"=@P10 AND "Requested Only"=@P11 AND "Routing No_"=@P12 AND "Routing Reference No_"=@P13 AND "Line No_"<@P14 ORDER BY "Worksheet Template Name" DESC,"Worksheet Batch Name" DESC,"Worksheet Line No_" DESC,"Operation No_" DESC,"Status" DESC,"Prod_ Order No_" DESC,"Requested Only" DESC,"Routing No_" DESC,"Routing Reference No_" DESC,"Line No_" DESC ',1,@p5 output,@p6 output
    select @p1, @p5, @p6

    Now the profile does not indicate the DELETE :oops:
    sql_trace_duration.jpg
    164K
  • strykstryk Posts: 645Member
    If that's a problem or not is ahrd to tell - 3 seconds out of 2 minutes is not that much, depending how often that query was fired ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • nav_studentnav_student Posts: 224Member
    Hi guys,

    i run the calculation for all items on the range and are queries that takes almost 2 minutes to complete, with a great number of read and writes.

    Query that takes 2 minutes:

    SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS$Prod_ Order Capacity Need" WITH (UPDLOCK) WHERE (("Worksheet Template Name"=@P1)) AND (("Worksheet Batch Name"=@P2)) AND (("Worksheet Line No_"=@P3)) AND (("Operation No_"=@P4)) AND "Worksheet Template Name"=@P5 AND "Worksheet Batch Name"=@P6 AND "Worksheet Line No_"=@P7 AND "Operation No_"=@P8 AND "Status"=@P9 AND "Prod_ Order No_"<@P10 ORDER BY "Worksheet Template Name" DESC,"Worksheet Batch Name" DESC,"Worksheet Line No_" DESC,"Operation No_" DESC,"Status" DESC,"Prod_ Order No_" DESC,"Requested Only" DESC,"Routing No_" DESC,"Routing Reference No_" DESC,"Line No_" DESC

    Is this significative?
    query.jpg
    186K
    query.jpg 186.3K
  • davmac1davmac1 Posts: 1,172Member
    You are using this key:
    Worksheet Template Name,Worksheet Batch Name,Worksheet Line No.,Operation No.

    The query has consecutive parameters for each field, then repeats them. Any idea why?

    Have you tried running the query manually outside of Navision?
Sign In or Register to comment.