ADO too slow, any alternatives?

I have UDF's which can be placed thousands of times in large spreadsheets. Internally, the connect to our SQL Server DB - typically, they call custom SQL functions which do some calculations and return a result, which in turn return the value to the current cell. Each function takes about 1/10 of a second to execute. Which seems fast, but multiply it over thousands of cells and you are in trouble.

My client lives in fear that anything he touches will trigger a workbook recalculation, and he can never predict when that will happen.

I know the SQL functions run very quickly, and running equivalent queries from a SQL client on the same machine over thousands of rows returns near-instantaneous results. My research so far into ADO via Excel VBA is that it's just plain horribly slow.

I have done everything I can think of to optimize things from the VBA side (for example, persistent, cached connections). Has anyone solved a similar problem?

Thanks,

Carlo

Have you investigated the volatile method to stop your functions re-calculating unless they need to?

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Thanks very much, Z. That's very handy. I'd never seen that before in all the forums I've browsed, and yet people constantly complain about needless recalculation!

I will try this, but in the end, the real problem is the ADO performance. For example, the SQL functions MIGHT return different results as the DB content changes, so the recalculation behaviour does make sense. It's just crippling and I would still like to know if there are any alternatives. If I created an XLL, placed my functions in there and accessed ADO through that, would it be faster, or is there something about the combination of ADO and Excel that will slow things down no matter what?

Without knowing much about your workbook it *sounds* like it is the sheer volume of calls to the database that is the issue. Are you able to reduce the number of calls to the db by grabbing a dataset and making your UDF query the dataset rather than repeatedly connecting and querying the db? I don't think changing the architecture is going to make much difference if vba is going to create an ADO object, connect to the database, authenticate, query, return the recordset and gracefully disconnect before repeating for the next cell and then again and again, several thousand times. If each re-calc kicks off a query to the db that changes a value in the workbook you could end up in a really bad spiral of recalcs and queries very quickly.

Getting all the data you need in one fell swoop, maybe into a temporary worksheet if needs be (although it should/could be queried in memory), and then clearing the worksheet can help. Your UDF would then be working on local data, no remote query, using inbuilt functions, it should zip along.

The idea is to create functions that users can easily place in any cell with whatever parameters they want, so we don't have a situation where we can read ahead and do multiple calculations in one swoop. I am not designing the spreadsheet for the users, I am designing the functions to give them the data they need when they need it. We considered local caching, but since that introduces the same problems as setting function volatility, I would prefer to go with that as it is cleaner and easier to maintain.

We don't repeatedly connect, BTW. And in fact everything but the recordset is reused (this might be dangerous, but so far it has been working).

Here is our function:

Please Login or Register  to view this content.

(The ConnectionOpen() function calls a connection manager that creates a new ADODB.connection if one doesn't exist and then holds it open in a similar manner that SqlGetString creates and keeps its own ADODB.Command).

Using debug.print, we know that the nearly entirety of the time spent rendering the result to the cell is spent in Set rs = DB_SqlGetString_Command.Execute. The command being called is readonly, and the SqlGetString function's purpose is clear. Can any of the objects in use have any parameters set to optimize for this limited type of use?