7 Gigabits Per Second WAN Bottlenecks

[Deleted Bruce 182381]

I have a fiber optic modem that provides 7 gigabits per second speed, and using a Cat 8 double shielded ethernet cable that connects to a 10 gigabits per second NIC on my desktop. I am getting ~1 Giga Bytes per second xfer speed to a cloud backup share. I am wondering if I can reliably link an Access FE to a remote db if my WAN connection can maintain at least 1 Giga Bytes per second speed? @Albert D. Kallal should be able to shed light on this.

 

[NauticalGent]

Quit complaining, most of us only get a gig to the house and lucky if I can get 800mb to my PC and that is with COAX/MoCA network. Truth be told, its about 600mb.

 

[tvanstiphout]

Are you confusing bits and bytes?

 

[NauticalGent]

If the question is directed at me, I am referring to megabytes, MB

 

[AccessBlaster]

Doesn't it boil down to Latency not speed?

 

[NauticalGent]

I use VDI/Citrix on a thick client for my primary job (maintaining an Access app) with my puny/wired 600mb service - the performance is pretty impressive considering I am in Virginia and the server is in Ohio.

 

[AccessBlaster]

Latency, as in internet traffic lag?

Your pulling data across your network many times in a Access scenario, in a SQL backend it's probably better. But it still gets bogged down.

 

[The_Doc_Man]

I have successfully run a native Access FE/BE situation for the U.S.Navy with a 1 Gbit Ethernet, for 15-20 concurrent users. I have PAINFULLY run an Access FE/BE from New Orleans to Norfolks and back again (it's a LONG story) where at least one of the intermediate hops was a 10 Mbit Ethernet. It ran. (Actually, no... it didn't run. Didn't even walk. It C-R-A-W-L-E-D. But it operated.) The problem was never that it couldn't operate. It was just that it couldn't operate quickly.

We switched from the innermost (and most secure) network to an intermediate level that no longer utilized the Norfolk connection. That's when I gave up the option to send encrypted e-mails from my app. I could still send e-mails but the server that took part in certificate validation couldn't be reached. After that, the speed problem went away. However, that was an in-building network.

How far is remote? More precisely, how RELIABLE is the remote site's connection? If the reliability score is measured in percent and the score is above 1% instability, you realize that the odds say your network will fail every hundred operations.

The question of latency in networks is simply one of how many hops do you need to make in the standard store-and-forward algorithm. The speed of the network DOES figure into that, but what you really care about with networks isn't latency - it is drops, collisions, outages, that sort of thing. As long as the message makes it through, if you don't pull up the network stats, you can't tell the difference between a network with lots of collision/retransmission and a network with just a slow transmission rate. And you don't care. It is packet loss rate that eats your socks.

 

[tvanstiphout]

No, there's 8 bits in every byte.

Your screenshot in #1 is showing 1.08 GB/s. The "B" stands for Byte.
Your #1 message seems to convey disappointment that "7 gigabits" speed only results in this 1.08 GB speed.
I tried to point out the two are (almost) equivalent.

 

[Albert D. Kallal]

Well, things have vast improved over the years.

So, while at one time, say a VPN between two locations, and running a split Access database?

Was not that practical.

Now, well, actually it is!

However, if the VPN was being used say by “at home” workers?

Then that falls down, since it not all the fancy corporate hardware and fast network at play here, but the limitation is the “at home” users internet plan!

So, as networks become faster, better etc.?

Then the “ability” to run a split FE/BE over that network thus also improves.

So, assuming no VPN bottle necks?

Then such a setup should work quite well.

Often the issue with a VPN?

Well, along the way the outside “internet” is in the way, and often then the risk or issue is not the network speed, but does it ever “drop” or “break”. That drop or break problem is the deal breaker here. And thus one would recommend moving back end to SQL server. And it often not a speed issue to adopt SQL server, it’s a question of network reliability.

A split FE/BE simple does not tolerate a network connection that can stop or break for a small amount of time. A FE to SQL server can….


If that split FE/BE now runs well on a network?

And the design is to “limit” records pulled into the form?

Then I can’t see why this proposed setup would not work well…..


Remember, if you take a plain standard FE/BE, and it’s running over a network?
(no SQL server)

And then you bind the access form to a table with 1 million rows?


And then you do this:

Docmd.OpenForm “frmInvoice”,,,”InvoiceNum = 12343”

Access will actually only pull the ONE record over the network!

(assuming InvoiceNum column is indexed).


So, not knowing the application?

Well, if it runs ok over a network now?
Then I would be comfortable suggesting that it will run just fine with the proposed setup…

However, the instant some kind of VPN or remote users come into this mix?
Then it's SQL server, or remote desktop all the way....

R
Albert

 

[The_Doc_Man]

Agreed, so hopefully technology will soon reach a point where no TCP packets will be lost,

As long as networks required a physical transmission medium (even if it is wireless), the problem isn't that TCP packets are lost to protocol errors, but that the point-to-point layer can be interrupted by things that take too long to fix. Like, lightning striking a wireless tower with a less than perfect ground connection. Or some person of limited intelligence slamming his obscenely huge pickup truck into a utility pole that carries fiber data for phone lines. Automatic re-routing has come a long way, but if you are in an isolated area, you might not have an alternate route. Since it is possible for a given network-related app to involve all seven layers of the OSI model, you have entirely too many ways to lose a connection.

 

[Albert D. Kallal]

So despite having ultra fast internet connection, it's still recommended to optimise WAN traffic between native Access FE/BE link. I am experimenting with recommendations mentioned in the attached document.

yes.

in fact, the #1 developer time saving tip I can share?

Any time you have a query with multiple joins?
Convert that to that to a SQL view, and link to that view.

But why this suggestion over say a pass-through query, or even perhaps say some stored procedure on SQL server?

Simple: cost and time vs the benefits.

Often the report (or form) will use some addtional VBA code to create a filter. And often such code represents significant time and cost.

Well, converting to a view means that the code, the form, the report? The existing filters will THEN play nice with SQL server, and MORE imporant is the cost and time to acheive first rate results?
It is very low.

If you have a complex form, or report based on a complex SQL query?
if you move the query to a view, link to that view (and even give it the same name as the client side query)?

Well, now you going to get about the same performance if you used a pass-though query, or even a stored procedure.

But, the HUGE bonus is you don't have to change any client side code that creates a filter for the form/report.
And by filter, I mean use of the "where" clause that both forms and reports have.

So, say this:

docmd.OpenForm "frmInvoices",,,"InvoiceNum = 13243"

The above code does not require any changes. But, if frmInvoices (or report) is based on a mutli-table query with joins?
Then you get server side performance, the joins and SQL occurs server side, and ONLY the result is sent back down the wire.

now, there are as noted other ways to achieve this, but adopting the view, linking to that view?
Far less work then ANY other solution and approach.

Bottom line:
Views are your first friend and best first steps to improve Access to SQL server performance.
9 out 10 times, with this simple effort and change?

You get first rate performance, and as noted, in near all cases, no changes to the client side VBA code that no doubt should and does use a "where clause" when opening that form or report....

So, this simple change (using a view) tends to equal the performance compared to the significant efforts to say create a pass-through query, or even a stored procedure.

So, my view on this?
Use views! - pun intended!!!!

R
Albert

 

[Minty]

If the combo's are lookups and normally static values then load them into a local cache table on the FE database when you open it.
Things like employee lists, state codes, Countries, Currencies etc. that are frequently used and rarely change can and should be loaded into a local lookup table for speed.

Complicated Combo rowbox sources should also be made from views, but I suspect you are already doing that.

 

[Albert D. Kallal]

The row sources are queries that bring in values which are regularly updated, so I wouldn't be able to leverage storing those values on the frontends, nor using views because the combos need all the values.

Actually, I can add one more FYI to the issue of combo boxes.

Do NOT use a pass-through query to drive a bound combo box.

But, why?

This advice seems counter intuitive, since after all, a PT query is perhaps the fastest performing way to pull data from SQL server, right?

Well, there is one "dark" downside, and issue one has to be aware in the case of a PT query.

Answer:
Access from the client side cannot filter such a query!!!

So, EVEN if one where to build (modify) the PT query client side to restrict the rows for that combo box?

Still bad!!!
And the reason is as follows:

When the form loads, or you navagate to another record?
Access is displaying the combo box, and OFTEN of course we have a hidden id, and are thus displaying a 2nd column (description or whatever).
And, that means Access has to look up, and take that ONE row out of the combo box data source.

But, as I pointed out, Access can't filter a PT query down to one record, and in fact Access cannot filter the PT query in ANY WAY!!!

What this means?
Well, when you navigate to the next record, or even load the form?
Access has to hit the data source, and "scan it" for the one row that is currently selected. And since the data source can't be filtered?
Then the whole data source will be scanned.

I had a few forms - combo box based on PT query. And when I moved to next record, there was a significant delay.
I traced the issue down to that combo box + PT query.

Changing the combo box source to a view? (which the client side can then filter correctly)?
The big delay went away.

So, yes, use a PT query as you need to, but if ANY client side code, ANY where clause is to be against that PT query?
Then do not use or do this!!!
use a view.

And if the query is a single table, then of course no view is required nor will it help, and you are free to base the combo box on that SQL that points to the linked table.

However, using a PT query for that combo box, and it is a bound combo box?
Nope, Access can't filter the results (well, it can, but that's AFTER all rows are pulled from server, and THEN the filter is applied).

So, Access client will attempt a single row filter against the combo box data source - and that's not possible against a PT query.

Once again, the above narrative shows why I'm such a big fan of linked views over PT queries.

Of course, more then 1000 rows for a combo box is a bad idea anyway. But, at least with a view, then the Access client can correctly filter the one row result for display - with a PT query, Access can't filter down to that one row - Access will wind up re-pulling the data....


R
Albert

 

[NauticalGent]

Do NOT use a pass-through query to drive a bound combo box.

Found this out the hard way, using the flawed logic you mentioned...

 

[NauticalGent]

The combos need all unfiltered rows from single tables that are regularly updated, so using views provides no advantge.

I haven't tried views, but the fact that it is server-side filtering makes me think it would be beneficial. What I DO know is that using a client-side query was significantly better than using a PT query for my combo-boxes. I had no idea why, but like posi-traction - it just worked!

 

[Albert D. Kallal]

I haven't tried views, but the fact that it is server-side filtering makes me think it would be beneficial. What I DO know is that using a client-side query was significantly better than using a PT query for my combo-boxes. I had no idea why, but like posi-traction - it just worked!

As noted, PT queries and a combo box don't play nice here.

Access client can't filter the PT query correctly.
And for messy sql? Again, move to server, and then base the combo box on that view......

So, a PT query for a combo box simply does not work well, but building the view and using that linked view for the combo?
You be happy with the results ......

R
Albert

 

[Albert D. Kallal]

Views are perfect for when you need filtered results, and for queries involving multiple tables. The heavy lifting is best done server side to minimise network traffic. The beauty about server side views is that Access FE's see them as linked tables. I have some combos that use self-join queries as their row sources, and those also delay form loading.

Another trick that helps?
Just specify the view for the combo box, not a SQL query. They again will load faster. This can't always be done. But, if you have say 3-5 combo boxes on a form?
You notice a slight improvment. So, JUST the view specifed for the combo box source. Now, to be fair, views in theory don't have a valid sort, but you can put in a sort on the view (server side). The designer will then toss in a top 100 clause. As noted, a view is considered a un-sorted table.

So, if just 1-3 combo boxes? Then you can well just use SQL against the view for the combo box source.

But, I have noticed a slight improvement if you JUST specify the view name, and no SQL for the combo box.....
R
Albert

 

[Albert D. Kallal]

Server views appear as linked tables in Access FE's so I just use a regular AccessSQL query against that logical table.

Yes, but note the above trick - if you JUST use the view name - no SQL for the combo source, the form loads even faster.
You not notice this effect unless/until you have about 3 or more combo's on the page. So, it's a "minor" issue, but I have seen this trick reduce form loads times.

So, for most forms, I would not bother to introduce this trick - but for forms with more then a few combos, it's just a FYI to keep in mind.

Regardless, you fine here, and as you state, you go with just using regular accessSQL for this.

The real FYI and lesson here?
Don't use PT query to drive a bound combo box - they don't work well, and this is not widely known....

Good luck!!

R
Albert

 

[Albert D. Kallal]

The combos row source property says Table/Query, so you're saying put the table name that represents the linked view?

Yes, correct.
So, no sql statement at all - just the view name.

You find that form loads a bit faster - but only when you have say beyond 3 or more combo boxes will you start to notice this "small" reduction in form load time.

So, not a huge deal, but yes, in the combo source, don't place any SQL at all - but JUST the view name (that is the linked view).

R
Albert