Problem with sqlDataAdapter.fill performance

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
Thu Feb 17 05:59:09 CST 2005

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" <d.piccinini@nospam.com> wrote in message
news:elkxvdNFFHA.3732@TK2MSFTNGP14.phx.gbl...
> 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 -
>
>

Daniele
Fri Feb 18 01:54:52 CST 2005

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 -

"John Smith" <fakeemail@hotmail.com> ha scritto nel messaggio
news:u%23yIF$NFFHA.624@TK2MSFTNGP15.phx.gbl...
> 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" <d.piccinini@nospam.com> wrote in message
> news:elkxvdNFFHA.3732@TK2MSFTNGP14.phx.gbl...
>> 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 -
>>
>>
>
>