Solved

Limit & Offset Using Top - Like MySQL

Posted on 2004-04-07
1
754 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:jkeepi
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 375 total points
ID: 10778509


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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database maintenance 36 100
T-SQL to copy a database 37 66
Upgrading to SQL Server 2015 Express 2 29
sql query 5 43
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question