I have the following scenario: a table with up to 20000 entries and another table with corresponding custom fields. I need to implement a query with a filter opportunity over all colums (including the custom fields) AND skip and take AND I need the total row count after filtering. With the help of dynamic sql I managed to implement a query which adds the custom fields as columns to the first table. But I am really having troubles implementing a skip and take functionality which runs really fast AND also returns the total row count. As not all of our customers are running on SQL Server 2012, the best way would be to implement the take and skip via a where clause over the row_number, but i think this also is the slowest alternative. I like the OFFSET and FETCH functionality for the 2012 case, but still getting the row count seems to slow down a lot.
this is my query resulting from the dynamic sql, below the whole dynamic query, both with the two alternatives as comments
With tempTable AS
(
SELECT *
--1. ALTERNATIVE:
,ROW_NUMBER() Over (ORDER BY Nachname, Vorname desc) As ROW_NUMBER ,COUNT(1) OVER () as Total_Rows
FROM
(
SELECT [a].*, [DFSF].[Datenfeld_Name],[Datenfeld_Inhalt]
FROM
[dbo].[Ansprechpartner] AS a left join [dbo].[Datenfeld] AS dfe
ON [a].[Id] = [dfe].[Datenfeld_AnsprechpartnerID]
left join
[Datenfeld_Standardfelder] AS DFSF
ON dfe.[StandardfeldID] = [DFSF].[id] and datenfeld_kategorie = 'Ansprechpartner'
) AS j
PIVOT
(
max([Datenfeld_Inhalt]) FOR [j].[Datenfeld_Name] IN ([Medium],[Kontaktthema],[Mediengattung],[Medienthema],[E-Mail],[Homepage],[Rolle])
) AS p
)
SELECT *
--2. ALTERNATIVE:
--,COUNT(1) OVER ()
FROM tempTable
WHERE 1=1
-- 1. ALTERNATIVE:
and Row_Number BETWEEN 0 AND 100
ORDER BY Nachname, Vorname DESC
--2. ALTERNATIVE:
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
;
and following the whole dynamic query. I actually think, there is an error in it, because like this I won't get the correct row count, I would probably have to call it once again without the row_number filter to get it right...
DECLARE @filterExpression nvarchar(MAX)
DECLARE @showOnlyDoublets int
DECLARE @sortExpression nvarchar(MAX)
DECLARE @skip AS int
DECLARE @take AS [int]
SELECT @skip = 0
SELECT @take = 100
--SELECT @filterExpression = 'WHERE Vorname like ''%luc%'''
SELECT @filterExpression = ' WHERE 1=1'
SELECT @sortExpression = 'ORDER BY Nachname, Vorname desc'
SELECT @showOnlyDoublets = 0
DECLARE @idList nvarchar(MAX)
select @idList = COALESCE(@idList + '],[', '[') + [DFSF].[Datenfeld_Name] from
[Datenfeld_Standardfelder] AS DFSF
where datenfeld_kategorie = 'Ansprechpartner'
SELECT @idList = @idList +']'
--SELECT @idList
DECLARE @sqlToRun nvarchar(max)
SET @sqlToRun =
'With tempTable As
(
SELECT *
, ROW_NUMBER() Over (' + @sortExpression + ') As Row_Number
FROM
(
SELECT [a].*, [DFSF].[Datenfeld_Name],[Datenfeld_Inhalt]--, CAST( ROW_NUMBER() OVER(ORDER BY [DFSF].[Datenfeld_Name] DESC) AS varchar(20))
FROM
[dbo].[Ansprechpartner] AS a left join [dbo].[Datenfeld] AS dfe
ON [a].[Id] = [dfe].[Datenfeld_AnsprechpartnerID]
left join
[Datenfeld_Standardfelder] AS DFSF
ON dfe.[StandardfeldID] = [DFSF].[id] and datenfeld_kategorie = ''Ansprechpartner''
) AS j
PIVOT
(
max([Datenfeld_Inhalt]) FOR [j].[Datenfeld_Name] IN (' + @idList + ')
) AS p
)
SELECT *, COUNT(*) OVER () as Total_Rows FROM tempTable
' + @filterExpression + '
AND Row_Number BETWEEN ' + CAST ( @skip AS varchar ) + ' AND ' + CAST ( @take AS varchar ) + '
' + @sortExpression + '
--OFFSET ' + CAST ( @skip AS varchar ) + ' ROWS FETCH NEXT ' + CAST ( @take AS varchar ) + ' ROWS ONLY
;'
PRINT @sqlToRun
EXECUTE sp_executesql @sqlToRun
So my question is: is there a way to improve this query (one of the two alternatives)? Or do you have a totally different idea, because I think, either way, if I call the count correctly, it will cost a lot of time.
