Responsive UI as in the Mgmt Studio Queries

microsoft.public.dotnet.framework.adonet

2 Feb 2007 11:47 PM

shripathikamath

I have a SQL query, which when executed in Mgmt Studio Query Window
takes about 10-12 seconds to complete. From the time I issue the
query, I see an initial lag of about 1 seconds after which the grid
starts getting populated, and then runs its course for the remaining
10 seconds or so.

The UI responsiveness is impressive. Almost immediately, well, within
1 second at most, I see data appear from the server into my query
grid.

I'd like to get the same responsiveness if possible on my client
application written in C# using ADO.NET.

I use the SqlDataReader class off the SqlCommand class to make the
same query (identical), and yet it takes close to 9 or 10 seconds
before even the first row is retrieved by the application. I am not
saying that it takes 9 or 10 second for my DataGrid to start being
populated; it is simply that the first Read operation of the
SqlDataReader after I connect (which is instantaneous), that takes 9
to 10 seconds. Once the data retrieval starts, the grid populates in
short order.

Are there any pointers by which I can get just a few rows fast enough
to give the user the impression that the query is executing. In other
words, what techniques can I use to get the same sort of
responsiveness as in the Mgmt Studio Query Window? I tried a
suggestion of using OPTION (FAST 10) as suggested in another group
where I posted this, but that made no difference.

In case it is relevant, there are some 250,000 rows returned by the
query.

Again, what I am looking for is the same UI responsiveness (and I want
the same UI paradigm) as in Mgmt Studio, for the same query. I am not
looking for alternative UI paradigms or redefining my problem.

My implementation thus far is as follows:

I have a class that has a method to specify the SQL query string &
connection parameters and the UI thread creates this class. The UI
thread then launches a method on a background thread, which then uses
SqlDataReader to commence getting the data row by row. Each row is
then sent to an event handler which then Invokes the main thread to
populate the data grid.

Now, I step through the code, and the first Read off the SqlDataReader
takes about 9 or 10 seconds as indicated above. All the code execution
after that is fine. The grid gets populated.

What I cannot figure out is why it takes 9 - 10 seconds for the first
row to appear when it takes only 1 second in Mgmt Studio.

The code fragment is posted below, in case it is important.

private void FetcherCallback(object userHandle)
        {
            try
            {
                using (SqlConnection connection = new
SqlConnection(m_dataAccess.ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(m_sql,
connection))
                    {
                        command.CommandTimeout =
m_dataAccess.CommandTimeout;
                        using (SqlDataReader reader =
command.ExecuteReader())
                        {

//NOTE: The Read below, for the first time takes 9 seconds or so.
                            while (reader.Read() && m_bExecute)
                            {
                                object[] values = new
object[m_columnCount];
                                reader.GetValues(values);
                                if (null != CustomTransform)
                                {
                                    CustomTransform(userHandle,
values);
                                }
                                if (null != RowFetched)
                                {
                                    RowFetched(userHandle, values);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            if (null != FetchingComplete && m_bExecute) // only if we
were not cancelled
            {
                FetchingComplete(userHandle);
            }
        }

Thanks for all helpful responses in advance.