DELETEALL-SQL Profiler How to optimize high number of Reads

nav_student · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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

Code: Select all

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

Code: Select all

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

Code: Select all

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 · **Posted:** Mon Jun 20, 2011 5:50 pm

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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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

Code: Select all

  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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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

Code: Select all

  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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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

Code: Select all

  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 · **Posted:** Mon Jun 20, 2011 9:20 pm

nav_student wrote:

Code: Select all

  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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

Denster,

the error was mine.

When i put the code i forgot to take out the comments

Code: Select all

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 · **Posted:** Wed Jun 22, 2011 9:16 am

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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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 · **Posted:** Thu Jun 23, 2011 1:50 pm

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 · **Posted:** Thu Jun 23, 2011 5:43 pm

nav_student wrote:

Code: Select all

IF NOT ProdOrderCapNeed.ISEMPTY THEN
 ProdOrderCapNeed.DELETEALL(TRUE);
 ProdOrderCapNeed.DELETEALL;
 TransferRouting;

Well, this is actually a mistake ... this should be:

Code: Select all

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 · **Posted:** Thu Jun 23, 2011 5:47 pm

nav_student wrote:

Denster,

the error was mine.

When i put the code i forgot to take out the comments

Code: Select all

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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

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 · **Joined:** Mon Aug 10, 2009 12:24 pm **Posts:** 98

I contacted Microsoft and they say that the problem could be on my SQL Instance.

Microsoft Reccomends ( re-index the database):

Code: Select all

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

Code: Select all

EXEC sp_updatestats

stryk · **Posted:** Mon Jun 27, 2011 12:16 pm

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

Quote:

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

mibuso.com

DELETEALL-SQL Profiler How to optimize high number of Reads

Who is online