August 9, 2004 at 5:45 am
My Access 97 front end subforms use passthru queries which call SQL Server procedures.
As always, I wish to limit the data transmitted across our WAN and less of an issue to mimimise impact on the Server.
I was thinking that as the number of, for example, sales reports increases in the backend. Perhaps I should limit the number of Sales reports visible at anyone time for a single customer.
Currently a user could view 20 sales report summaries at any one time. And then use normal Windows functionality to scroll through the list. Using down scroll bar, clicking on arrow etc.
I am quite happy with the way this works but wondered whether this method has an adverse impact on WAN traffic and SQL Server performance.
I was wondering whether I need to replicate Access functionality but say filter for top 20 records, then top 20 but not in top 20, top 20 but not in top 40 ...... but would be difficult to make this as user friendly as the default method.
When I check in trace (maybe I am checking the wrong thing), I find that the procedure is called only once. But if I create a dummy table with say 1 million rows, it is clear that only the immediately required rows are returned to the frontend. So perhaps default functionality works efficiently anyway.
Would appreciate advice.
August 9, 2004 at 6:16 am
sql server and access will drip feed the results through the connection.
what you can do to give your users the perception of a faster connection is use the FAST hint in your stored procedures.
this will return the first n number of rows before execution has completed. the query still takes roughly the same amount of time, and the same amount of data is transfered, it just appears faster.
MVDBA
August 10, 2004 at 9:54 am
Just to be clear.
If Access calls a procedure which returns 1,000,000 rows. The Access form only displays the most recent 20 which might be all the user wants to see.
So the user views the 20 rows and then exits the form.
Are you saying that while the form is open, the first 20 rows come in fast (they do) but then the remaining 999,999,980 will drip feed across the WAN into the local PC memory.
If this is the case, then performance on the local PC does not appear to be effected but the impact on the WAN would be enormous.
Or as I hope, does Access say to SQL Server - only send me the first 20 rows, I will tell you if I need any more? If this is the case, it is possible to see this happening in Profiler?
August 11, 2004 at 11:34 am
So why don't you limit your data return like specifying a where clause in your store procedure? Another thing you can do is run a access query to filter your store procedure data. I prefer to do everything in the store procedure. This will help to improve the performance as well.
Minh
August 12, 2004 at 1:54 am
not quite that way,
access calls gets the results steadily using as much network bandwidth as it can.
the reult set is available immediatley, (which is why you can navigate quickly between your first 20 records) but if you clicked on the LAST record button, hyou would see that access stalls and waits for the WHOLE recordset to be completed.
if you are using Access in a WAN environment then you should think about running access on a terminal server or citrix session where your bandwidth between TS and SQL is higher
MVDBA
August 12, 2004 at 3:03 am
Before deciding the current method I did test on one of our Citrix Servers. I found that response times generally were much faster using ADO with the front end on the client rather than via Citrix.
I concluded that the reason for this was that generally the application is designed to pull back only data immediately relevant to the user interface. This data must be much less I guess than the screen coming across the WAN. Or perhaps it could be that our Citrix Server is not fast enough.
Response times across the Wan with my app are reported as being faster than any of our conventional systems (split second) so it seems to be a good solution. the only downside is the need for a frontend on each PC.
"access calls gets the results steadily using as much network bandwidth as it can" Are you sure about this? Is there anyway I can prove / monitor this? When run performance monitoring on a test backend and monitor bytes sent, the graph only shows any movement when I press the page down button. Which seems to indicate that data is only pulled across the WAN when required.
I guess that I can easily add date range filtering to the front end (and probably will do so) but I would really like to try and understand exactly what is going on. Still feel bit confused.
August 12, 2004 at 3:09 am
there are some network throttling (and monitoring) tools that you can download (we used them to simulate moving from citrix published apps to a Client/server app over the WAN.
i'm not sure about Access forms, but datasheet view will attempt to complete the query.
what bandwidth do you have for your connection? and also what is the usage like on your citrix server.
if access goes AWOL on you then your citrix desktops and other published apps will suffer because access will try and take up the whole bandwidth.
MVDBA
August 12, 2004 at 7:39 am
Mike, many thanks for your comments.
While monitoring I was using an SQL Passthru query which when switching from design to view I guess is the equivalent of a table.
If the whole data set is trickled across the WAN, why is it that I only see the peaks on the graph when I page down?
Believe our WAN runs at 100 Mbps between the main locations.
"if access goes AWOL on you then your citrix desktops and other published apps will suffer because access will try and take up the whole bandwidth"
Lots of our applications are slow and have always been so. My Access application always runs fast even when users sign in using VPN via Internet (broadand or modem). In view of your comments even more concerned about getting to the bottom of this!!!
As you probably gather I am fairly new to SQL Server and networks but have quite a bit of Access experience. Dangerous I guess, but I am very cautious and try to understand what I am doing.
August 12, 2004 at 8:02 am
100Mbps - no worries - i've been used to deploying these acess/SQL solutions across a 512kbps ADSL line(where it's difficult to get a higher speed line installed - such as building sites and railway depots)
there are going to be different behaviours in your network usage depending on how your app is coded/designed.
if access isn't throttling back other apps by using up the bandwidth then you should be ok. but it would be wise to test the >> button (go to last record) on an access datasheet or form - see how long it takes and what your network response is.
a thin client solution would be better across lower speed WAN connections, although your citrix costs will go through the roof to keep performance high.
MVDBA
August 13, 2004 at 7:48 am
"your citrix costs will go through the roof "
I remember now when preparing the Capex that the backend hardware and software for the simple solution came to $17,000 but with Citrix we needed 3 or 4 Servers instead of 1 plus additional license costs. The price would indeed have gone through the roof and the project would never have been approved.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply