SqlCommand.ExecuteReader takes ages to return

[Pradeep1210]

I have the following block of code in my program:

            '' create a command object 
            Dim cmd As New SqlCommand
            cmd.CommandText = "SomeStoredProc"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 0
            cmd.Parameters.AddRange(SpParmList.ToArray)     ' SpParmList is a List of parameters

            '' Get records from database
            Using cnn As New SqlClient.SqlConnection(DbConnectionString)
                cnn.Open()
                cmd.Connection = cnn
                Using reader As SqlDataReader = cmd.ExecuteReader()      '<-- this line
                    myDS.Load(reader)
                End Using
            End Using

The problem is that it gets stuck at the highlighted lines somethimes, but not always.
Sometimes it takes just a few seconds on that line, while at other times it takes even more than half an hour, for the same stored procedure.

Note: The stored procedure returns millions of records.

Anyone knows why this happens and what can be done for this?

[techgnome]

Run SQL Profiler and start working to optimize the query... look at what's also going on, if there are locks, scans or other things in the way. Check to make sure you have indexes in all the right places and that they are up to date. Are you doing inserts or updates? or is it a select only?

-tg

[Pradeep1210]

Run SQL Profiler and start working to optimize the query... look at what's also going on, if there are locks, scans or other things in the way. Check to make sure you have indexes in all the right places and that they are up to date. Are you doing inserts or updates? or is it a select only?

-tg

I would prefer to leave that part to the developers of the Stored Procedures.

Actually this code is part of a common method in the DAL layer, and is used to execute Stored Procedures. The "myDS" is an instance of a class I developed to hold data that comes from the Reader, since the .NET DataTables can't hold this much amount of data and starts giving SystemOutOfMemory errors. The myDS.Read method accepts an IDataReader object and gets the data out of it into its collection by calling the DataReader.Read method in a loop (just the way you would read from a DataReader, nothing different).

The problem is that the program gets stuck at a line before the Read/Load method is called, and so has nothing to do with my class. This time taken is erratic and can range from a few seconds to more than half an hour, for the same stored procedure. I would guess that there is some sort of caching going on at the SQL Server end, but not sure.

I had set the CommandTimeout = 0 so that atleast the command doesn't time out. Is there anything else that can be done? Also I want to know whether the stored procedure is executed on the sql server as soon as the .ExecuteReader() method is called, or when we start reading the data out of it?

[Pradeep1210]

I tried running the stored procedures causing problems from SSMS several times. They seem to be running fine and usually give the result in maximum one to two minutes.