Re: UPDATE SQL for Excel - very slow - any suggestions?


"Martin B" <mb@xxxxxx> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:43d4d212$1@xxxxxxxxxxxxxxxxxxxxxxxxx
> Hello world,
>
> I am using ADO components to connect to a datasource; the user can
> connect to Excel sheets, Access or any other database.
>
> After using the program, the user will want to update certain fields in
> the datasource.
> I have implemented this with standard "UPDATE" SQL queries.
> This works fine with Access. But with Excel sheets it is very slow (factor
> 60).
> This is probably due to the fact that there is no index in the key fields
> in Excel.
>
> Any suggestions?
>
> Regards,
> Martin

AFAIK TADODataSet with CommandType = cmdTableDirect and CursorLocation =
clUseServer is fastest for append data in Excel with ADO, so you can try
this combination for Update. Also I think that yours guess about
inefficiency of using where clause for un-indexed data could be quite valid,
so you can switch to referencing of target cells by there Excel coordinate
instead. Please let us know on yours results if any.



Regards,

Vitali


.

Quantcast