Treeview performance slow in MS Access after upsizing to SQL

[peterzeke]

Frustrating situation I'm facing.

I've upsized my data tables from within Access to SQL. The data is queried
instantly from within VBA, so I have no complaints about establishing a
recordset.

The problem I'm encountering, however, is that a treeview I have on a form
now populates quite slowly. I haven't changed the VBA code since upsizing to
SQL except for needing to specify "dbSeeChanges" when acquiring the data.
Nothing else in the VBA code has been changed.

Any thoughts on why I'm experiencing such a huge performance hit when
populating the treeview? Before I upsized, the treeview populated in a mere
second; now it seems to take at least 30 seconds.

Any help will be greatly appreciated.

--Pete

 

[Sylvain Lafontaine]

The most likely explanation is the use of bad query plans. This first thing
to do would be to update the statistics using the sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and clean the caches after that:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You should always make these steps (especially updating the statistics)
after upsizing a database. Reindexing everything could also be a good idea.
If this still doesn't work after that, then you should think about creating
and using Views or passthrough queries or to use ADO objects to call stored
procedures on the server.

In the case of SP, you must take care of not having the SP compiled with a
bad query plan because of the use of atypical parameters on the first call;
so you may have to use the WITH RECOMPILE option or to use intermediate
variables in order to deactivate the parameter sniffing procedure from
SQL-Server. (The use of
intermediary variables is probably better than to use the With Recompile
option because recompiling big procedures take time. Also, you don't have
to use both.). Here are some references on these topics:

http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41

http://blog.sqlauthority.com/2007/0...abase-tables-and-update-statistics-on-tables/

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx


If you are with SQL-Server 2005, you can also play with the OPTIMIZE FOR
option instead of deactivating the parameters sniffing or using the With
Recompile option:

http://www.sql-server-performance.com/articles/per/using_plan_guides_p1.aspx

and:
http://www.sql-server-performance.com/articles/per/new_query_hints_p1.aspx

 

[peterzeke]

Sylvain:

Thanks for the detailed reply. I'll certainly look over all that you suggest.

In the meantime, I may have solved the issue... I decided to make a change
in retrieving the data from SQL by having VBA create a passthrough querydef
that includes a parameter value to feed into a stored proc. (A user uses a
combo box to make a selection which is then fed to VBA to create the
passthrough query.)

The above feature didn't make the data return any faster (since data
retrieval was always flowing welL), but I felt it couldn't hurt to leverage
SQL server where possible.

So, now that I was using a passthrough query for data retrieval (called
qry_COND_ID), I felt I could change the OpenRecordset statement from
dbSeeChanges to dbReadOnly since VBA was no longer querying the sql data
directly. See below:

strSQL = "Select CONDITION_ID, ITEM_ID, SUP_ID, REFID_DESC, SORT_ID, TAG
From qry_COND_ID"
Set Db = CurrentDb
Set rst = Db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

Changing to dbReadOnly appears to be the real fix! The treeview now
populates instantly, refreshes instantly.

Nevertheless, I will still review the materials you've suggested as I very
much wish to understand all the issues that can come into play regarding
performance.

Thanks again!
--Pete

 

[Pieter Wijnen]

General advice.
Don't use DbOpenDynaset when you only intend to read data, use
DbOpenSnapshot instead

Pieter

 

[Stefan Hoffmann]

hi Pete,

Just upsizing doesn't improve performance. Indeed in a lot of
circumstances you will see decreasing it.

I've upsized my data tables from within Access to SQL. The data is queried
instantly from within VBA, so I have no complaints about establishing a
recordset.

Are you filling it recursivly?

Any thoughts on why I'm experiencing such a huge performance hit when
populating the treeview? Before I upsized, the treeview populated in a mere
second; now it seems to take at least 30 seconds.

How do you populate it? Can you show us some code?


mfG
--> stefan <--

 

[peterzeke]

Pieter:

Thanks - I'll make a note of using DbOpenSnapshot.

I wish I knew so much more about the nuances of VB/VBA. I'm actually a SQL
analyst, so I can crank out data from SQL server with a lot of
sophistication. But, once I start integrating a front end like Access, I
begin to run into a variety of issues where the solution often seems elusive.

Thanks again for your advice.
--Pete

 

[Alex Dybenko]

Or even dbOpenForwardOnly - this will allow to only move next, but this is
ok to fill a treeview

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com