Problem with Long Stored Proc.

[Jenny]

Hi

I have a C# (windows app) program where I call several stored procs. However there stored proc are very long (total of about 15-20 minutes)

My Problem is that when a execute the program, it kind of stalls, and its status is NOT RESPONDING. But it seems to execute the Store Proc OK. But we don't see the regular user interface anymore, and it looks like if the program has crashed... Any idea what I can do to smoothen the user experience

(I have put the CommandTimeout to a large number, and I have sure that it is not a connection or privileges problem.

thanks

Jenny

[Cowboy \(Gregory A. Beamer\)]

Suggestions:

1. See what you can do to improve query performance, including
pre-aggregation of the data during off times (ad hoc will not work here, but
common reports will). Denormalization and indexes are other options.

2. Determine if the data can be moved to a data warehouse for the types of
queries, as data warehouses have great value if the data is not OLTP.

3. If neither of the above work, consider throwing the data retrieval to
another thread and creating an event when the data is returned. If you store
the data in a DataSet, the user can choose when to get back to the query.
When you spawn the thread, tell the user it will take some time and they
should do something else. You will inform them when their report is
returned.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Jenny" <an*******@discussions.microsoft.com> wrote in message
news:60**********************************@microsof t.com...

Hi,

I have a C# (windows app) program where I call several stored procs. However there stored proc are very long (total of about 15-20 minutes).
My Problem is that when a execute the program, it kind of stalls, and its status is NOT RESPONDING. But it seems to execute the Store Proc OK. But we
don't see the regular user interface anymore, and it looks like if the
program has crashed... Any idea what I can do to smoothen the user
experience?
(I have put the CommandTimeout to a large number, and I have sure that it is not a connection or privileges problem.)
thanks!

Jenny

[Peter Rilling]

Sounds like a place where threads might be useful. When your UI needs to
execute the stored procedures, it can spawn a new thread and all SP work
will be done in that new thread. That way control can return to your main
application loop.

"Jenny" <an*******@discussions.microsoft.com> wrote in message
news:60**********************************@microsof t.com...

Hi,

I have a C# (windows app) program where I call several stored procs. However there stored proc are very long (total of about 15-20 minutes).
My Problem is that when a execute the program, it kind of stalls, and its status is NOT RESPONDING. But it seems to execute the Store Proc OK. But we
don't see the regular user interface anymore, and it looks like if the
program has crashed... Any idea what I can do to smoothen the user
experience?
(I have put the CommandTimeout to a large number, and I have sure that it is not a connection or privileges problem.)
thanks!

Jenny

[Eric Johannsen]

As the others already said - put it in a worker thread.

Here's a great article on how to do that:

http://msdn.microsoft.com/vbasic/usi...ult.aspx?pull=
/library/en-us/dnadvnet/html/vbnet09272002.asp

Eric

"Jenny" <an*******@discussions.microsoft.com> wrote in message
news:60**********************************@microsof t.com...

Hi,

I have a C# (windows app) program where I call several stored procs. However there stored proc are very long (total of about 15-20 minutes).
My Problem is that when a execute the program, it kind of stalls, and its status is NOT RESPONDING. But it seems to execute the Store Proc OK. But we
don't see the regular user interface anymore, and it looks like if the
program has crashed... Any idea what I can do to smoothen the user
experience?
(I have put the CommandTimeout to a large number, and I have sure that it is not a connection or privileges problem.)
thanks!

Jenny