jkeepi
asked on
Limit & Offset Using Top - Like MySQL
I have written an SP which allows me to retrieve a specified number of records from an offset I have declared (eg 25 - 50). The SP also allows me to sort the results on any field in either ASC or DESC.
This SP is fairly large / complex and I'm unsure how it will perform.
Can you offer any suggestions as to how performance can be improved... ?
CREATE PROCEDURE usp_GetContactsBasic
@iUserID int,
@iOffset int = 0,
@iLimit int = 10,
@sOrderField nvarchar(32) = 'contactd_namefirst',
@bOrderDesc bit = 0
AS
DECLARE @sExecSQL nvarchar(4000),
@sParamList nvarchar(4000),
@iTotalRows int,
@iRecords int,
@sOrder1 nvarchar(4),
@sOrder2 nvarchar(4)
SET @iTotalRows = @iOffset + @iLimit
SELECT @sOrder1 = CASE @bOrderDesc
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END
SELECT @sOrder2 = CASE @bOrderDesc
WHEN 0 THEN 'DESC'
ELSE 'ASC'
END
SET NOCOUNT ON
SELECT @iRecords = COUNT(*)
FROM tbl_companyd CompD INNER JOIN
tbl_companym CompM ON CompD.companyd_companymid = CompM.companym_id INNER JOIN
tbl_contactm ContM ON CompD.companyd_id = ContM.contactm_companydid INNER JOIN
tbl_contactd ContD ON ContM.contactm_id = ContD.contactd_contactmid AND
(ContD.contactd_changetype = 1 OR ContD.contactd_changetype = 3) INNER JOIN
tbl_country Ctry ON ContM.contactm_countryid = Ctry.country_id
WHERE ContM.contactm_countryid IN
(SELECT Ctry2.country_id
FROM tbl_user Usr2
INNER JOIN tbl_access Acc2 ON Usr2.user_id = Acc2.access_userid
INNER JOIN tbl_country Ctry2 ON Acc2.access_countryid = Ctry2.country_id
WHERE Usr2.user_id = @iUserID)
SELECT @iRecords [COUNT]
If @iTotalRows > @iRecords
BEGIN
SET @iTotalRows = @iRecords
SET @iLimit = @iRecords - @iOffset
END
IF @iLimit < 0
BEGIN
SET @iLimit = 0
SET @iTotalRows = 0
END
SET
@sExecSQL = N'SELECT * FROM ('
+ N' SELECT TOP ' + CAST(@iLimit AS nvarchar(16)) + ' * FROM ('
+ N' SELECT TOP ' + CAST(@iTotalRows AS nvarchar(16)) + ' '
+ N' CompM.companym_name, '
+ N' ContD.contactd_namefirst, '
+ N' ContD.contactd_namelast, '
+ N' ContD.contactd_email, '
+ N' Ctry.country_full, '
+ N' ContM.contactm_id, '
+ N' ContM.contactm_changemade '
+ N' FROM tbl_companyd CompD INNER JOIN '
+ N' tbl_companym CompM ON CompD.companyd_companymid = CompM.companym_id INNER JOIN '
+ N' tbl_contactm ContM ON CompD.companyd_id = ContM.contactm_companydid INNER JOIN '
+ N' tbl_contactd ContD ON ContM.contactm_id = ContD.contactd_contactmid AND '
+ N' (ContD.contactd_changetype = 1 OR ContD.contactd_changetype = 3) INNER JOIN '
+ N' tbl_country Ctry ON ContM.contactm_countryid = Ctry.country_id '
+ N' WHERE ContM.contactm_countryid IN '
+ N' (SELECT Ctry2.country_id '
+ N' FROM tbl_user Usr2 '
+ N' INNER JOIN tbl_access Acc2 ON Usr2.user_id = Acc2.access_userid '
+ N' INNER JOIN tbl_country Ctry2 ON Acc2.access_countryid = Ctry2.country_id '
+ N' WHERE Usr2.user_id = @iUserID '
+ N' )'
+ N' ORDER BY ' + @sOrderField + ' ' + @sOrder1 + ', contactm_id ' + @sOrder1 + ') [First]'
+ N' ORDER BY ' + @sOrderField + ' ' + @sOrder2 + ', contactm_id ' + @sOrder2 + ') [Second]'
+ N'ORDER BY ' + @sOrderField + ' ' + @sOrder1 + ', contactm_id ' + @sOrder1
SET
@sParamList = N'@iUserID int'
EXEC sp_executesql
@stmt = @sExecSQL,
@params = @sParamList,
@iUserID = @iUserID
SET NOCOUNT OFF
GO
DROP PROCEDURE usp_GetContactsBasic
EXEC usp_GetContactsBasic 1, 0, 10, 'companym_name', 0
This SP is fairly large / complex and I'm unsure how it will perform.
Can you offer any suggestions as to how performance can be improved... ?
CREATE PROCEDURE usp_GetContactsBasic
@iUserID int,
@iOffset int = 0,
@iLimit int = 10,
@sOrderField nvarchar(32) = 'contactd_namefirst',
@bOrderDesc bit = 0
AS
DECLARE @sExecSQL nvarchar(4000),
@sParamList nvarchar(4000),
@iTotalRows int,
@iRecords int,
@sOrder1 nvarchar(4),
@sOrder2 nvarchar(4)
SET @iTotalRows = @iOffset + @iLimit
SELECT @sOrder1 = CASE @bOrderDesc
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END
SELECT @sOrder2 = CASE @bOrderDesc
WHEN 0 THEN 'DESC'
ELSE 'ASC'
END
SET NOCOUNT ON
SELECT @iRecords = COUNT(*)
FROM tbl_companyd CompD INNER JOIN
tbl_companym CompM ON CompD.companyd_companymid = CompM.companym_id INNER JOIN
tbl_contactm ContM ON CompD.companyd_id = ContM.contactm_companydid INNER JOIN
tbl_contactd ContD ON ContM.contactm_id = ContD.contactd_contactmid AND
(ContD.contactd_changetype
tbl_country Ctry ON ContM.contactm_countryid = Ctry.country_id
WHERE ContM.contactm_countryid IN
(SELECT Ctry2.country_id
FROM tbl_user Usr2
INNER JOIN tbl_access Acc2 ON Usr2.user_id = Acc2.access_userid
INNER JOIN tbl_country Ctry2 ON Acc2.access_countryid = Ctry2.country_id
WHERE Usr2.user_id = @iUserID)
SELECT @iRecords [COUNT]
If @iTotalRows > @iRecords
BEGIN
SET @iTotalRows = @iRecords
SET @iLimit = @iRecords - @iOffset
END
IF @iLimit < 0
BEGIN
SET @iLimit = 0
SET @iTotalRows = 0
END
SET
@sExecSQL = N'SELECT * FROM ('
+ N' SELECT TOP ' + CAST(@iLimit AS nvarchar(16)) + ' * FROM ('
+ N' SELECT TOP ' + CAST(@iTotalRows AS nvarchar(16)) + ' '
+ N' CompM.companym_name, '
+ N' ContD.contactd_namefirst, '
+ N' ContD.contactd_namelast, '
+ N' ContD.contactd_email, '
+ N' Ctry.country_full, '
+ N' ContM.contactm_id, '
+ N' ContM.contactm_changemade '
+ N' FROM tbl_companyd CompD INNER JOIN '
+ N' tbl_companym CompM ON CompD.companyd_companymid = CompM.companym_id INNER JOIN '
+ N' tbl_contactm ContM ON CompD.companyd_id = ContM.contactm_companydid INNER JOIN '
+ N' tbl_contactd ContD ON ContM.contactm_id = ContD.contactd_contactmid AND '
+ N' (ContD.contactd_changetype
+ N' tbl_country Ctry ON ContM.contactm_countryid = Ctry.country_id '
+ N' WHERE ContM.contactm_countryid IN '
+ N' (SELECT Ctry2.country_id '
+ N' FROM tbl_user Usr2 '
+ N' INNER JOIN tbl_access Acc2 ON Usr2.user_id = Acc2.access_userid '
+ N' INNER JOIN tbl_country Ctry2 ON Acc2.access_countryid = Ctry2.country_id '
+ N' WHERE Usr2.user_id = @iUserID '
+ N' )'
+ N' ORDER BY ' + @sOrderField + ' ' + @sOrder1 + ', contactm_id ' + @sOrder1 + ') [First]'
+ N' ORDER BY ' + @sOrderField + ' ' + @sOrder2 + ', contactm_id ' + @sOrder2 + ') [Second]'
+ N'ORDER BY ' + @sOrderField + ' ' + @sOrder1 + ', contactm_id ' + @sOrder1
SET
@sParamList = N'@iUserID int'
EXEC sp_executesql
@stmt = @sExecSQL,
@params = @sParamList,
@iUserID = @iUserID
SET NOCOUNT OFF
GO
DROP PROCEDURE usp_GetContactsBasic
EXEC usp_GetContactsBasic 1, 0, 10, 'companym_name', 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.