Solved

Limit & Offset Using Top - Like MySQL

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now