Are their noticable performance gains by calling SQL from VBA?

[handlebars]

As opposed to using the docmd.openquery method? Are there any other benefits?

Just wondering if it is worth rewriting some action queries in my Access database?

Thanks in advance

Andrew

 

[toryee]

Hi,
I suppose you mean making a call to SQL Server from a VBA application.

Performance gains would mostly depend on the number of records you need to manipulate in your query. The larger the dataset, the more beneficial it is to execute the query in SQL server. I have a 1.6GHz machine and use multiple query statements in stored procedures; the SQL Server's superb performance becomes obvious with tens of thousands of records. So it's all design decision. You need to have a database exist on the SQL Server in order to do query against it.

Tory

 

[TheAceMan1]

[blue]As opposed to using the docmd.openquery method? Are there any other benefits?[/blue]

[purple]Definitely No![/purple]

Queries in the query window are pre-compiled, which affords faster running of the query with the DoCmd. Any SQL in VBA has to be parsed then compiled at runtime, [purple]a slower process[/purple].

Although slower, the advantage of SQL in VBA is that [blue]it can be dynamic[/blue], in a great many ways you can't achieve with Query alone.

See Ya! . . . . . .

 

[willir]

Andrew

If you have a large dataset, then compiled SQL statments will have a noticable performance boost.

For smaller datasets, there is no "practicle" difference.

What does have an impact is your indexes. Query a large table on a field without an index, or on a table with the field names in a different order than your index = very slow.

As the AceMan says, Dynamic queries, quries built on the fly are extremely conveinent vs other ways.

What is not a good way in terms of performance is to use DAO or ADO recordsets when you can use an SQL statement.

Richard