SQL Server Performance

Tweaking SP/Query for improved performance

Discussion in 'T-SQL Performance Tuning for Developers' started by ClassyThemes, Jun 19, 2003.

  1. ClassyThemes New Member

    Hi All.
    I'm running into a problem with one of the queries on my site. At times it takes up to 15% of the resources on the server to run it.

    The purpose of the query is to pull a number of records on to the page which meet a certain criteria. This is do diplay my products in a "paged" fashion.

    I'm using this stored procedure:

    *************************************************************************

    (
    @Page int,
    @RecsPerPage int,
    @SQL nvarchar(500)
    )
    AS

    -- We don't want to return the # of rows inserted
    -- into our temporary table, so turn NOCOUNT ON
    SET NOCOUNT ON

    -- Find out where we will start our records from
    DECLARE @RecCount int
    SELECT @RecCount = @RecsPerPage * @Page + 1



    --Create a temporary table
    CREATE TABLE #TempItems
    (
    ID int IDENTITY,
    theme int,
    ProductID int,
    ModelName nvarchar(50),
    ThemeHomePage text,
    Description nvarchar(3800),
    UnitCost money,
    UnitSaleCost money,
    ProductImage nvarchar(50),
    ModelNumber nvarchar(50),
    ThemeDesigner nvarchar(75),
    YourID nvarchar(50),
    PMID nvarchar(50)
    )


    -- Insert the rows from tblItems into the temp. table
    INSERT INTO #TempItems (theme, ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, YourID, PMid)
    Exec('SELECT theme, ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid FROM Products WHERE '+@SQL)
    --SELECT theme, ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber FROM Products ORDER BY ModelName

    -- Find out the first and last record we want
    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)

    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID > @LastRec
    )
    FROM #TempItems
    WHERE ID > @FirstRec AND ID < @LastRec


    -- Turn NOCOUNT back OFF
    SET NOCOUNT OFF

    *******************************************

    Then I execute this query: SQL = "theme=2 AND Disable IS NULL ORDER BY Modelname"


    Any ideas how I can modify my system to take up less resources??

    BTW - we have over 2500 products which are in the db and are sorted with this query.

    Thanks in advance.



  2. Malcolm New Member

    It looks like you are allowing parameter of, say,

    Display page number 10 with 20 rows per page, with a where clause that is defined previously.

    If the idea is to use the where clause to select for example 800 of the original rows and then display a single page with a result of record 181 to 200 then I wonder whether you would do better to create the full result set of 800 in a temporary table and display starting at record number 181 using a row number. The reason I suggest this as an alternative is that if the next query want to display another page but using the same where clause, the data would already be residing on the temp table and the query would only need to display from the temp table and not run the query with the where clause ?
    So the display part of the query may look like -
    Select Top @RecsPerPage * from temptable
    (I have used * as the result set to save space !)
  3. ClassyThemes New Member

    Hi Malcom, thanks for your prompt reply.

    I thought that's what I was doing. <yikes!>

    Isn't this what you are referring to??

    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)

    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID > @LastRec
    )
    FROM #TempItems
    WHERE ID > @FirstRec AND ID < @LastRec


  4. gaurav_bindlish New Member

    The point that Malcom was trying to stress was that you are inserting data into temporary table everytime you want to generate a page. Instead, run a stored procedure for data generation once and then use another stored procedure to page through the data. Ofcourse this assumes that the base data does not change that often.

    Seehttp://sql-server-performance.com/forum/topic.asp?TOPIC_ID=595 for similar discussion.

    HTH.


    Gaurav
  5. ClassyThemes New Member

    Thanks for the clarification.

    I'll check out the thread and see what I can figure out.
  6. ClassyThemes New Member

    <sigh!>

    I'm afraid this is all over my head.

    Products are added to our db throughout the day & night, so with the base data changing at any time, creating this other temp table and then extracting from it might not return the most recent products. Isn't that right?? The additions/deletions are random...sometimes 100+ /day sometimes less than 10.

    With this in mind, should I continue with the additional temp table option??


  7. gaurav_bindlish New Member

    No in thid case its not a good idea. But there is one more thing that you can do. When you populate the data in temp table, insert time stamp for insertion as well. And when you are providing the data to the user, check if the data is older than a threshold time and if it is, refresh the table.


    Gaurav
  8. bambola New Member

    Let's try it one step at a time.
    The first thing you need to understand, is that creating a temp table that containts all the fields is costly. So I would begin by limiting the columns to IDENTITY + PK of table. The insert would be very much the same. In the last select, JOIN the temp table with the original one to get the data you need.
    There could be other things you could do, but let's take it slowly. I'll try to help you write the code once I get home.

    Bambola.
  9. ClassyThemes New Member

    Thanks babola!

    OK....I think I understand that what you are getting at.

    I appreciate your offer of help when you get home. In the meantime I'll try and see if I can get it running as you suggest, with the join.

  10. ClassyThemes New Member

    Hi all.

    I'm able to limit the columns to Identity & ProductID for the table....but I'm not sure where I should be putting the Join.

    Thanks!
  11. bambola New Member

    Here it is. Let me know if you are having any problems.


    CREATE PROCEDURE page_records
    (
    @Page int,
    @RecsPerPage int,
    @SQL nvarchar(500)
    )
    AS

    SET NOCOUNT ON

    DECLARE
    @FirstRec int,
    @LastRec int,
    @row_count int,
    @error int

    -- calculating start and end row
    SELECT @FirstRec = (((@Page - 1) * @RecsPerPage) + 1)
    SELECT @LastRec = @FirstRec + @RecsPerPage - 1

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    seq_id int IDENTITY primary key,
    ProductID int -- I assume this is the PK of the table.
    )

    -- Insert the rows from tblItems into the temp.
    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Products WHERE ' + @SQL)

    SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
    -- @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
    -- add error control here.

    SELECT theme, t.ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid
    FROM #TempItems t WITH (NOLOCK)
    INNER JOIN NORTHWIND..Products p
    ON p.ProductID = t.ProductID
    WHERE seq_id BETWEEN @FirstRec AND @LastRec

    -- calculating number or rows and pages
    IF @@ROWCOUNT > 0
    SELECT @row_count as ROW_COUNT,
    @row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount
    ELSE
    SELECT 0 ROW_COUNT, 0 PageCount

    -- cleanup
    DROP TABLE #TempItems

    SET NOCOUNT OFF

    Bambola.
  12. gaurav_bindlish New Member

    Great thought bambola. I missed this.

    ClassyThemes, When you are selecting data form temp table, join the temp table having identity and primary key to main table on ptimary key. e.g. if theme, ProductID is primary key then query becomes,
    SELECT b.*,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID > @LastRec
    )
    FROM #TempItems a
    join Products b
    on a.Theme = b.Theme
    and a.ProductID = b.ProductID
    WHERE ID > @FirstRec AND ID < @LastRec
    HTH.


    Gaurav
  13. bambola New Member

    quote:Originally posted by gaurav_bindlish
    ClassyThemes, When you are selecting data form temp table, join the temp table having identity and primary key to main table on ptimary key. e.g. if theme, ProductID is primary key then query becomes,
    SELECT b.*,
    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID > @LastRec
    )
    FROM #TempItems a
    join Products b
    on a.Theme = b.Theme
    and a.ProductID = b.ProductID
    WHERE ID > @FirstRec AND ID < @LastRec
    HTH.
    Gaurav

    There is really no need to COUNT the number of records in the temp table. It is a waste of time and resources. You can use the @@ROWCOUNT (asigned into the variable @row_count in my example above) to calculate it (as shown at the end).

    Bambola.
  14. ClassyThemes New Member

    Thanks so much for your help! A very slick routine which I think will help my problem.

    The only last issue relates to the "MoreRecords".

    How do I use your "PageCount" or "RowCount" in the page.

    You see, originally, I was paging the db with this code:

    if CInt(rs("MoreRecords")) > 0 then
    bolLastPage = False
    NumPages = Cint(rs("MoreRecords"))/10
    else
    bolLastPage = True
    end if

    Now I do not have a "MoreRecords" in the rs. so I'm not sure how to do that part.

    Thanks again!
  15. bambola New Member

    I assume we are talking asp. Basically you have 2 recorset returned by this procedure.


    <%
    dim conn' connection object
    dim rs' record set
    dim data' an array to hold the record set
    dim Page' number of page you want ro retrieve
    dim RecsPerPage ' number of records per page
    dim PageCount ' number of pages left
    dim ROW_COUNT ' number of rows corrisponding to the search condition

    ' open a connection

    ' call the function that is calling the paging paging stored procedure
    set rs = PageRecords(conn, ... Page , RecsPerPage)

    ' throwing result set into an array.
    if not rs.EOF then
    data = rs.getrows
    end if

    ' getting the next recordset (the one that holds 2 paramaters: PageCount and ROW_COUNT)
    set rs = rs.NextRecordset

    if not rs.EOF then
    ROW_COUNT = rs("ROW_COUNT")
    PageCount = rs("PageCount")
    else
    ROW_COUNT = 0
    end if

    ' Cleanup
    ' closing recordset
    ' closing connection
    %>

    Bambola.
  16. gaurav_bindlish New Member

    The idea was to return only one recordset to the client application. If returning two recordsets is fine, I think bambola's solution is good.

    Gaurav
  17. ClassyThemes New Member

    Naturally, one rs would be better. I'm still working on it.

    Thanks.
  18. ClassyThemes New Member

    BEAUTIFUL!

    I was able to get it to work with one RS.

    All I did was add the orginal

    MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.ID > @LastRec
    )

    To my final select and it works like a charm!

    It's hard to tell so soon after implementing it, but it looks like it's only taking 30% server recources to run the query.

    Thank you all so much!
  19. gaurav_bindlish New Member

    Actually you can use the @@rowcount variable in the select statement. This way Bambola's concern is also addressed. I am making changes to his code and posting it


    quote:Originally posted by bambola

    Here it is. Let me know if you are having any problems.


    CREATE PROCEDURE page_records
    (
    @Page int,
    @RecsPerPage int,
    @SQL nvarchar(500)
    )
    AS

    SET NOCOUNT ON

    DECLARE
    @FirstRec int,
    @LastRec int,
    @row_count int,
    @error int

    -- calculating start and end row
    SELECT @FirstRec = (((@Page - 1) * @RecsPerPage) + 1)
    SELECT @LastRec = @FirstRec + @RecsPerPage - 1

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    seq_id int IDENTITY primary key,
    ProductID int -- I assume this is the PK of the table.
    )

    -- Insert the rows from tblItems into the temp.
    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Products WHERE ' + @SQL)

    SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
    -- @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
    -- add error control here.

    SELECT theme, t.ProductID, ModelName, ThemeHomePage, Description, UnitCost,
    UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid, @row_count as ROW_COUNT,
    @row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount

    FROM #TempItems t WITH (NOLOCK)
    INNER JOIN NORTHWIND..Products p
    ON p.ProductID = t.ProductID
    WHERE seq_id BETWEEN @FirstRec AND @LastRec

    -- cleanup
    DROP TABLE #TempItems

    SET NOCOUNT OFF

    Bambola.

    HTH.

    Gaurav
  20. bambola New Member

    quote:The idea was to return only one recordset to the client application. If returning two recordsets is fine, I think bambola's solution is good.

    Really? I must have missed the part where it said "return only one recordset", and I swear, it took me more than 47 seconds to read.
    And what is the problem with returning 2 recordset? Is it better to run count on a table (that in this example has 2500 rows but could have more) when I already know the number of records there just to avoid returning 2 recordsets?

    Anyway, here are my tests. I created a table with 2500 rows and 2 sprocs that run on it.
    The sproc that creates the temp table with only IDENTITY + PK runs in less than half time (see results bellow).



    USE NORTHWIND
    GO

    CREATE TABLE Test_Products (
    theme int,
    ProductID int IDENTITY(1,1),
    ModelName nvarchar(50),
    Description nvarchar(1000),
    UnitCost money,
    UnitSaleCost money,
    ProductImage nvarchar(50),
    ModelNumber nvarchar(50),
    ThemeDesigner nvarchar(75),
    YourID nvarchar(50),
    PMID nvarchar(50)
    )

    SET NOCOUNT ON

    -- inserting 2500 records in a table that similar to ClassyThemes only smaller (no text field, and Description reduce to 1000)

    INSERT INTO Northwind.dbo.Test_Products
    (theme, ModelName, [Description], UnitCost, UnitSaleCost, ProductImage, ModelNumber, ThemeDesigner, YourID, PMID)
    select12, replicate('a', 50), replicate('b', 1000), 100.0, 100.0, replicate('c', 50), replicate('d', 50), replicate('e', 75), replicate('f', 50), replicate('g', 50)
    from dbo.[Order Details]

    GO
    set rowcount 345
    INSERT INTO Northwind.dbo.Test_Products
    (theme, ModelName, [Description], UnitCost, UnitSaleCost, ProductImage, ModelNumber, ThemeDesigner, YourID, PMID)
    select12, replicate('a', 50), replicate('b', 1000), 100.0, 100.0, replicate('c', 50), replicate('d', 50), replicate('e', 75), replicate('f', 50), replicate('g', 50)
    from dbo.[Order Details]
    GO

    -- First stored procedure that uses a temp table with identity + PK.

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE page_records_only_pk
    (
    @Page int,
    @RecsPerPage int,
    @SQL nvarchar(500)
    )
    AS

    SET NOCOUNT ON

    DECLARE
    @FirstRec int,
    @LastRec int,
    @row_count int,
    @error int

    -- calculating start and end row
    SELECT @FirstRec = (((@Page - 1) * @RecsPerPage) + 1)
    SELECT @LastRec = @FirstRec + @RecsPerPage - 1

    --Create a temporary table
    CREATE TABLE #TempItems
    (
    seq_id int IDENTITY primary key,
    ProductID int -- I assume this is the PK of the table.
    )

    -- Insert the rows from tblItems into the temp.
    INSERT INTO #TempItems (ProductID)
    EXEC('SELECT ProductID FROM Test_Products WHERE ' + @SQL)

    SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
    -- @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
    -- add error control here.

    SELECT theme, t.ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid
    FROM #TempItems t WITH (NOLOCK)
    INNER JOIN NORTHWIND..Test_Products p
    ON p.ProductID = t.ProductID
    WHERE seq_id BETWEEN @FirstRec AND @LastRec

    -- calculating number or rows and pages
    IF @@ROWCOUNT > 0
    SELECT @row_count as ROW_COUNT,
    @row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount
    ELSE
    SELECT 0 ROW_COUNT, 0 PageCount

    -- cleanup
    DROP TABLE #TempItems

    SET NOCOUNT OFF


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    -- Second stored procedure which insert all rows into temp table.
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE page_records_all_fields
    (
    @Page int,
    @RecsPerPage int,
    @SQL nvarchar(500)
    )
    AS

    SET NOCOUNT ON

    DECLARE
    @FirstRec int,
    @LastRec int,
    @row_count int,
    @error int

    -- calculating start and end row
    SELECT @FirstRec = (((@Page - 1) * @RecsPerPage) + 1)
    SELECT @LastRec = @FirstRec + @RecsPerPage - 1

    --Create a temporary table
    CREATE TABLE #TempItems (
    seq_id int IDENTITY primary key,
    theme int,
    ProductID int,
    ModelName nvarchar(50),
    ThemeHomePage text,
    Description nvarchar(1000),
    UnitCost money,
    UnitSaleCost money,
    ProductImage nvarchar(50),
    ModelNumber nvarchar(50),
    ThemeDesigner nvarchar(75),
    YourID nvarchar(50),
    PMID nvarchar(50)
    )
    -- Insert the rows from tblItems into the temp.
    INSERT INTO #TempItems ([theme], [ProductID], [ModelName], [ThemeHomePage], [Description], [UnitCost], [UnitSaleCost], [ProductImage], [ModelNumber], [ThemeDesigner], [YourID], [PMID] )
    EXEC('SELECT [theme], [ProductID], [ModelName], [ThemeHomePage], [Description], [UnitCost], [UnitSaleCost], [ProductImage], [ModelNumber], [ThemeDesigner], [YourID], [PMID] FROM Test_Products WHERE ' + @SQL)

    SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
    -- @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
    -- add error control here.

    SELECT [theme], [ProductID], [ModelName], [ThemeHomePage], [Description], [UnitCost], [UnitSaleCost], [ProductImage], [ModelNumber], [ThemeDesigner], [YourID], [PMID]
    FROM #TempItems WITH (NOLOCK)
    WHERE seq_id BETWEEN @FirstRec AND @LastRec

    -- calculating number or rows and pages
    IF @@ROWCOUNT > 0
    SELECT @row_count as ROW_COUNT,
    @row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount
    ELSE
    SELECT 0 ROW_COUNT, 0 PageCount

    -- cleanup
    DROP TABLE #TempItems

    SET NOCOUNT OFF

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    -- running page_records_all_fields procedure
    dbcc dropcleanbuffers
    DBCC FREEPROCCACHE
    declare @d datetime
    select @d = getdate()
    exec page_records_all_fields 1, 15, 'PRODUCTID > 20'
    select datediff(ms, @d, getdate())
    -- results between 1050-1200 ms

    -- running page_records_only_pk procedure
    dbcc dropcleanbuffers
    DBCC FREEPROCCACHE
    declare @d datetime
    select @d = getdate()
    exec page_records_only_pk 1, 15, 'PRODUCTID > 20'
    select datediff(ms, @d, getdate())
    -- results between 450-490 ms

    -- Cleanup
    DROP TABLE Test_Products
    DROP PROCEDURE page_records_only_pk
    DROP PROCEDURE page_records_all_fields

  21. ClassyThemes New Member

    Hey folks...<br /><br />I didn't want to start a duel. <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />Absolutley, thanks for your code bambola. It really did speed up my site as your test showed and has gotten me out of the hot seat with the hosting company for the SQL server resources.<br /><br />I am very greatful for the assistance.<br /><br />Cheers!<br /><br />www.ClassyThemes.com
  22. bambola New Member

    Duel? not at all. we are just programmers trying to prove our point with examples, sometimes with sense of humour but never in a rude way. Or so I think. Don't you?

    Bambola [8D]
  23. gaurav_bindlish New Member

    Bambola is right. The idea here is to have the fastest performing code. No matter who writes it. I have always loved healthy combination.

    Cheers,

    Gaurav
  24. bambola New Member

    ClassyThemes, you can simply return the value from the stored procedure. Two columns less, one recordset. Just add RETURN (@whatever_int) to the end of it<br /><br />Now Gaurav, you really must tell me how in the world did you manage to read *and* answer my post in 47 seconds. Talking about performance... [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Bambola.<br />
  25. gaurav_bindlish New Member

    Well that's a trade secret. I think this is a coincidence that just when your post was submitted, I logged in and saw ur post. Had this idea of joins in mind and so copied and modified the code from ClassyThemes.

    I guess u'll understand that when u are in office, u don't have much time to think. That's how it is....

    Gaurav

Share This Page