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
jkeepiAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:


don't know how it will perform...
but i'd suggest putting this into either a temporary  table.
or generating the set of inlist values required...
there should only be at most 173? entries..

you should include distinct unless they are anyway..

the other quesrion is do you have to join everything at the lowest level? in order to obtain
your list of values...
are any of the relationships 1:1
if so then remove them from the joins and resolved them at the outermost
level...

it may help if you express what your trying to achieve in your sql
in a simple "english"    

SELECT Distinct
       Ctry2.country_id
  Into #temp
  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


do you really need to count in this procedure....
won't you already have the maximum?

your performance will be mainly determined by the availablity of usable indexes...


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
       (select contactd_contactmid
               contactd_namefirst,
               contactd_namelast,
               contactd_email
          from tbl_contactd
         Where contactd_changetype in (1,3)  
       ) as ContD
    ON ContM.contactm_id = ContD.contactd_contactmid
 INNER JOIN tbl_country Ctry
    ON ContM.contactm_countryid = Ctry.country_id
 Inner Join #temp as t
    on ContM.contactm_countryid = t.country_id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.