Database is taking alot of time to get the data on localhost.

[arsheena.alam]

Hi Friends!!

I am coming across this problem that whenever I am trying to retrieve the data in a dataset Through database, it is taking around 10 min to get the data no the localhost. And when I am running the same code on the live server it doesnot take time at all and I get the data in few minutes only.
My dataset Contains around 25rows and each row containing 12 columns.
I am getting this problem only in retrieving this particular dataset. Rest all are not taking time to get the data.

Can anyone tell me the solution to this.
Help Needed!!!

Thanks in advance :)

[BitBlt]

It could be any number of things, but there's no way for us to know what. You haven't told us:
1. What your data structures look like
2. What your data looks like
3. What your query looks like
4. What your machine specs are

I'm hoping Dani opens up a "MindReaders" forum, but until then...

[adam_k]

Like BitBlt said it can be a number of things and you need to provide info. For all we know you could be retrieving 25 rows of 12 fields of 2GB each. Or it could be a deadlock or ....
Addtionally to BitBlt questions: Are you connecting the same way to the server for all your datasets? Can you tell us how (ODBC, SQL native client (with named pipes or not), etc)

When everything else fails I've found that a trace and the execution plan can be a lot of help.

[arsheena.alam]

Hi,
My code looks like this.

DataSet ds = new DataSet();
SqlConnection connect;
connect=connecttodatabase() 
/* connecttodatabase is a method used to connect to the database by setting the connection string. */
SqlCommand command = new SqlCommand("GetAssignment", connect);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@assignmentId", SqlDbType.BigInt).Value = assignmentId;
command.CommandTimeout = 0;
SqlDataAdapter sda = new SqlDataAdapter(command);
sda.Fill(ds, " ");
return ds;

I have few changes in the stored procedure and now my sp is returning three datasets instead of one.
The first dataset is returning the following table:
Question_id- Bigint
Quesiton - varchar(max)
Type-int
RightAnswer- varchar(max)
Hint- varchar(max)
SubjectId- int
Unit- int
ChapterId - int
Days - int
Easy - bit
Medium- bit
difficult - bit

The second dataset is returning the following table:
Question_id - bigint
CorrectOption- varchar(max)

The third dataset is returning the following table:
Question_id - bigint
WrongOption- varchar(max)

The Same method is used to connect to database for all the datasets and I am Using SQL Server 2005

[dickersonka]

As previously stated before it could be a lot of things. The schema doesn't look like it should take anytime to query regardless if indexes are in place or not.

When you are in this 10 min waiting phase, run this command on the database, just in management studio is fine.

sp_who2

My guess, is that something is locking up these records. Might not be, but for just a basic table with a small recordset, something extraordinary is going on.

The thing to look at is the BlkBy column. Please report back your results