Solved

Limit & Offset Using Top - Like MySQL

Posted on 2004-04-07
1
753 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

837 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