在执行大型存储过程时在SQL中运行查询

时间:2022-09-20 23:04:46

I have a situation where I need to run a stored procedure (>5mins) and want my (silverlight) application to remain 'alive'

我有一种情况需要运行存储过程(> 5分钟)并希望我的(silverlight)应用程序保持“活着”

This I do with an async WCF Service for my UI thread to be responsive, at least until I request some data from the same DB again

我使用异步WCF服务为我的UI线程做出响应,至少在我再次从同一个DB请求一些数据之前

Then, only after SP is finished the other request are executed

然后,只有在SP完成后才执行其他请求

Can I somehow (in SQL I suppose) set the storedprocedure to be able to run alongside with other queries?

我可以以某种方式(在SQL中假设)将storedprocedure设置为能够与其他查询一起运行吗?

Thanks

edit:

  1. DB is on SQL Server
  2. DB在SQL Server上

  3. starting SP from Linq2Sql model in [OperationContract(AsyncPattern = true)] service method
  4. 在[OperationContract(AsyncPattern = true)]服务方法中从Linq2Sql模型启动SP

  5. other data is handled with WCF Ria Services (DomainService from Linq2SQL model, same connection string)
  6. 其他数据由WCF Ria Services处理(来自Linq2SQL模型的DomainService,相同的连接字符串)

edit 2:

@dan1111: With testing I saw that until the point when WCF service returns result to Silverlight, DomainContext 'freezes'

@ dan1111:通过测试我看到,直到WCF服务将结果返回给Silverlight,DomainContext'冻结'

Initially:

  1. I call WCF Async method with Thread.Sleep(15000)
  2. 我用Thread.Sleep(15000)调用WCF异步方法

  3. Application continues to work good
  4. 应用程序继续运行良好

  5. When I go to a page where data is needed, I request DataContext.Load()
  6. 当我转到需要数据的页面时,我请求DataContext.Load()

  7. DataContext.IsLoading becomes true
  8. DataContext.IsLoading变为true

  9. I must wait the remaining of the 15seconds
  10. 我必须等待15秒的剩余时间

  11. Now the completed event is fired for the previous DataContext.Load
  12. 现在,为之前的DataContext.Load触发了已完成的事件

Please tell me your opinion

请告诉我你的意见

1 个解决方案

#1


3  

A very simple solution is to open more than one connection to the database. Each connection can do its own stuff.

一个非常简单的解决方案是打开多个数据库连接。每个连接都可以做自己的事情。

However, there is another way to do it that actually sets up a procedure to run asynchronously:

但是,有另一种方法可以实际设置一个异步运行的过程:

http://rusanu.com/2009/08/05/asynchronous-procedure-execution/

#1


3  

A very simple solution is to open more than one connection to the database. Each connection can do its own stuff.

一个非常简单的解决方案是打开多个数据库连接。每个连接都可以做自己的事情。

However, there is another way to do it that actually sets up a procedure to run asynchronously:

但是,有另一种方法可以实际设置一个异步运行的过程:

http://rusanu.com/2009/08/05/asynchronous-procedure-execution/