Problem with sqlDataAdapter.fill performance

[Daniele Piccinini]

Hi
I am a newbie to the ADO .NET. I use a dataset as datasource for a web
Crystal Report. I fill this dataset using a sqlDataAdapter. The
selectCommand is quite complex and use many table and relations. The
execution of the fill command is very long (it takes about 30 sec.), but if
i run the select command in the query builder it takes about 2 sec, so i
think that the problem is not the execution of the query by SQLServer. How
can i speeed up the fill of this dataset ?

Thank you

- Daniele -

 

[John Smith]

Try the following and tell us about the results:

Call BeginLoadData before the Fill and EndLoadData after the Fill.

MyDataTable.BeginLoadData
MySqlDataAdapter.Fill(MyDataSet, "tbMyTable") ' or fill the datatable
directly with ...Fill(dtMyDataTable)
MyDataTable.EndLoadData

There is another tip. You can open and close your connection before the
beginloaddata and after endloaddata explicitly. This can avoid the overhead
involved in checking if connection is open by the fill method.

Let us know of the results.

 

[Daniele Piccinini]

Thanks John. I've tried both the tip but the fill command is still very
slow.
I've even try to use a SqlDataReader, but the ExecuteReader command is only
a bit faster.

Additional info:
- The select command returns 25 records, so i don't think the dalay is
caused by the filling of the destination table.
- The select command is a groupby that involves 8 table ( containing a lot
of records), 10 join and has 3 parameters.

Now i try to use a stored procedure and tell you about the result...

Thank you

- Daniele -