Tweaking SP/Query for improved performance

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.

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 !)

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

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

Thanks for the clarification.

I'll check out the thread and see what I can figure out.

<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??

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

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.

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.

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!

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.

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

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.

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!

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.

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

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

Thanks.

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!

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

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

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

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]

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

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='' />]<br /><br />Bambola.<br />

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