• Freinds,

    Finally the confusion has got endup and based on the advice from you guys (bit from each of you) have achieved the query to run faster and completed in 2 seconds (without any index implementation)

    what i did is:

    1)the query used to take count and to fetch the rows are the same, so populated that into a temp table with identity column.

    2)taken the max of the identity from @@identity instead of executing the query for one more time

    3)paging was implemented on the temp table with offset and fetch next row option in 2012

    4)excluded the XML parsing (as input parameter) and substituted with individual parameters.

    thus executing in 2 seconds.

    thanks guys.

    Prabhu.st