Post reply

Query becoming slower when records are retrieved..how to make it faster

  • November 10, 2014 at 8:01 am

    #300931

    Dear all

    Following is my db table

    student_id student_code student_parent_id student_name

    1 11 0 a

    2 111 1 b

    3 1111 2 c

    4 11111 3 d

    I want to generate following op

    student_id student_code student_parent_id student_name Hierarchy

    1 11 0 a 11

    2 111 1 b 11-111

    3 1111 2 c 11-111-1111

    4 11111 3 d 11-111-1111-11111

    Following is the query

    i want to retrieve around 10000 in one go.. its taking around 8 seconds.. how to make it faster???

    even if i retrieve 1 record or 10000 records, its taking around 8 seconds...

    --- create table

    create table test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

    ---- insert records

    insert into test values (1, '11', 0, 'a')

    insert into test values (2, '111', 1, 'b')

    insert into test values (3, '1111', 2, 'c')

    insert into test values (4, '11111', 3, 'd')

    ---- result query

    ;WITH SInfo AS

    (

    SELECTsId

    ,scode

    ,ParentId

    ,sName

    ,CONVERT(nvarchar(800), scode) AS Hierarchy

    FROMtest

    WHEREParentId=0

    UNIONALL

    SELECTTH.sId

    ,TH.scode

    ,TH.ParentId

    ,TH.sName

    ,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

    FROMtest TH

    INNER JOINSInfoONSInfo.sId = TH.ParentId

    )

    Select * from SInfo

    thanks

    peter

  • November 10, 2014 at 9:34 am

    #1757466

    Do you have the correct indexes on the table to support the hierarical lookups caused by the recurssion of the CTE? Also, who is going to read 10,000 rows?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 10, 2014 at 9:37 am

    #1757468

    no i dont know about the correct indexes , how can i find that out?

  • November 10, 2014 at 9:43 am

    #1757475

    As Jeff mentioned, the indexes matter. If you don't have good ones, then this can be problematic. Please include the DDL for indexes and the tables, with your sample data.

    The recursive CTE is a performance issue, and it can be problems. As Jeff mentioned, 10k rows is silly for a report of any sort, unless it's a large scale view of a hierarchy, in which case, I wouldn't run this often. I'd cache results, as this isn't likely to change often. If it does, no one will go through the details of the entire thing on a regular basis. You'd be better off limiting the scope of the results to the areas a user is interested in. What that is depends on the application.

  • November 10, 2014 at 9:52 am

    #1757479

    ok thanks,

    even if i retrieve single record also its taking around 8 seconds so that means something is going wrong with my query..

    pls let me know what can i do to make it faster...

  • November 10, 2014 at 10:36 am

    #1757492

    The 1 or 10000 being the same tells me you could almost certainly use an index on something that is seekable to get you down to that 1 row efficiently.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 10, 2014 at 10:55 am

    #1757503

    YES, thats what i am going to dp...

  • November 10, 2014 at 3:56 pm

    #1757574

    Caching is a good idea but with only 10,000 rows, this should be sub-second. The correct indexes will make that happen.

    Shifting gears a bit, if you really want a deep dive on the kind of stuff you're trying to do, please see the following article. Even if you don't end up using Nested Sets, it does use the same kind of code you're trying to write in the CTE.

    http://www.sqlservercentral.com/articles/Hierarchy/94040/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply