none
strange behaviour of only one table in database

    Question

  • Hi,


    Problem with executing query only with specific table in the database. I placed this table in stored procedure.

    SELECT * FROM TABLE1;   - This query work fine.

    When i search or join the same TABLE1 executing continuously; it seems to me is strange thing.

    --This is not working
    1. SELECT * FROM TABLE1 where name='xyz'; 
    2. SELECT * FROM TABLE1 where name in ('xyz','aaa','ask');
    3. SELECT * FROM TABLE1 AS A
    INNER JOIN
    TABLE2 AS B
    ON
    A.NAME=B.NAME

    Executing above three continuously for hours. I don't know what the problem here.

    Hoping for your help

    Thanks.

    Best Regards

    Kashif Chotu

    Tuesday, May 14, 2013 11:19 AM

Answers

  • I have 85193 rows.

    There is no index.  Its a small table.

    A table with 85193 is not a small as you think. Can you try creating an index on Name and see the difference. Without an index, it needs to look at/scan all the pages of table.


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

    • Marked as answer by Kashif Chotu Tuesday, May 14, 2013 12:06 PM
    Tuesday, May 14, 2013 11:48 AM

All replies

  • Can you post the error message you got for the same.
    Tuesday, May 14, 2013 11:21 AM
  • There is no error.

    Executing query....     (only this message at the bottom).

    Tuesday, May 14, 2013 11:24 AM
  • Are u getting any error or the query goes on executing.If the query is executing without any error that means table doesn.t have any index and since u have more data Sql server is taking time to fetch the result . This may be one of the reason.But lets see for any experts reply.


    Please have look on the comment

    Tuesday, May 14, 2013 11:24 AM
  • Selecting without a predicate is much faster than with predicate?How much time it takes without predicate?  Could you please let us know the number of records in the table? Could you please let us know are there any index on Name column?


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

    Tuesday, May 14, 2013 11:24 AM
  • I have 85193 rows.

    There is no index.  Its a small table.

    Tuesday, May 14, 2013 11:27 AM
  • It is quite possible that the NAME field has some extra space character(carriage return/line feed or any) at the end of string. Sometime they are not visible. In order to check the presence of such character, you can try exporting the NAME field or paste it on a note pad or notepad++. You will be able to view those character.If that is a ASCII character try replacing it and use your SELECT statement again. Hopefully that should work.


    Regards, RSingh

    Tuesday, May 14, 2013 11:38 AM
  • I did all the test before posting my question.

    It runs smoothly yesterday without having any problem; problem started today.

    I have group of stored procedures which I am executing; this table is lot of depend upon other task.  As stored procedures executing continuously, I troubleshoot statement by statement and found problem which there is search or join along with this table. Others tables working fine without any problem.

    Thanks.

    Tuesday, May 14, 2013 11:47 AM
  • I have 85193 rows.

    There is no index.  Its a small table.

    A table with 85193 is not a small as you think. Can you try creating an index on Name and see the difference. Without an index, it needs to look at/scan all the pages of table.


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

    • Marked as answer by Kashif Chotu Tuesday, May 14, 2013 12:06 PM
    Tuesday, May 14, 2013 11:48 AM
  • Or maybe you can post the estimated execution plan of the queries that take forever and the actual query plan for the one that is fast.
    Tuesday, May 14, 2013 11:52 AM
  • First-of-all thank for the solving the problem.

    It works fine when created index.

    Will you please let me know it works fine for the past many days then suddenly problem started today. Can you answer please for this reason?

    Thanks again.

    Best Regards

    Kashif Chotu

    Tuesday, May 14, 2013 12:10 PM
  • Is there any data skew recently? For heap, there will be chance of having heap fragmentation which might be the cause of the delay.

    It would be extremely difficult to say the exact issue within the forum scope.


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

    Tuesday, May 14, 2013 12:19 PM