Linked SQL View Performance

tiki
tiki Member Posts: 16
edited 2013-06-04 in NAV Three Tier
Hello,

We have a database that has a number of companies (30 - 40+), and there is a SQL view that unions all the companies together so that certain records can be displayed across companies. This view has 18,668 records and runs in under a second in SSMS. However, in NAV, the page for this is VERY slow and there is no extra logic to cause any slowdown (i.e. filters, etc.).

Has anyone seen slow performance in linked SQL views?

Comments

  • Johannes_Nielsen
    Johannes_Nielsen Member Posts: 206
    Use of SQL profiler might give you a precise answer to how the server is queried when you use SMSS and what NAV does (wrong) when it quiries the server.
    Best regards / Venlig hilsen
    Johannes Sebastian
    MB7-840,MB7-841
  • tiki
    tiki Member Posts: 16
    Thanks - that was my next step, so I'll look into it and post back if I find anything useful.
  • kriki
    kriki Member, Moderator Posts: 9,121
    I have seen this too in some case. I didn't find really a problem, but I think it is because the NAV client is slow (I don't know why) to receive the info.
    If I used the same view and queried it using ADO, it was a lot faster.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • tiki
    tiki Member Posts: 16
    Hi kriki, how would you translate that to the results on a page, etc.? Did you fill a recordset and then populate a temp table?
  • deV.ch
    deV.ch Member Posts: 543
    We had similar issues with a fairly complex View which was fast when executed through management studio, but very slow on nav side. I then recognized that when you use client monitor you see that the sql command used to gather the records was a select top 1, which caused the view code to execute every once the next record was retrieved. This was a very bad situation since the view was so complex ant the minimum execution time was nearly the same as the maximum time (so if you do top 1 or select * would end in the nearly the same time). Therefore we needed to change things a bit. What we did, was instead of using the view directly, we use a stored procedure to transfer the view results in a Nav Table. Then we just use this table for processing the results.
    My Blog
  • kriki
    kriki Member, Moderator Posts: 9,121
    With that particular case, I used ADO to get the fields I needed in put them in a temptable and showed that temptable.

    Another way is what deV.ch did: run a stored procedure that fills up a NAV table. Also that system I have used.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories