SQL Low Performance than Native Database

kpsingh_mca

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

stryk

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!

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]

Marije_Brummel

SQL Server wil probably never,ever outperform Native as it comes to posting documents.

kriki

Mark Brummel 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.

David_Singleton

kriki wrote:

Mark Brummel 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)

kriki

David Singleton wrote:

kriki wrote:

Mark Brummel 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.

davmac1

What version of Navision are you running?
There are different optimizations for each version - generally better each new release

Resender

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.

DenSter

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.

Resender

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_Brummel

You're high jacking an old post. This should be discussed somewhere else.

David_Singleton

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.