How to improve speed for query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to improve speed for query?

Author

Topic

Demon1
Starting Member

2 Posts

Posted - 2011-05-14 : 02:46:58

I use that below query in MSSQL 2005 for paging but still works to slow as
in my database like this table contains more then 600.000 records, I tryed
display max 20 records per page but still works slow. I seen in the
query that lost performance on TotalRecords about twice, so I want to speed up somehow
that part of query e.g. SELECT MAX(*) FROM OrdersR.

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 1;
SET @PageSize = 20;

WITH OrdersR AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate) AS RowNum
,o.OrderID, o.OrderDate, o.CustomerID, ol.SKU
FROM dbo.Orders as o
INNER JOIN dbo.OrderLines AS ol ON ol.OrderID=o.OrderID
)
SELECT *, (SELECT MAX(*) FROM OrdersR) as TotalRows
FROM OrdersR
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize

Is it possible somehow to execute one time query SELECT MAX(*) FROM OrdersR without
lost speed in my sample, or is it other alternative paging method that works faster.
As I need to display total records too.

For my table not helped any indexes that's why I think nead to speed up query.

Thanks,
Demon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-14 : 02:56:09

DECLARE @PageNum INT, @PageSize INT

SELECT @PageNum = 1, @PageSize = 20

;WITH ctePaging
AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum
,o.OrderID, o.OrderDate, o.CustomerID, ol.SKU, COUNT(*) OVER () AS TotalRows
FROM dbo.Orders as o
INNER JOIN dbo.OrderLines AS ol ON ol.OrderID = o.OrderID
)
SELECT *
FROM ctePaging
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize

N 56°04'39.26"
E 12°55'05.63"

Demon1
Starting Member

2 Posts

Posted - 2011-05-14 : 10:02:36

Thanks for quick reply. I will try you solution and will check if that helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources