SQL Adapter slow to fill one of three tables

  • Thread starter Thread starter J. Clay
  • Start date Start date
J

J. Clay

I have set up a typed dataset with three tables. On my form using the
dataset, when I fill the tables with data adapters, two of the tables fill
immediately but the third take a very long time. They all use the same
parameter.

Any Suggestions??

TIA,
Jim
 
Addl Info:

SQL Profiler shows that the query completed immediately, but it takes over
30 seconds for the table to be filled code to continue.
 
Hi J. Clay,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that it takes about 30 seconds to fill the
third table in you typed Dataset. If there is any misunderstanding, please
feel free to let me know.

I would like to ask a few more questions here.

1. How many records are there in the 3 tables? Is the last table much
bigger than the other 2?
2. What OS are you working on?
3. How did you create the SqlDataAdapter? Did you create one DataAdapter
and return multiple result sets or did you create 3 DataAdapter and fill
each table seperately? I suggest you create 3 DataAdapters to fill the
DataSet seperately. It would be helpful if you can provide some of your
code.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for you response Kevin. See inline below...Jim

Kevin Yu said:
Hi J. Clay,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that it takes about 30 seconds to fill the
third table in you typed Dataset. If there is any misunderstanding, please
feel free to let me know
Click to expand...

This is correct. The interesting thing is that SQL Profiler shows that the
query is complete instantly.
..
I would like to ask a few more questions here.

1. How many records are there in the 3 tables? Is the last table much
bigger than the other 2?
Click to expand...

Table 1 (Primary table): 40 Records
Table 2: 191 Records
Table 3 (Problem table): 40 records

2. What OS are you working on?
Click to expand...

XP SP1 (pro) - Unable to upgrade to SP2 due to incompatibility issues. That
is the reason for this project.
SQLServer 2000 SP3
3. How did you create the SqlDataAdapter? Did you create one DataAdapter
and return multiple result sets or did you create 3 DataAdapter and fill
each table seperately? I suggest you create 3 DataAdapters to fill the
DataSet seperately.
Click to expand...

3 Seperate DataAdapters

It would be helpful if you can provide some of your
code.
Click to expand...

Private Sub FillContactDataSet()

Try
Me.ContactInfo1.ContactOrganizations.Clear()
Me.ContactInfo1.Address.Clear()
Me.ContactInfo1.ContactDetails.Clear()

Me.dadContactOrganizations.SelectCommand.Parameters("@ContactID").Value =
ContactID
Me.dadContactOrganizations.Fill(ContactInfo1.ContactOrganizations)

Me.dadContactDetails.SelectCommand.Parameters("@ContactID").Value =
ContactID
Me.dadContactDetails.Fill(ContactInfo1.ContactDetails)

Me.dadAddress.SelectCommand.Parameters("@ContactID").Value = ContactID
Me.dadAddress.Fill(ContactInfo1.Address)
'*************The above fill is called, SQL Profile shows that query
returned immediately, but code does not resume for approx 37 seconds. ****

Catch e As System.Exception
MessageBox.Show(String.Format("Error: {0}", e.Message), "LCP CRM",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub
 
New Info:

I had a Dataview defined against this table in the design window. When I
deleted the dataview and removed code related to it, there is no delay. I
then added back the DV and related code:
dvAddress.RowFilter = ""
dvAddress.RowFilter = "ContactID = " & ContactID.
All ran fine.

The problem came back when I added the binding of the DV to a combo box on
my form in the designer.

Why would binding a DV of the DataSet cause an issue?

Thanks,
Jim
 
Hi J. Clay,

Thanks for providing me with the new information. It is very important.
Based on my research, this is a known issue that if a combobox is bound to
a DataView with RowFilter set, the performance is poor. This issue will be
fixed in the next version of .NET framework. However, currently, we have a
workaround like the following:

1. Set the DataSource of ComboBox to null before filling data.
2. Set the DataSource back to DataView.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
You're welcome, Jim.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top