Linked SQL View Performance

tiki

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?

Johannes_Nielsen

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.

tiki

Thanks - that was my next step, so I'll look into it and post back if I find anything useful.

kriki

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.

tiki

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

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.

kriki

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.