SQL Low Performance than Native Database

kpsingh_mcakpsingh_mca Posts: 11Member
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 Posts: 645Member
    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 Posts: 7,865Member, Moderator
    [Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]
    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_BrummelMark_Brummel Posts: 4,243Member, Moderators Design Patterns
    SQL Server wil probably never,ever outperform Native as it comes to posting documents.
  • krikikriki Posts: 7,865Member, Moderator
    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
    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

  • David_SingletonDavid_Singleton Posts: 5,364Member
    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
    Dynamics NAV Freelancer
    Dynamics Book
    Go-Live International
  • krikikriki Posts: 7,865Member, Moderator
    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
    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

  • davmac1davmac1 Posts: 1,173Member
    What version of Navision are you running?
    There are different optimizations for each version - generally better each new release
  • ResenderResender Posts: 119Member
    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 Posts: 8,043Member
    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 Posts: 119Member
    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
  • Mark_BrummelMark_Brummel Posts: 4,243Member, Moderators Design Patterns
    You're high jacking an old post. This should be discussed somewhere else.
  • David_SingletonDavid_Singleton Posts: 5,364Member
    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
    Dynamics NAV Freelancer
    Dynamics Book
    Go-Live International
Sign In or Register to comment.