Global recordset?

[shaunhubbs]

Hi all,

I have a visual basic form that is uses a treeview and every time a node in the tree is clicked the information for the selected item load in the right pane. The problem I am having is that the information seems to be loading fairly slowly because it runs the same SQL select statement involving linked tables each time a node is clicked.

Does anyone have any suggestions for speeding this up? Changing the table structure is not an option as the small application I am develping works along side an existing application that defines that table structures.

Thanks in advance.

- Shaun

 

[BobRodes]

A couple of things you could try:

I'm assuming that you're using Access for the linked tables. What are they linked to? Can you access the tables directly? That should remove some overhead.

You could also pull all the data to a local recordset when you load your program, and use Filters and/or Finds to get your data. Depending on the size of the tables, this could run faster. If you choose this method, you can speed up data access by indexing the recordset using

myRs.Fields([field number]).Properties("Optimize") = True

HTH

Bob

 

[shaunhubbs]

Thanks for the response Bob. I am using Access, but an ADP file so there is no linking to the tables. Your response seems feasible, but as you mentioned "depending on the size of the tables". Anyway, the tables will be very large in the end so I may consider using filter/finds, but I believe that the issue I was having involved a "group by" in the SQL select statement that I was using that was slowing it down. For now everything seems to be working fine.

Thanks.

- Shaun

 

[BobRodes]

<the issue I was having involved a "group by" in the SQL select statement that I was using that was slowing it down.

That's worth knowing. Thanks for sharing.

Bob

 

[vbSun]

if you have a group by and a where clause together, try splitting it.. use the where clause first, grab the required records and then issue the group by.. that will improve performance.. may be you can save the results of the where clause on to a temp table inside access ("select .. in to.. table name" statement) before you issue the group by on the temp table.

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[shaunhubbs]

Thanks for that response as well vbSun. That makes perfect sense and I will keep that in mind in case I do need both the WHERE and GROUP BY in the future.

- Shaun