stbedes
asked on
un-split database
We have created a databse in Access2000 for use over a LAN.
This has been split so the back end is on one of our serves and the front end on the workstation.
The reference to the back end used a UNC to prevent reliance on a specific mapped drive letter, but performance is poor.
We suspect we may be better off mapping a drive letter in the login script and referring to our back end through that.
Question - how do we unsplit/rejoin the database so we can split it again. (do we have to map each query to its new location - hopefully not - or can we point to it in a single place in the database)
Constructive comments please, even if there may be an obvious answer we have missed.
This has been split so the back end is on one of our serves and the front end on the workstation.
The reference to the back end used a UNC to prevent reliance on a specific mapped drive letter, but performance is poor.
We suspect we may be better off mapping a drive letter in the login script and referring to our back end through that.
Question - how do we unsplit/rejoin the database so we can split it again. (do we have to map each query to its new location - hopefully not - or can we point to it in a single place in the database)
Constructive comments please, even if there may be an obvious answer we have missed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i'll verify what dennisborg wrote. so far you're two for two on good answers. i've got something else though...
instead of remapping the tables (you could though, that's fine) simply map a drive to the server. any drive letter will work.
i used to have the EXACT same situation as you have. someone would have a problem and i'd go to their station to troubleshoot. when i got there, the app ran 10x slower than it did on MY workstation. i was puzzled. why was it so much slower at the user's workstation. then one day i found a user who had a mapping to my server. (any mapping to the server is fine) and at that station the app was as fast as it is on MY development station... finally i realized that as long as the user's pc had ANY mapping to the server in question, performance was improved dramatically. (moving to sql server has even better effects)
what happens when you map a drive "permanently" is the M$ operating system hold the "path" to the server and caches that path somewhere. then, when the UNC name is called, it knows the EXACT path to take to get to the server, making things a BUNCH faster...
try it out. map ANY drive to the server in question without modifying the app and see if it makes a difference. i know it did for me using WinNT and A97. i'd be interested to know if it was just something that i experienced.
btw. i work for a large company (30,000+ employees worldwide) and my users are on a couple of different subnets...
enjoy,
dovholuk
instead of remapping the tables (you could though, that's fine) simply map a drive to the server. any drive letter will work.
i used to have the EXACT same situation as you have. someone would have a problem and i'd go to their station to troubleshoot. when i got there, the app ran 10x slower than it did on MY workstation. i was puzzled. why was it so much slower at the user's workstation. then one day i found a user who had a mapping to my server. (any mapping to the server is fine) and at that station the app was as fast as it is on MY development station... finally i realized that as long as the user's pc had ANY mapping to the server in question, performance was improved dramatically. (moving to sql server has even better effects)
what happens when you map a drive "permanently" is the M$ operating system hold the "path" to the server and caches that path somewhere. then, when the UNC name is called, it knows the EXACT path to take to get to the server, making things a BUNCH faster...
try it out. map ANY drive to the server in question without modifying the app and see if it makes a difference. i know it did for me using WinNT and A97. i'd be interested to know if it was just something that i experienced.
btw. i work for a large company (30,000+ employees worldwide) and my users are on a couple of different subnets...
enjoy,
dovholuk
dovholuk:
Thank you for the kind words. :-)
I do have a couple questions though.
First, thank you for the good explaination and suggestion. I learned something new today because of you. :-)
As I understand your suggestion, you would map a drive to the server, but not change the connect property for any of the linked tables. That is, the linked tables would still use the UNC references. This in and of itself would improve the performance when the two computers communicate with each other.
Question #1) - You said to map any drive to that *server*. For your suggestion to work properly, would you not need to map to that same Share Name on that server? I know you didn't say that any share on that server would do, but it was left a little unclear.
In other words, if you used \\MyServer\MyShare\MyDir\M
Question #2) - Do you know, either by experience or by theory, whether replacing the UNC's with a drive reference in the linked table's connection property would help more than simply mapping a drive but leaving the linked table connections unchanged?
Again, thank you for your insight in this matter.
-Dennis Borg
Thank you for the kind words. :-)
I do have a couple questions though.
First, thank you for the good explaination and suggestion. I learned something new today because of you. :-)
As I understand your suggestion, you would map a drive to the server, but not change the connect property for any of the linked tables. That is, the linked tables would still use the UNC references. This in and of itself would improve the performance when the two computers communicate with each other.
Question #1) - You said to map any drive to that *server*. For your suggestion to work properly, would you not need to map to that same Share Name on that server? I know you didn't say that any share on that server would do, but it was left a little unclear.
In other words, if you used \\MyServer\MyShare\MyDir\M
Question #2) - Do you know, either by experience or by theory, whether replacing the UNC's with a drive reference in the linked table's connection property would help more than simply mapping a drive but leaving the linked table connections unchanged?
Again, thank you for your insight in this matter.
-Dennis Borg
Hi Dennis...
1.) you need not map to the same share. a common technique i used to employ (and still do where necesarry) is to map to UNC "hidden" shares that the user normally can't open, see, or access. even still. mapping to ANY share will hold the path (through the network) to the server. i found that THAT's the really important part. every time you send a packet to a server it follows a route through routers/switches/hubs (whatever's applicable). once you establish this route the OS somehow caches the path and data transmission is very fast. at least, it was for me! :)
2.) i've mapped to drives on purpose because it DOES provide a significant improvement in speed. for example. i had a db that was "saving" images into a db. (in reality, the images were being saved to a share) using UNC it took a LONG time. i found that the time that it was taking, was simply the time to LOATE the server. after the server was located, the data transfer happened as fast as the network works. so, for fun i mapped a drive and tried again. mapping the drive skips the whole "hello network. please find me MyServer. Oh, MyServer? that's IP 192.168.32.23. IP .... etc." that was what i found was the limiting step. by mapping a drive, all the "does the server exist and where is it" work the OS does, is cached so the transmission happens very fast.
does that clear things up any?
i GOT to fly (gotta "date")
if i didn't answer your questions throughly, please forgive me. i'll check later.
dovholuk
1.) you need not map to the same share. a common technique i used to employ (and still do where necesarry) is to map to UNC "hidden" shares that the user normally can't open, see, or access. even still. mapping to ANY share will hold the path (through the network) to the server. i found that THAT's the really important part. every time you send a packet to a server it follows a route through routers/switches/hubs (whatever's applicable). once you establish this route the OS somehow caches the path and data transmission is very fast. at least, it was for me! :)
2.) i've mapped to drives on purpose because it DOES provide a significant improvement in speed. for example. i had a db that was "saving" images into a db. (in reality, the images were being saved to a share) using UNC it took a LONG time. i found that the time that it was taking, was simply the time to LOATE the server. after the server was located, the data transfer happened as fast as the network works. so, for fun i mapped a drive and tried again. mapping the drive skips the whole "hello network. please find me MyServer. Oh, MyServer? that's IP 192.168.32.23. IP .... etc." that was what i found was the limiting step. by mapping a drive, all the "does the server exist and where is it" work the OS does, is cached so the transmission happens very fast.
does that clear things up any?
i GOT to fly (gotta "date")
if i didn't answer your questions throughly, please forgive me. i'll check later.
dovholuk
In regard to my Question #2:
Did you realize my question was in how the preformance compared between the following scenarios:
1) Link the tables using UNC, but map a drive to the server anyway to improve performance.
2) Link the tables using a logical drive reference instead of using UNC
I was wondering if you could confirm my suspicion that scenario #2 would perform better than scenario #1
Thanks again, and I hope you have a great date!
BTW, do you have any comments on linking to a UNC refering to an IP address instead of to a machine name? (ex: \\111.222.333.444\ShareNam
-Dennis Borg
Did you realize my question was in how the preformance compared between the following scenarios:
1) Link the tables using UNC, but map a drive to the server anyway to improve performance.
2) Link the tables using a logical drive reference instead of using UNC
I was wondering if you could confirm my suspicion that scenario #2 would perform better than scenario #1
Thanks again, and I hope you have a great date!
BTW, do you have any comments on linking to a UNC refering to an IP address instead of to a machine name? (ex: \\111.222.333.444\ShareNam
-Dennis Borg
Well, old, old , common knowledge. Even if it may NOT be the solution to your problem it's always a good idea to create a dummy table (say, single row one) in the backend called e.g. "Dual", and upon opening frontend to open it in code and leave it open so that connection to back-end is always kept open.
Try it, and at least you'll find whether the slowdown is in Access seeking for backend locaton.
Try it, and at least you'll find whether the slowdown is in Access seeking for backend locaton.
hey dennis,
yes i noticed what q2 entailed. i thought i'd answered it, but i interpreted q2 differently.
i never did a study on which one had the better performance, however i really don't think that either would perform better than the other. the key is having a persistent mapping as koka pointed out. i don't like holding tables open with access though (just me) but holding a table open probably would do the same thing. the only difference is that the initial "startup" it takes to open the first connection is when you start the app, and not when you start the OS.
if you do any "studies", be sure to let me know what you find out! :)
dovholuk
yes i noticed what q2 entailed. i thought i'd answered it, but i interpreted q2 differently.
i never did a study on which one had the better performance, however i really don't think that either would perform better than the other. the key is having a persistent mapping as koka pointed out. i don't like holding tables open with access though (just me) but holding a table open probably would do the same thing. the only difference is that the initial "startup" it takes to open the first connection is when you start the app, and not when you start the OS.
if you do any "studies", be sure to let me know what you find out! :)
dovholuk
ASKER
Thanks for all these suggestions and the info.
We will try them next week (as anohter issue has raised it head) and get back to you.
We originally set our user profiles (start menu etc) to point to a UNC in a system policy (to avoid need for mapped drive letter and visible access to central profile on network). Logging in was ok, but evry time users went to the start menu it was v slow.
Changed it to a mapped drive letter in login script (accepting the security compromise) and it speeded up - hence my suggestion in question and confirmed by comments above. Don't have stats for the difference though.
We will try them next week (as anohter issue has raised it head) and get back to you.
We originally set our user profiles (start menu etc) to point to a UNC in a system policy (to avoid need for mapped drive letter and visible access to central profile on network). Logging in was ok, but evry time users went to the start menu it was v slow.
Changed it to a mapped drive letter in login script (accepting the security compromise) and it speeded up - hence my suggestion in question and confirmed by comments above. Don't have stats for the difference though.
ASKER
Apologies for delay in responding. The higher order IRQ was called MS Exchange Server.
Mapping to the same location speeded things up as suggested - so the points are yours.
(didn't have the add-in so could not give them to previous suggestion)
Many thanks for so many constructive contributions.
Mapping to the same location speeded things up as suggested - so the points are yours.
(didn't have the add-in so could not give them to previous suggestion)
Many thanks for so many constructive contributions.
i have a dutch version, so my naming might be of...
select menu 'Tools' --> 'Add in' --> ...
here is an add-in to refresh the linked tables.
U can select a new location in this window.
cheers
Ricky