Long-Running DataSet.Fill Ties-Up System

D

David Pyper

Hi,

Here's my situation. I developed a Microsoft Access query that polls
a remote Intersystems Caché system where all my data resides. Access
has a few features I prefer to use, in particular some agregate
queries I'd find difficult to replicate on my Caché system. Here's an
example of one query:

PARAMETERS [@qpar_Start_Date] DateTime, [@qpar_End_Date] DateTime;
SELECT SYSTEM_R001_Summary_Data_LIV.Buyer_ID,
Count(SYSTEM_R001_Summary_Data_LIV.Buyer_ID) AS [Count]
FROM SYSTEM_R002_Line_Detail_LIV INNER JOIN
SYSTEM_R001_Summary_Data_LIV ON
SYSTEM_R002_Line_Detail_LIV.Line_KeyTo_R001_Summary_Data =
SYSTEM_R001_Summary_Data_LIV.Summary_Data_R001_Key
WHERE (((SYSTEM_R001_Summary_Data_LIV.PO_Date)>=[@qpar_Start_Date] And
(SYSTEM_R001_Summary_Data_LIV.PO_Date)<=[@qpar_End_Date]) AND
((SYSTEM_R002_Line_Detail_LIV.Item_Number) Is Not Null))
GROUP BY SYSTEM_R001_Summary_Data_LIV.Buyer_ID;

I use this query as the datasource for another query. Anyhow, suffice
to say that I'm heavily invested in MS-Access for performing local
queries and I'm not likely to change that anytime soon.

These Access-based queries are bound to some ASPX files which users
interface with through our intranet website. In this particular case,
the parameters in the query (above) are filled based on data that
comes from the web site. All this works very well, with one
exception:

The user submits the form that executes the code-behind. This starts
a process whereby the query is run by way of OleDbDataAdapter. A
DataSet is filled, and then further things occur depending on the
result of the data. The problem is, when the user launches the query,
every other OleDbDataAdapter call stalls until the first one finishes.
Another problem that I believe is related is that I can't launch more
than one query at a time.

Here's a simplified bit of code that illustrates the problem. For the
sake of the example, this method would be called by a Button_Click
event:

Private Sub POsByBuyerUncat()
Dim oDs As New System.Data.DataSet
Try
With oDaPOsByBuyerUncat
.SelectCommand.Parameters("[@qpar_Start_Date]").Value = _
Convert.ToDateTime(tbStartDate.Text)
.SelectCommand.Parameters("[@qpar_End_Date]").Value = _
Convert.ToDateTime(tbEndDate.Text)
.Fill(oDs, "POsByBuyerUncat")
End With
Catch ex As Exception
lblRuntimeError.Text = ex.ToString
Finally
If Not oDaPOsByBuyerUncat Is Nothing Then
oDaPOsByBuyerUncat.Dispose()
oDaPOsByBuyerUncat = Nothing
End If
If Not oDs Is Nothing Then
oDs.Dispose()
oDs = Nothing
End If
End Try
End Sub

The problem occurs when the .Fill executes. No other .Fill statements
can run on the same IIS machine while the .Fill is running. That
includes OleDb calls in other IIS applications, in other .MDB files,
and so forth.

My remote Caché system is still running fine, and will gladly run all
kinds of other jobs so I don't think it's responsible. I have toyed
with ODBC Pooling, multithreading, file permissions, and many other
combinations of factors but I can't figure it out.

Has anyone ever seen this? And if so, how do you fix it?

Please reply, either to this newsgroup or by e-mail at
David.PyperDontSpamMeMUHC.McGill.CA.

David
 
M

Miha Markic

Hi David,

How do you manage connection object? Is it global?
How do you open access database - it shouldn't be open in exclusive mode
either.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

David Pyper said:
Hi,

Here's my situation. I developed a Microsoft Access query that polls
a remote Intersystems Caché system where all my data resides. Access
has a few features I prefer to use, in particular some agregate
queries I'd find difficult to replicate on my Caché system. Here's an
example of one query:

PARAMETERS [@qpar_Start_Date] DateTime, [@qpar_End_Date] DateTime;
SELECT SYSTEM_R001_Summary_Data_LIV.Buyer_ID,
Count(SYSTEM_R001_Summary_Data_LIV.Buyer_ID) AS [Count]
FROM SYSTEM_R002_Line_Detail_LIV INNER JOIN
SYSTEM_R001_Summary_Data_LIV ON
SYSTEM_R002_Line_Detail_LIV.Line_KeyTo_R001_Summary_Data =
SYSTEM_R001_Summary_Data_LIV.Summary_Data_R001_Key
WHERE (((SYSTEM_R001_Summary_Data_LIV.PO_Date)>=[@qpar_Start_Date] And
(SYSTEM_R001_Summary_Data_LIV.PO_Date)<=[@qpar_End_Date]) AND
((SYSTEM_R002_Line_Detail_LIV.Item_Number) Is Not Null))
GROUP BY SYSTEM_R001_Summary_Data_LIV.Buyer_ID;

I use this query as the datasource for another query. Anyhow, suffice
to say that I'm heavily invested in MS-Access for performing local
queries and I'm not likely to change that anytime soon.

These Access-based queries are bound to some ASPX files which users
interface with through our intranet website. In this particular case,
the parameters in the query (above) are filled based on data that
comes from the web site. All this works very well, with one
exception:

The user submits the form that executes the code-behind. This starts
a process whereby the query is run by way of OleDbDataAdapter. A
DataSet is filled, and then further things occur depending on the
result of the data. The problem is, when the user launches the query,
every other OleDbDataAdapter call stalls until the first one finishes.
Another problem that I believe is related is that I can't launch more
than one query at a time.

Here's a simplified bit of code that illustrates the problem. For the
sake of the example, this method would be called by a Button_Click
event:

Private Sub POsByBuyerUncat()
Dim oDs As New System.Data.DataSet
Try
With oDaPOsByBuyerUncat
.SelectCommand.Parameters("[@qpar_Start_Date]").Value = _
Convert.ToDateTime(tbStartDate.Text)
.SelectCommand.Parameters("[@qpar_End_Date]").Value = _
Convert.ToDateTime(tbEndDate.Text)
.Fill(oDs, "POsByBuyerUncat")
End With
Catch ex As Exception
lblRuntimeError.Text = ex.ToString
Finally
If Not oDaPOsByBuyerUncat Is Nothing Then
oDaPOsByBuyerUncat.Dispose()
oDaPOsByBuyerUncat = Nothing
End If
If Not oDs Is Nothing Then
oDs.Dispose()
oDs = Nothing
End If
End Try
End Sub

The problem occurs when the .Fill executes. No other .Fill statements
can run on the same IIS machine while the .Fill is running. That
includes OleDb calls in other IIS applications, in other .MDB files,
and so forth.

My remote Caché system is still running fine, and will gladly run all
kinds of other jobs so I don't think it's responsible. I have toyed
with ODBC Pooling, multithreading, file permissions, and many other
combinations of factors but I can't figure it out.

Has anyone ever seen this? And if so, how do you fix it?

Please reply, either to this newsgroup or by e-mail at
David.PyperDontSpamMeMUHC.McGill.CA.

David
Click to expand...
 
D

David Pyper

Hi Miha,

My connection object is indeed global. By this I mean that the
connection to the .MDB file is accessible through the Server Explorer.

The access permissions are set to Share Deny None. I have also tried
Read, and Read Write but without any change in behaviour.

Any ideas?

David

Miha Markic said:
Hi David,

How do you manage connection object? Is it global?
How do you open access database - it shouldn't be open in exclusive mode
either.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

David Pyper said:
Hi,

Here's my situation. I developed a Microsoft Access query that polls
a remote Intersystems Caché system where all my data resides. Access
has a few features I prefer to use, in particular some agregate
queries I'd find difficult to replicate on my Caché system. Here's an
example of one query:

PARAMETERS [@qpar_Start_Date] DateTime, [@qpar_End_Date] DateTime;
SELECT SYSTEM_R001_Summary_Data_LIV.Buyer_ID,
Count(SYSTEM_R001_Summary_Data_LIV.Buyer_ID) AS [Count]
FROM SYSTEM_R002_Line_Detail_LIV INNER JOIN
SYSTEM_R001_Summary_Data_LIV ON
SYSTEM_R002_Line_Detail_LIV.Line_KeyTo_R001_Summary_Data =
SYSTEM_R001_Summary_Data_LIV.Summary_Data_R001_Key
WHERE (((SYSTEM_R001_Summary_Data_LIV.PO_Date)>=[@qpar_Start_Date] And
(SYSTEM_R001_Summary_Data_LIV.PO_Date)<=[@qpar_End_Date]) AND
((SYSTEM_R002_Line_Detail_LIV.Item_Number) Is Not Null))
GROUP BY SYSTEM_R001_Summary_Data_LIV.Buyer_ID;

I use this query as the datasource for another query. Anyhow, suffice
to say that I'm heavily invested in MS-Access for performing local
queries and I'm not likely to change that anytime soon.

These Access-based queries are bound to some ASPX files which users
interface with through our intranet website. In this particular case,
the parameters in the query (above) are filled based on data that
comes from the web site. All this works very well, with one
exception:

The user submits the form that executes the code-behind. This starts
a process whereby the query is run by way of OleDbDataAdapter. A
DataSet is filled, and then further things occur depending on the
result of the data. The problem is, when the user launches the query,
every other OleDbDataAdapter call stalls until the first one finishes.
Another problem that I believe is related is that I can't launch more
than one query at a time.

Here's a simplified bit of code that illustrates the problem. For the
sake of the example, this method would be called by a Button_Click
event:

Private Sub POsByBuyerUncat()
Dim oDs As New System.Data.DataSet
Try
With oDaPOsByBuyerUncat
.SelectCommand.Parameters("[@qpar_Start_Date]").Value = _
Convert.ToDateTime(tbStartDate.Text)
.SelectCommand.Parameters("[@qpar_End_Date]").Value = _
Convert.ToDateTime(tbEndDate.Text)
.Fill(oDs, "POsByBuyerUncat")
End With
Catch ex As Exception
lblRuntimeError.Text = ex.ToString
Finally
If Not oDaPOsByBuyerUncat Is Nothing Then
oDaPOsByBuyerUncat.Dispose()
oDaPOsByBuyerUncat = Nothing
End If
If Not oDs Is Nothing Then
oDs.Dispose()
oDs = Nothing
End If
End Try
End Sub

The problem occurs when the .Fill executes. No other .Fill statements
can run on the same IIS machine while the .Fill is running. That
includes OleDb calls in other IIS applications, in other .MDB files,
and so forth.

My remote Caché system is still running fine, and will gladly run all
kinds of other jobs so I don't think it's responsible. I have toyed
with ODBC Pooling, multithreading, file permissions, and many other
combinations of factors but I can't figure it out.

Has anyone ever seen this? And if so, how do you fix it?

Please reply, either to this newsgroup or by e-mail at
David.PyperDontSpamMeMUHC.McGill.CA.

David
Click to expand...
Click to expand...
 
Top