none
Getting query result set faster from table RRS feed

  • Question

  • Hi Experts,

    In one table, I've nearly 150,000 records.

    When I am doing SELECT column1,column2,column3 FROM tableName (NOLOCK)

    its taking nearly 9 minutes to get the complete result set.

    I've one clustered Index and one Non clustered index 

    Is there any way to get the result set faster.

    Regards

    NoorBi

    Monday, May 20, 2013 11:22 AM

All replies

  • I think its an expected unless there is any issue in your network if you are querying to local machine from server or any physical IO issues. Index will not help you as you are scanning the entire page.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 11:30 AM
  • I am connecting to sql server from my local and querying it.

    No other way to reduce the time?


    Monday, May 20, 2013 11:34 AM
  • A covering index would help. But it seems that you have a problem with your network, 9 min seems too much for 150.000 rows if column1, colum2 and column3 are not big. Are they?

    Monday, May 20, 2013 11:37 AM
  • N/w is good. Problem is with this table data.

    col1,col2,col3 are all of datatype VARCHAR(25).

    Monday, May 20, 2013 11:40 AM
  • N/w is good. Problem is with this table data.

    col1,col2,col3 are all of datatype VARCHAR(25).


    That should bo fine. But why do you want to query the entire table? If its just for analysing something, I do not think you need to worry on it. But if this is the case for an application perspective, yes, I think you need to work on the logic. But, anyway, you are fetching all the values in a table, it needs a scan on the table.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 11:43 AM
  • Because I am writing a view including this table inner join with 2 more tables.

    complete execution of this view taking nearly 9 minutes of time.

    I want to reduce the amount of time for the execution of this view.

    Monday, May 20, 2013 11:45 AM
  • Would you be able to post the execution plan..

    execute the query, note down the spid of the session. open another window and run the following 

    SELECT * FROM sys.dm_exec_requests AS der WHERE session_id=xx

    replace xx with the main quires spid. what wait type is it showing

     

    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


    • Edited by SimpleSQL Monday, May 20, 2013 11:51 AM
    Monday, May 20, 2013 11:47 AM
  • ASYNC_NETWORK_IO
    Monday, May 20, 2013 12:01 PM
  • ASYNC_NETWORK_IO

    This would indicate the slowness is in returning the results to the client rather than on the SQL Server side.  I would expect the results to return in no more than a few seconds on a LAN.

    How fast does the query return if you run it locally on the server via SSMS?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, May 20, 2013 12:07 PM
  • Please see the link 

    http://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/

    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

    Monday, May 20, 2013 12:23 PM
  • One way to find out if it is your network is to do a

    SELECT COUNT(column name) FROM tableName(NOLOCK)

    Pick a column which is not in your nonclustered index and allows NULL values. 

    The above will force SQL Server to scan the whole table(clustered index), but will return only a small amount of data.  That can help determine if it is SQL Server or something else like your network or the application that is displaying your data. If that returns quickly, then it is not SQL Server.  If it also takes 9 minutes, then it is probably something going on inside SQL Server.

    Tom

    Monday, May 20, 2013 1:08 PM
  • Because I am writing a view including this table inner join with 2 more tables.

    complete execution of this view taking nearly 9 minutes of time.

    I want to reduce the amount of time for the execution of this view.

    Are all three tables in the same database?

    Are all the join columns indexed on each table?

    Have you run a select on the view locally on the server?

    Monday, May 20, 2013 8:06 PM
  • when I executed query in local via SSMS it has taken just 4 secs.
    Tuesday, May 21, 2013 4:56 AM
  • Yes, all 3 tables are in same Database.

    Yes, all join columns are indexed on each table.

    Yes, I've executed select query on view locally via SSMS and it has taken 4 secs.

    Tuesday, May 21, 2013 4:57 AM
  • Then its a clear indication that it has something outside SQL Server.

    ASYNC_NETWORK_IO indicates the issues in returning the results to cleint side.

    http://beyondrelational.com/modules/24/syndicated/509/posts/12828/study-of-waitstatspart-5-asyncnetworkio.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by SQLZealots Tuesday, May 21, 2013 5:07 AM
    Tuesday, May 21, 2013 5:00 AM
  • Hi Latheesh,

    I've looked in the link send by you but not able to find the solution as author had some restrictions in revealing it.

    if you have any solution, could you please help.

    SELECT  m.Col1
           ,s.Col2
           ,m.Col3
           ,J.Col4
           ,ISNULL(RTRIM(J.Col3), '') + ' - ' + ISNULL(J.Col4, '') AS Col5
           ,m.Col6
           ,V.Col7
           ,RTRIM(V.Col6) + CASE WHEN ISNULL(V.Col7, '') = '' THEN '' ELSE ' - ' END +
            ISNULL(V.Col7, '')  AS Col8
           ,m.Col9
    FROM dbo.TableA m (NOLOCK)
    JOIN dbo.TableB J  (NOLOCK) ON m.Col3 = J.Col3
    JOIN dbo.TableC V  (NOLOCK) ON m.Col6 = V.Col6
    JOIN dbo.TableD s  (NOLOCK) on s.Col2 = m.Col2

    I've PK on TableA for all the fields in it. 

    Tuesday, May 21, 2013 5:29 AM
  • You need to have supporting indexes for allt he tables.

    Can you post the indexes on those tables and execution plan for the query?

    Ideally, TableA.Col3,TableB.Col3,TableC.Col6,TableD.Col2 are good candidates for the index. Having said, with very limite knowledge on your system.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 21, 2013 5:33 AM
  • Hi Lateesh,

    TableA has Clustered index on Col1,col2,Col3,Col6

    TableB has Clustered index on Col3, 

    TableC has Clustered index on Col6 and TableD has Non clustered index on col2.

    in execution plan am seeing 3 Hash match joins consuming - TableA and TableD join 23%

    TableA JOIN TableB -> 24%

    TableA join TableC -> 31%

    • Edited by SQL2012BI Tuesday, May 21, 2013 5:49 AM
    Tuesday, May 21, 2013 5:45 AM
  • Can you post the execution plan for the query?

    Try creating non clustered index on Col2 and Col3 on TableA.

    BTB, How much data that you are fetching and how much time it is taking to fetch? How many rows are present in the tables?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 21, 2013 5:50 AM
  • TableA have 144961 records,

    TableB have 998 records, 

    TableC have 184 records and TableD have 1603 records.

    When I am executing SELECT * FROM TableA in local sql server machine via SSMS its taking 3 secs while the same from my local machine to sql server its taking 3 minutes.

    the complete logic (Joining 4 tables for view taking around 8 mins from my local and 5 secs in local server machine)

    Tuesday, May 21, 2013 6:07 AM
  • Noorbi, Lets forget about the difference between local and SSMS for now.

    But your plan shows scan on the tables. Also, I can see compute scalar. Can you open the property and see why the compute scalar happens at first? If there is any implicit conversion, please cross check the datatypes of table that are joining.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 21, 2013 6:14 AM
  • that conversion is because am concatenating columns but here it is 0%.

    Yes, Datatypes are compatible.

    Tuesday, May 21, 2013 9:59 AM
  • Any progress?

    TableA has Clustered index on Col1,col2,Col3,Col6

    The composite CI is not helpful when JOINing on Col3.

    Consider adding index support for ON & WHERE clause columns for example NCI on Col3.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, June 10, 2013 10:24 PM
    Moderator
  • Hi Noorbi

    Use of non-clusteres index insted Clusterd one can solve your probelm.. or if there is calculation in the script make all those in ##temp table and then insert final output in final table if desired.. More ever try below link for query otimization.. I guess, your answer lies within..

    http://www.sqlusa.com/articles/query-optimization

    Tuesday, June 11, 2013 7:13 AM