Report fast on local, slow on SQL server

zappy
zappy Member Posts: 15
edited 2008-02-13 in NAV/Navision Classic Client
I hav every simple report but with meny records, something like
Entry (1000000)
__Documents (5)
____Entry (1000000)
______Entry (100)
________if ....
__________INSERT something

In () are number of record ... cca.
On the local PC is very fast, about 2..3 minutes. On the MS SQL server it takes hours ...
All kyes has SWIFT right, all objects are rebuild on the SQL server. I have tried tu run in exactly on the same server, where database is .. the same result = slow like turtle. ](*,)

Any idea?
Tomas Zadrapa

Comments

  • Tomas
    Tomas Member Posts: 420
    zappy wrote:
    I hav every simple report but with meny records, something like
    Entry (1000000)
    __Documents (5)
    ____Entry (1000000)
    ______Entry (100)
    ________if ....
    __________INSERT something

    In () are number of record ... cca.
    On the local PC is very fast, about 2..3 minutes. On the MS SQL server it takes hours ...
    All kyes has SWIFT right, all objects are rebuild on the SQL server. I have tried tu run in exactly on the same server, where database is .. the same result = slow like turtle. ](*,)

    Any idea?

    What are you doing with this report? Printing information? Calculating something? Do you really need to run through same table (Entry) twice or more?
    Tomas,
    Dynamics NAV Enthusiast
  • kine
    kine Member Posts: 12,562
    1) Local (native) DB is working in another way than SQL and it means that the problem is in the way, you are working with the data.
    2) You can use client monitor or SQL profiles to see what took so long and you can optimize the indexes
    3) It seems that the process is scanning the data instead seeking wih help of some good index. Do not forget - you need good selectivity key to get as few records as possible by first field in the index
    4) If the problem is the insert (tooks too long or too many write opperation per one insert) you need to optimize the table by disabling unnecessarily indexes and SIFTs.
    5) It means, you need to optimize the tables for SQL.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • girish.joshi
    girish.joshi Member Posts: 407
    In almost every case I've seen of this, the problem is usually because SQL server is not configured properly.

    is the transaction log hitting a seperate drive?
    are you using the right disk configuration (raid 10)?

    Does the box you're running SQL server on have enough RAM? Is SQL server using all fo the available RAM?

    and then there are all of things kine mentioned...

Categories