Improving performance

[liddlem]

Hi all
I currently have an access Front-End with a SQL Back-end.

I recently read that when queries (which reside in the front-end) are run, that ALL the data in the linked tables is sent from the server to the client before any filtering, sort etc is applied.
The client then does the filtering and sorting.

Clearly, this implies that a lot of 'unnecessary' data is being transported across the network.

In order to improve performance I tried to create a view on SQL server side and made a link to the view.

However - It turns out that
A. The view does not refresh when the underlying data is changed.
B. After I refresh the view, I have discovered that I also need to re-link it to the front-end.

Have I misunderstood something?
Surely there is more efficient way to
A. Reduce the network traffic? (thus improving performance)
B. Not require a call to (update) the server view every time that the data changes.
C. not need to re-link the view on the client side.

thanks

 

[Galaxiom]

I recently read that when queries (which reside in the front-end) are run, that ALL the data in the linked tables is sent from the server to the client before any filtering, sort etc is applied.
The client then does the filtering and sorting.

Not if the query is appropriately designed. The Access engine will pass the query to the server if it can be translated. This main things that force the data to be downloaded are joins to tables not located on the server and Access user defined functions applied to data in the Where clause.

The view does not refresh when the underlying data is changed.

The view should always include the current data. But like an Access linked table it won't immediately update the display while it is open.

After I refresh the view, I have discovered that I also need to re-link it to the front-end.

You should only need to relink if you change the structure of the View.

 

[liddlem]

Awesome - Thanks

Not if the query is appropriately designed. The Access engine will pass the query to the server if it can be translated. This main things that force the data to be downloaded are joins to tables not located on the server and Access user defined functions applied to data in the Where clause.

I have learned something new again.
All my tables reside on the server, so there should be no issue here.

The view should always include the current data. But like an Access linked table it won't immediately update the display while it is open.

Hmm . . . . In light of the first comment, this is not really an issue to me any more. However (for curiosity sake) ....I found that after closing the form and re-opening, that I was getting all sorts of errors until I ran a 'sp_refreshView' on the server. How would I issue a refresh from Access? . . .or is the problem that the view is linked (thus still 'open')?

 

[Galaxiom]

I found that after closing the form and re-opening, that I was getting all sorts of errors until I ran a 'sp_refreshView' on the server. How would I issue a refresh from Access? . . .or is the problem that the view is linked (thus still 'open')?

I have never experienced this problem but generally my data isn't updated while I am connected to the views. Maybe I misunderstand what should happen.

I do have one where I could try this. I'll do it if I get a chance today.

You could run a passthrough query from Access to execute the system stored procedure.

 

[liddlem]

Thanks again
Im not too concerned about it right now since you have clarified that (in my case) access will parse the query for the server to handle.

 

[Minty]

We use lots of Views for complex queries that sometimes can't be written easily in Access, or to drag data in from other databases.
I can't think I have ever experienced the issues you are describing? Odd.

 

[isladogs]

My experience with using views is identical to Minty's

I also use views to speed up output where running a query in Access would take too long

In fact, the only downside as far as I'm concerned is that you can't have a PK field in a view which means its complex trying to use the view as part of further queries or procedures