Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    2

    Question Unanswered: How to improve speed for query?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Demon1 View Post
    I want to speed up somehow
    that part of query e.g. SELECT MAX(*) FROM OrdersR.
    i don't think you can do that

    i haven't used the more recent versions of SQL Server, so maybe they added a new feature to the MAX function, but as far as i know, you are not allowed to say MAX(*)

    you can say COUNT(*), but not MAX(*)

    did you test this? and it worked?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    2
    Quote Originally Posted by r937 View Post
    i don't think you can do that

    i haven't used the more recent versions of SQL Server, so maybe they added a new feature to the MAX function, but as far as i know, you are not allowed to say MAX(*)

    you can say COUNT(*), but not MAX(*)

    did you test this? and it worked?
    I tried to use Count(*) and Max(*), Max works lot of faster, also in my sample Max is correct as rownows is numerical ordered.
    If will be without Where in query or use one table I used then sysindex table to scan rowstotal that works ultra fast, but in reality I can't use that tables as my queries not simple one table or in most cases I use and Where expressions.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Demon1, I am not sure I followed what you are trying to do. If you just want the Max RowNum value but only want to calculate it once, you try try a second common table expression:
    Code:
    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
      )
      , TotalRow AS
      (
       SELECT MAX(RowNum) AS TotalRows
       FROM OrdersR
      )
    SELECT *, TotalRow.TotalRows
    FROM OrdersR
       , TotalRow
    WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
    (COUNT(*) could work as well as MAX(RowNum), I think)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    demon1, i'm still pretty sure i never saw MAX(*) before, and i dispute your claim that it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •