[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Limit & Offset Using Top - Like MySQL

Posted on 2004-04-07
1
Medium Priority
?
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1125 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

Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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