mibuso.com

Microsoft Business Solutions online community
It is currently Thu Oct 02, 2014 12:19 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 19 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: DELETEALL-SQL Profiler How to optimize high number of Reads
PostPosted: Mon Jun 20, 2011 5:07 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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?


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 20, 2011 5:50 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7698
Location: Milan
Country: Italy (it)
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

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 20, 2011 6:37 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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.


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 20, 2011 6:37 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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.


Last edited by nav_student on Mon Jun 20, 2011 6:45 pm, edited 2 times in total.

Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 20, 2011 6:44 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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.


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 20, 2011 9:20 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7990
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Tue Jun 21, 2011 1:33 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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?


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Wed Jun 22, 2011 9:16 am 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7698
Location: Milan
Country: Italy (it)
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

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Wed Jun 22, 2011 4:13 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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: )


Attachments:
sql_trace.jpg
sql_trace.jpg [ 166.53 KiB | Viewed 3908 times ]
Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Thu Jun 23, 2011 1:50 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7698
Location: Milan
Country: Italy (it)
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

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Thu Jun 23, 2011 5:43 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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 ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Thu Jun 23, 2011 5:47 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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"?

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 27, 2011 10:42 am 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 150
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.


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 27, 2011 11:35 am 
Offline

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


Top
 Profile E-mail  
 
 Post subject: Re: DELETEALL-SQL Profiler How to optimize high number of Re
PostPosted: Mon Jun 27, 2011 12:16 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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) ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 19 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: