Re: Query too long

Clay Fox — Thu, 10 Jul 2008 04:00:27 GMT

I would say with that amount of data you should switch to SQL Express and use a web service. Then you can query for a specific value and only return those values.

That would be the way to go if you want this to perform acceptably.

Query too long

Franc1 — Wed, 09 Jul 2008 10:10:10 GMT

Hello,

I have a repeating table(x rows) which query an Access database(~400MB, 3 tables each table 500 000 lines).

Well the problem is that when i try to query the database for 5 records, it needs like 40 min just to display "the data is exist" or "the data does not exist"(:s and me i hoped that i could check for like 50 records using the same form :-D)

This is the code:

Public Sub CTRL11_7_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)

Dim domNav As XPathNavigator = MainDataSource.CreateNavigator()

Dim rows1 As XPathNodeIterator = domNav.Select( _

"/dfs:myFields/my:group1/my:group2", NamespaceManager)

While rows1.MoveNext()

Dim p As Integer

p = 1

Dim field1 As String = rows1.Current.SelectSingleNode( _

"my:field1", NamespaceManager).Value

'set the query fields

Dim xpath1 As String = "/dfs:myFields/dfs:queryFields/q:MARA/@MATNR"

Dim matnr As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath1, NamespaceManager)

matnr.SetValue(field1)

Dim field2 As String = rows1.Current.SelectSingleNode( _

"my:field2", NamespaceManager).Value

Dim xpath2 As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@VKORG"

Dim vkorg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath2, NamespaceManager)

Dim field3 As String = rows1.Current.SelectSingleNode( _

"my:field3", NamespaceManager).Value

Dim xpath3 As String = "/dfs:myFields/dfs:queryFields/q:MVKE/@VTWEG"

Dim vtweg As XPathNavigator = MainDataSource.CreateNavigator().SelectSingleNode(xpath3, NamespaceManager)

MainDataSource.QueryConnection.Execute()

Dim XPathMV As String = "/dfs:myFields/dfs:dataFields/d:MARA/d:MVKE"

Dim mvke As XPathNodeIterator = MainDataSource.CreateNavigator().Select(XPathMV, NamespaceManager)

Dim XPathMA As String = "/dfs:myFields/dfs:dataFields/d:MARA"

Dim mara As XPathNodeIterator = MainDataSource.CreateNavigator().Select(XPathMA, NamespaceManager)

Dim countMA As Integer = mara.Count

If countMA = 0 Then

status("Data does not exist MARA")

Else

'status is a function that add a field in a another repeating table with the query status

status("Data exist in MARA")

xpath1 = "/dfs:myFields/dfs:queryFields/q:MVKE/@MATNR"

matnr.SetValue(field1)

vkorg.SetValue(field2)

vtweg.SetValue(field3)

MainDataSource.QueryConnection.Execute()

Dim countMV As Integer = mvke.Count

If countMV = 0 Then

status("Data does not exist MVKE")

Else

status("Data exist in MVKE")

End If

End While

End Sub

Any help would be appreciated . Thank you

P.S I'm using infopath 2007 and Access database

Data Connections

Re: Query too long

Query too long