none
query with rownum took long time to complete RRS feed

  • Question

  • Hi all,

    really need your help. i got a query that need me to use rownum. but it tooks so long to complete. if i remove the rownum part, the query run perfectly. anyone can suggest me how to make sure this query run smooth at the same time using the rownum concept.

    Thanks.

    SELECT *
    FROM (
        SELECT
        row_number() OVER (ORDER BY LS.Partner asc, L.Featured desc, L.PostedDate desc) AS rownum,
        L.ListingID,
        L.MinPrice,
        L.MaxPrice,
        L.Registered,
        L.Featured,
        LP.FileName,
        LP.Description as PhotoDesc,
        RFV1.Description as ListingTypeDesc,
        RFV2.Description as ProTypeDesc,
        RFV3.Description as CountryDesc,
        RFV4.Description as CurrencyDesc,
        RFV4.Value as CurrencyValue,
        LFV2.Description as Location,
        LFV3.Description as SubLocation
        FROM Listing AS L
        INNER JOIN Ref_FieldValue AS RFV1 ON RFV1.ValueID = L.ListingType AND RFV1.Ref_FieldTypeID = '2' AND RFV1.Language = 1
        INNER JOIN Ref_FieldValue AS RFV2 ON RFV2.ValueID = L.ProType AND RFV2.Ref_FieldTypeID = '1' AND RFV2.Language = 1
        INNER JOIN Ref_FieldValue AS RFV3 ON RFV3.ValueID = L.CountryCode AND RFV3.Ref_FieldTypeID = '5' AND RFV3.Language = 1
        INNER JOIN Ref_FieldValue AS RFV4 ON RFV4.ValueID = L.CurrencyCode AND RFV4.Ref_FieldTypeID = '3' AND RFV4.Language = 1
        LEFT OUTER JOIN ListingPhotos AS LP ON L.ListingID = LP.ListingID AND Main = 1
        LEFT OUTER JOIN ListingFieldValue LFV2 ON L.ListingID = LFV2.ListingID AND LFV2.ListingFieldTypeID = '2' AND LFV2.Language = 1
        LEFT OUTER JOIN ListingFieldValue LFV3 ON L.ListingID = LFV3.ListingID AND LFV3.ListingFieldTypeID = '3' AND LFV3.Language = 1
        INNER JOIN SiteSubscription AS SS ON SS.ListingID = L.ListingID
        LEFT OUTER JOIN ListingSubscription AS LS ON LS.ListingID = L.ListingID AND LS.Country = 'my'
        WHERE L.Status = 'A' AND SS.MY = 1 AND LS.Partner = 1
    ) AS A
    WHERE A.rownum BETWEEN (4751) AND (4760)


     


    kneeah
    Friday, January 27, 2012 9:11 AM

Answers

  • Insert the result into a temporary table and then filter out row number. It is possible to have an IDENTITY property defined on temporary table
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 29, 2012 7:25 AM
    Moderator
  • Hello,

    You can improve the performance by providing proper indexing on the columns. Use sql server performance tools(DTW) for this it will auto suggest you the indexing problems

    Sunday, January 29, 2012 3:39 PM

All replies

  • hi

    Have you got proper indexes on the relevant tables..

    also i don't understand ..

    WHERE A.rownum BETWEEN (4751) AND (4760)????

    VT


     


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Friday, January 27, 2012 9:17 AM
  • Some items that will help for discussion:

    • Statistics from Profiler for an execution of this query
    • The query plan of this query
    • The total number of rows returned by this combination of JOIN and WHERE clause
    • List of indices on your tables
    • Which version of SQL Server you are running
    • and perhaps the total number of rows in each of the source tables

    Friday, January 27, 2012 12:55 PM
    Moderator
  • It seem you are having tempdb spills due to addition of rownum. Analyse the actual execution plan for the same. See what is the estimated memory and actual memory used for the query.

     

    - Arun Kumar Allu

    Saturday, January 28, 2012 5:53 PM
  • What happens if you only select Listing, ListingSubscription and SiteSubscription tables first for the desired row numbers into a temp table and only then join with the rest of the tables?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, January 29, 2012 4:15 AM
    Moderator
  • Insert the result into a temporary table and then filter out row number. It is possible to have an IDENTITY property defined on temporary table
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 29, 2012 7:25 AM
    Moderator
  • Hello,

    You can improve the performance by providing proper indexing on the columns. Use sql server performance tools(DTW) for this it will auto suggest you the indexing problems

    Sunday, January 29, 2012 3:39 PM
  • hi please post script of your table, so that we can get idea about your table strucutre
    Wednesday, February 1, 2012 6:51 AM
  • The row numbers are used to paginate results, mimicking the functionality that other DBs provide with LIMIT and OFFSET. This is to avoid having to return all the records that match a query to the application layer when for example only the last ten are needed (i.e. for the last page of results). With LIMIT and OFFSET this is trivial, but since MSSQL doesn't support these, it gets a little complicated.

    Do you know if there is a better way to achieve this?

     

     


    kneeah
    Friday, February 3, 2012 1:30 AM