DELETEALL-SQL Profiler How to optimize high number of Reads

nav_student · 2011-06-20

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?

kriki · 2011-06-20

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.

nav_student · 2011-06-20

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_student · 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_student · 2011-06-20

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.

DenSter · 2011-06-20

nav_student wrote:

  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_student · 2011-06-21

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?

kriki · 2011-06-22

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.

nav_student · 2011-06-22

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: )

kriki · 2011-06-23

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.

stryk · 2011-06-23

nav_student wrote:

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 ...

stryk · 2011-06-23

nav_student wrote:
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"?

nav_student · 2011-06-27

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_student · 2011-06-27

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 @Database  
WHILE @@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 @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

       IF (@@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

stryk · 2011-06-27

nav_student wrote:

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) ...

nav_student · 2011-06-27

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:

stryk · 2011-06-27

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 ...

nav_student · 2011-07-01

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?

davmac1 · 2011-07-01

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?

Howdy, Stranger!

Quick Links

Categories

DELETEALL-SQL Profiler How to optimize high number of Reads

Comments