Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / PO Receipt 04.010 Slow...
Microsoft Dynamics SL (Archived)

PO Receipt 04.010 Slow loading PO with large number of lines

(0) ShareShare
ReportReport
Posted on by Steve Wier 944

When we load a large PO with Dftl PO Lines set to All, the screen will show "Unresponsive" and the user will wait 30+ seconds before the PO Lines are successfully loaded.  I ran a SQL Trace and found the "Fetch API_Cursor"  and "sp_cursorfetch" duration to be the only events with excessive durations(509) , reads(47620), and CPU(484).  I also noted that it appears to call these events for each line item on the PO. 

Since performance is very good in general, I am not concerned that this is related to our SQL Server performance.  I suspect the screen needs some performance tweeking. Any suggestions for improving the performance in this screen would be appreciated.

SL 2011 Version 8.00.20321.00

*This post is locked for comments

  • Rodney Hansen Profile Picture
    Rodney Hansen 26 on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    506ms with 47k reads seems to strongly suggest a missing index with your data. You will have to find the select statement prior to the fetch.  Here is a query you can run to catch the sql statement while the screen is loading.  This will show you the sql of the fetch while it is occurring,  You may have to press F5 to execute it many times while your screen is loading.  

    SELECT r.session_id,

          se.host_name,

          se.login_name,

          Db_name(r.database_id) AS dbname,

          r.status,

          r.command,

          r.cpu_time,

          r.total_elapsed_time,

          r.reads,

          r.logical_reads,

          r.writes,

          s.text                 sql_text,

          p.query_plan           query_plan,

          SQL_CURSORSQL.text,

          SQL_CURSORPLAN.query_plan

    FROM   sys.dm_exec_requests r

          INNER JOIN sys.dm_exec_sessions se

            ON r.session_id = se.session_id

          OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s

          OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p

          OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS

          OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL

                      LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS

                        ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle

          OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN

    -- -------------------------------------------------------------------------------------

    WHERE  r.session_id <> @@SPID

          AND se.is_user_process = 1

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    Curious if anyone tested this on their system and what type of performance they experienced.

    Steve

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    I just timed it out.  A 54 line item PO took 27 seconds to load.  The screen showed "Unresponsive after approx 10 seconds"  All the items on the PO tested are goods for sales order. 

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    Jayson,

    The more line items, the slower it gets.  50 line items takes 30+ seconds, 100 line items can take over a minute.  

    Steve

  • Jayson Montgomery Profile Picture
    Jayson Montgomery on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    Hi Steve,

    Thanks for posting to the OTC.  I understand you are seeing slowness when loading a large PO in the Receipt/Invoice Entry screen.  When you say that it is a large PO how many lines are being loaded when you notice the slowness?  I will wait to hear back from you.

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at
    Re: PO Receipt 04.010 Slow loading PO with large number of lines

    Steve

    Can I offer a thought.

    When data is being retrieved from the database, usually you get an SQL "SELECT" being executed.

    Then that is followed by a Fetch for each row that was returned by that SQL.

    It looks like you are looking at those Fetches.

    I think that if you look earlier in the trace, you should find the SQL that was executed.

    Once you have found that, you can try pasting it into SQL Server Manatgement Studio, and execute it.

    (Obviously have a look at it first to make sure it is "harmless". If the SQL you have found contains an "UPDATE" or "DELETE" then you won't want to run it !

    Does it run "slowly"?

    If so, that might suggest that it may be worth looking at adding an index .

    Just a thought or two...

    Barry

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,489 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans