How does Navision's restore work internally on SQL?

RobertMoRobertMo Member Posts: 484
edited 2007-10-17 in SQL General
We all know how Navision’s restore works from its dialog window. That means restoring TableData of each selected company, common data and objects (last 2 if selected and if DB is new) and at the end secondary keys (and sum-index tables).

But I want to know more in detail? Especially I am interested in estimating the timing?

What I have noticed is that the speed (reported as KB copied) is changing per tabledata. I noticed that the table 355 is very slow in KB, while table 17 is much faster.

I checked the list of table sizes and number of records and came to following assumptions:
Navision restore takes a batch of records (eg. 1000 or 2000 or 10000?) and inserts them into DB. And the average time to insert this batch is more or less constant (but of course HW dependant).
Therefore inserting 10000 records of table 355 takes similar time span as 10000 records of table 17, but since the size of the record is very different, thus also more KB are reported during restore of T17 then during T355.

After setting asumption I have checked other tables and it somehow confirms the idea.
Of course the speed is not linear to the records size reported in "Table information", becuase the record size is highly dependant also on the secondary keys, which means you can not simply do the math based on what the “Table information” reports.

But this is only my assumption? Can someone confirm or explain how Navision’s restore actually insert data to DB?
           ®obi           
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Comments

  • ara3nara3n Member Posts: 9,253
    the whole restore and backup is closed and you can't tell from front end.

    All I have found out is that it first insert the data into 20 Million range first. So it creates new tables on sql. Once all the data is transfered from fbk to into these 20 mil tables, then it transfers them into actual tables. Since it's a new company, it creates the tables again for the actual company and then transfers them. Afterwards it deletes the 20 mil records and deletes the table objects in 20 mil
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • kinekine Member Posts: 12,562
    You can use the SQL Profiler to see, what it is doing in background... :-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • ara3nara3n Member Posts: 9,253
    right from backend you can run profiler. I just haven't found the time to look at a backup routine. I think I can find more interesting things to do than analyze that. Unless of course a customer wants to pay for it, which I doubt.

    :mrgreen:
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • RobertMoRobertMo Member Posts: 484
    Yes I was thinking about SQL profiler, just didn't (and still don't) have the time to do it.
    I thought someone else already got curious once and did it and could share some ideas.

    Well anybody?
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • kinekine Member Posts: 12,562
    No, no time for that... :-) and it seems like others have no time too... 8)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • krikikriki Member, Moderator Posts: 9,077
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.