Asynchronous Delegate Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the application I am working on, the user makes a request of information
from the database. As there is a great deal of information to go through and
possibly return, to speed things up, the request for data only returns the
first 25 rows of data. So thas the user knows how many total rows of data
there are, a count is returned by another stored procedure. This query can
take a while to complete so I have created a delete called ProductCount that
I call asynchronously. The problem comes when the user changes their mind on
which item's information he wants to view. I need to cancel the previous call
to ProductCount and initiate the new call to ProductCount. The code looks
something like this:

public delegate void CountDelegate (string[] params);
private CountDelegate cDeleg;

public GetInfo ()
{
DataSet ds = GetProductResults (string params, int startIndex, int
numRowsReturn);

//not another page of current query results so count does not need to be
//computed again
if (newQuery)
{
cDeleg = new CountDelegate (ProductCount);
AsyncCallback cb = new AsyncCallback (CountCallback);
cDeleg.BeginInvoke(params, cb, "CountDelegate");
}
}

public void ProductCount (string[] params)
{
int records = GetProductCount (params); //call to stored procedure

//do some stuff with the record count
}

private void CountCallback (IAsyncResult ar)
{
cDeleg.EndInvoke (ar);
}

As it is the stored procedure part that takes the time to run and not the
code, how to I gracefully cancel so I can start the new query? I have tried
adding a parameter in ProductCount to check to see if the ending thread is
the same as the current thread number, but there is a race condition so the
index has changed in the middle of that function. I got the idea of the index
from the boolean examples I saw. I do not want to hold up the user interface
waiting for the long process to come back before starting the next get
product query. We are using Oracle so I do invoke the OracleCommand.Cancel,
but that can take a while as well. We are really trying to decrease response
time and the count is a major eater of time. Guestimating the count is not
usable because of the table structure so the count as been off by an order of
2. Any ideas?

Thank you,
Susan
 
I prefer not to just leave the process running on the database since
resources can be valuable.
 
You really don't have another option except for spinning off another
thread while waiting for the current one to complete/cancel. How much
tuning have you done for the query? If you want to post an explain plan
and statistics for the query in question we might be able to suggest
improvements there.
 
The database has over 30 million products in it so you can tune only so much.
We have a lot of security restrictions in the query. Our DBA (sjipped in from
Oracle) has optimized it the best he can. He was hoping to get the estimated
count to work, but it was off too much. I could always turn it from a
delegate to a thread, and then send the cancel command to Oracle before
aborting teh thread. I was just hoping for a more graceful end. :)

Thank you,
Susan
 
30 million records isn't a very large amount but if you trust that your
DBA has properly tuned/organized the database then there's no reason to
investigate further. How was he going about retrieving the "estimated"
count? Was he checking the cardinality from the explain plan?
Don't abort the thread, just let it finish gracefully in the
background while you run the new/next query from a different thread.
 
Seems to be a complex model, prone with potential issue. As you call count
anyway, it seems to me you should return count in the stream with the first
25 records. Then they know how many and you don't need another round trip to
the server, or worry about canceling the first call.

--
William Stacey [MVP]

| In the application I am working on, the user makes a request of
information
| from the database. As there is a great deal of information to go through
and
| possibly return, to speed things up, the request for data only returns the
| first 25 rows of data. So thas the user knows how many total rows of data
| there are, a count is returned by another stored procedure. This query can
| take a while to complete so I have created a delete called ProductCount
that
| I call asynchronously. The problem comes when the user changes their mind
on
| which item's information he wants to view. I need to cancel the previous
call
| to ProductCount and initiate the new call to ProductCount. The code looks
| something like this:
|
| public delegate void CountDelegate (string[] params);
| private CountDelegate cDeleg;
|
| public GetInfo ()
| {
| DataSet ds = GetProductResults (string params, int startIndex, int
| numRowsReturn);
|
| //not another page of current query results so count does not need to be
| //computed again
| if (newQuery)
| {
| cDeleg = new CountDelegate (ProductCount);
| AsyncCallback cb = new AsyncCallback (CountCallback);
| cDeleg.BeginInvoke(params, cb, "CountDelegate");
| }
| }
|
| public void ProductCount (string[] params)
| {
| int records = GetProductCount (params); //call to stored procedure
|
| //do some stuff with the record count
| }
|
| private void CountCallback (IAsyncResult ar)
| {
| cDeleg.EndInvoke (ar);
| }
|
| As it is the stored procedure part that takes the time to run and not the
| code, how to I gracefully cancel so I can start the new query? I have
tried
| adding a parameter in ProductCount to check to see if the ending thread is
| the same as the current thread number, but there is a race condition so
the
| index has changed in the middle of that function. I got the idea of the
index
| from the boolean examples I saw. I do not want to hold up the user
interface
| waiting for the long process to come back before starting the next get
| product query. We are using Oracle so I do invoke the
OracleCommand.Cancel,
| but that can take a while as well. We are really trying to decrease
response
| time and the count is a major eater of time. Guestimating the count is not
| usable because of the table structure so the count as been off by an order
of
| 2. Any ideas?
|
| Thank you,
| Susan
 
The count dpeends on a lot of factors (access to the record, filters he may
have on, etc.) so that would not be realistic. Otherwise, that would be a lot
easier. :)

Thanks,
Susan
 
Back
Top