locked
Simple Select query runs forever RRS feed

  • Question

  •  

    Hi all,

     

    I know this sounds rather dumb but my select statement is running forever! Sad

    I am trying to execute the statement through my C# code.

     

    If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.

     

    I am doing something like this:

     

    ###########################################################################################

    String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";

    SqlCommand command = new SqlCommand(query, connection);

    Object myObject = command.ExecuteScalar();

    ############################################################################################

     

    StudentTable contains roughly 1000 rows.

     

    Somebody please help me out of this. Thanks in advance.

     

    Surya

    Friday, December 14, 2007 5:21 PM

Answers

  • The indexing probably is masking a blocking issue that is cleared up in more cases now.  There is no reasonable reason why it would need to be indexed to retrieve rows from 1000 row table.  I can table scan a table of hundreds of thousands of rows on my laptop in seconds.

     

    Indexes can allow the query to be answered without scanning locked objects, which is the first step to a properly concurrent system Smile

     

    Friday, December 14, 2007 5:48 PM
  •  

    Indexing has nothing to do with the size of the table. What Anthony meant is that the query should not take that long since you only have 1000 rows.

     

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, December 14, 2007 5:49 PM

All replies

  • Any indexes on the studentDeptID  and deptName columns

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, December 14, 2007 5:28 PM
  • Thanks for your response!

     

    No, there are no indexex on the columns.

     

    Thanks,

     

    Friday, December 14, 2007 5:30 PM
  • Well, there is your problem 

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, December 14, 2007 5:34 PM
  • Doesn't sound quite right.  With only 1000 rows in the table you should be able to return the entire table right away.  Is the server being hammered by other processing?

     

    Friday, December 14, 2007 5:36 PM
    Answerer
  • What about other queries, do they run fine?  And what of "SELECT studentID from StudentTable" alone, how long does it take?  Can you post the structure?

     

    There is no reason, based on this evidence that there should be an issue.  Even if every row was on it's own page, this should take milliseconds.

     

    Is it the only statement running?

     

    My guess would be along the lines of:

     

    1. blocking from another process that is trying to update the row

    2. hardware issues

     

    Blocking seems most likely.  If you are in some form of locked/editing mode on a different row, you will get blocked, since with no indexes every edit will lock all rows, either as a table, or one row at a time (depends on the situation, but either might be possible.)

    Friday, December 14, 2007 5:45 PM
  • Thanks SQL_Menace. After creating index, it is working.

     

    But as Anthony said, I had thought a table with 1000 rows wouldn't need to be indexed.

     

    Anthony, the server is not being hit by any other processing as far as I know.

     

    Thanks,

    Friday, December 14, 2007 5:45 PM
  • The indexing probably is masking a blocking issue that is cleared up in more cases now.  There is no reasonable reason why it would need to be indexed to retrieve rows from 1000 row table.  I can table scan a table of hundreds of thousands of rows on my laptop in seconds.

     

    Indexes can allow the query to be answered without scanning locked objects, which is the first step to a properly concurrent system Smile

     

    Friday, December 14, 2007 5:48 PM
  •  

    Indexing has nothing to do with the size of the table. What Anthony meant is that the query should not take that long since you only have 1000 rows.

     

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, December 14, 2007 5:49 PM
  • Thank you all folks! Thanks for your responses!

     

     

    Friday, December 14, 2007 6:02 PM