We're experiencing performance issues in Universal with a certain view. I already tried using ForceRecompile in extended properties to make it load faster but that doesn’t seem to have an effect. I rewrote the sp_prepexec statement to a regular select query and it's just as slow. This is the query that’s being run on SQL Server:
declare @p0 tinyint, @p1 nvarchar(6), @p2 int, @p3 int, @p4 int
select @p0=200, @p1=N'%A%', @p2=1, @p3=201, @p4=200000
SELECT t1.[tsf_row_number], t1.[bedrijf_nummer], t1.[klant_groep_nummer], t1.[klant_nummer]
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY t1.[klant_nummer] ASC, t1.[bedrijf_nummer] ASC, t1.[klant_groep_nummer] ASC) AS [tsf_row_number], t1.[bedrijf_nummer], t1.[klant_groep_nummer], t1.[klant_nummer]
FROM
(
SELECT t1.*
FROM [vw_example] t1
) t1
WHERE t1.[klant_groep_soort] = @p0 AND (isnull(t1.klant_geblokkeerd_voor,0) in (0,1)
)
AND (exists (select kg.klant_groep_nummer_hoofd
from klant_groep kg
where kg.bedrijf_nummer = t1.bedrijf_nummer
and kg.klant_groep_nummer_hoofd= t1.klant_groep_nummer)
)
AND t1.[naam] LIKE @p1
) t1
WHERE t1.[bedrijf_nummer] = @p2 AND t1.[klant_groep_nummer] = @p3 AND t1.[klant_nummer] = @p4
ORDER BY t1.[tsf_row_number] ASC
OFFSET 0 ROWS
OPTION (RECOMPILE)As you can see it contains OPTION (RECOMPILE) at the very end but it doesn't seem to have any effect. This query takes 90 seconds to produce the output and it's just a single record.
The standard count on the list takes 55 seconds to complete despite it also containing OPTION (RECOMPILE). These long query times render this particular screen in Universal rather impossible to work with.
I understand it can be difficult to evaluate this from a query alone. I'm just looking for clues as to how to improve performance.
