Post reply

where does that estimated number come from?

  • December 20, 2011 at 8:59 pm

    #262138

    Dear all,

    I'm currently troubleshooting performance problem of our ERP software and using SQL trace to identify queries that might cause it.

    I tried to run one of the queries using Management Studio and got this result:

    My question is, where did SQL get that estimated # of rows from?

    The index statistics is up-to-date and looks like this:

    The server is MS SQL Server 2000 SP3a and the table in question has 1,103,030 records.

    Any thoughts will be appreciated.

    Regards,

    David

  • December 20, 2011 at 9:23 pm

    #1423991

    What version of MS Dynamics Nav is this?

    It looks like you have a cross join in that query (FROM tbl1, tbl2 and then no where condition to make a join)

    I can't make any comments on the stats other than you clearly just updated them with fullscan. So those are as good as they can be.

    Any way you can post the full plan? I don't know if you can get the 2005 version of the plan (using ssms). Remote debugging this on 2000 is not the easiest thing.

  • December 20, 2011 at 10:45 pm

    #1423999

    The OPTION(FAST 5) is probably responsible for the estimated rowcount of 5.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 20, 2011 at 11:16 pm

    #1424019

    Hi Ninja,

    It's Navision version 4 without SP.

    I can assure you it's from single table.

    I tried to conceal our company name but missed a part of it (", PT") in the process 🙂

    I'm afraid I don't understand what you meant by "full plan".

    Do you mean the screenshot without me hovering the mouse over?

    Thanks

  • December 20, 2011 at 11:31 pm

    #1424027

    Hi Jeff..

    You're absolutely right! It was the "FAST 5"...I can't believe I've missed something that obvious 😀

    Thanks a lot

  • December 20, 2011 at 11:49 pm

    #1424036

    I am not able to find any 'FAST 5' or '(", PT")' anywhere in the thread. What am I missing?

  • December 21, 2011 at 12:35 am

    #1424050

    Hi Dev,

    If you can see the first screenshot on my opening post, look at the SQL query and there you'll find "FAST 5" and ", PT" 😛

  • December 21, 2011 at 12:41 am

    #1424052

    assistan11 (12/21/2011)

    Hi Dev,

    If you can see the first screenshot on my opening post, look at the SQL query and there you'll find "FAST 5" and ", PT" 😛

    I was assuming you attached something but I can’t see any attachment... :unsure:

  • December 21, 2011 at 5:55 am

    #1424166

    assistan11 (12/20/2011)

    Hi Ninja,

    It's Navision version 4 without SP.

    I can assure you it's from single table.

    I tried to conceal our company name but missed a part of it (", PT") in the process 🙂

    I'm afraid I don't understand what you meant by "full plan".

    Do you mean the screenshot without me hovering the mouse over?

    Thanks

    I think there's no need for it now.

    In sql 2005+, you can save the graphical plan as a .sqlplan (which is xml). Which you can then upload so that we can open it on our computer with SSMS.

    Unfortunately afaik there's no way to change that behavior in Nav so I don't have any alternatives to offer to you.

  • December 21, 2011 at 6:18 pm

    #1424539

    @ninja Thanks for the tip on saving graphical plan

    @dev Try open http://img100.imageshack.us/img100/9936/42805049.jpg

  • December 21, 2011 at 8:28 pm

    #1424547

    assistan11 (12/20/2011)

    Hi Jeff..

    You're absolutely right! It was the "FAST 5"...I can't believe I've missed something that obvious 😀

    Thanks a lot

    You should see what I miss before I've had my 2nd cup o' Joe. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2011 at 1:18 am

    #1424613

    assistan11 (12/21/2011)

    @dev Try open http://img100.imageshack.us/img100/9936/42805049.jpg

    Thanks for sharing it but I can't access it. Never Mind...

    You got what you were expecting from SSC. Enjoy 😉

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply