SQL Low Performance than Native Database

kpsingh_mcakpsingh_mca Member Posts: 11
edited 2012-03-05 in SQL Performance
Scenario: SQL Database Vs Native Database
1. Currently I am using SQL database for one of my client and not able to run adjust cost on daily basis in 6 Hrs.

Task already done for SQL performance improvement
Quad Core Processor(64 Bit)
52 GB RAM
SQL Database is already divided into 10 Disk with RAID proper configurations.
Maintenance Plan run on regular basis including index maintenance

2. For month closing I need to take backup of system and restore in native database.
I don't don't want to do this activity again.

My requirement is make SQL performance up to Native database performance.

Friends pls suggest me the best feasible way out to improve SQL performance considering above scenarios.

Database figures:
GL - 10020561
ILE- 1631169
VLE- 6873680
3 Months Transactions
KRISHNA PAL SINGHMS-Dynamics -NAVISION

Comments

  • strykstryk Member Posts: 645
    I highly recommend to search MIBUSO (specifically) and the WWWeb (generally) about "NAV SQL Performance". This is actually a persistent topic which is permanently discussed - I'm sure you'll find gazillions of valuable advices!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • krikikriki Member, Moderator Posts: 9,115
    [Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    SQL Server wil probably never,ever outperform Native as it comes to posting documents.
  • krikikriki Member, Moderator Posts: 9,115
    SQL Server wil probably never,ever outperform Native as it comes to posting documents.
    But with everything optimized, SQL server can get very close.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • David_SingletonDavid_Singleton Member Posts: 5,479
    kriki wrote:
    SQL Server wil probably never,ever outperform Native as it comes to posting documents.
    But with everything optimized, SQL server can get very close.

    But that is not what Mark said. 8)
    David Singleton
  • krikikriki Member, Moderator Posts: 9,115
    kriki wrote:
    SQL Server wil probably never,ever outperform Native as it comes to posting documents.
    But with everything optimized, SQL server can get very close.

    But that is not what Mark said. 8)
    Mark said that native will always post documents faster than SQL. (at least that is what I understood). And I wrote that posting documents on SQL can get close to the speed of native if it is (heavily) optimized to post documents.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • davmac1davmac1 Member Posts: 1,283
    What version of Navision are you running?
    There are different optimizations for each version - generally better each new release
  • ResenderResender Member Posts: 119
    Being an SQL wizard myself I can probably give another avenue.
    Indexes, when migrating the native database to sql server every key in native is translated to an index in SQL server [-X, while this helps performance in the native to many indexes slow SQL Server's performance.

    It is like having a book with an index at the back, and then an index on each page for that individual page.

    Check what indexes and keys you really need in the SQL database.

    In a last statement relational databases like SQL Server can easilly outperform native, but they need to be configured correctly.
  • DenSterDenSter Member Posts: 8,307
    Resender wrote:
    SQL Server can easilly outperform native, but they need to be configured correctly.
    No, not true, misleading at best. There is no such thing as an "easy configuration" that will make NAV on SQL Server outperform the native DBMS. Index tuning is just part of a potential solution, and that requires a development effort, so you can't call that configuration.
  • ResenderResender Member Posts: 119
    I've seen an SQL database turn over data out of a database thats more complex and larger then the one in our navision faster.

    I find the native to be clumbersome and prone to more mistakes.
    The native might be more suited for Navision/Dynamics itself but if you want to export that data to 3th party tools or a website SQL beats native
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You're high jacking an old post. This should be discussed somewhere else.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Resender wrote:
    I've seen an SQL database turn over data out of a database thats more complex and larger then the one in our navision faster.

    I have seen Ferraris that go faster than Volkswagens, but that has as much relevance to this thread as your comment.
    David Singleton
Sign In or Register to comment.