00713: Mapped drive performance is slow for more than one user

Title:

Mapped drive performance is slow for more than one user

Description:

When one user accesses a file such as spreadsheet via a mapped drive, performance is quite good. As soon as more users try to access these files, performance greatly decreases. How can I resolve this and still maintain data integrity? 

Resolution:

The following code sample creates a single-keyed MKEYED file on a mapped drive, writes a record to it, extracts the record, increments the counter in the data, and repeats this routine 10,000 times. We ran this test twice, as a benchmark, on an NT Workstation 4.0. Both times, it executed this program in 9 seconds. 

0010 LET F$=”c:\temp\darryl.dat” 
0020 LET F$=”/<reltest6,port=5160>c:\temp\darryl.dat” 
0030 ERASE F$,ERR=0040 
0040 MKEYED F$,10,10,10 
0050 OPEN (1)F$ 
0060 LET K$=”00000″ 
0070 WRITE (1,KEY=K$)K$ 
0080 CLOSE (1) 
0090 LET START_TIME=TIM 
0100 OPEN (1)F$ 
0110 FOR I=0 TO 10000 
0120 EXTRACT (1,KEY=”00000″)A$ 
0130 LET A$=STR((I+1):”00000″) 
0140 WRITE (1,KEY=”00000″)A$ 
0150 NEXT I 
0160 CLOSE (1) 
0170 PRINT “Time:”,(TIM-START_TIME)*3600,” seconds” 
0180 END 

Using a mapped drive is a good solution for one user but is not designed for multiple users. Sharing files, especially a database, among multiple users is a configuration that requires a database solution. To test this scenario, we added the PRO/5 Data Server (BASIS’ DBMS solution) to our configuration. Here is what we identified. 

1) When a Windows workstation read and wrote data to a file on the NT Server, it performed much faster via the PRO/5 Data Server than to a mapped drive. 

2) When an NT workstation read and wrote data to a file on the NT Server, it performed much faster via the mapped drive than to the PRO/5 Data Server. However, this performance gain from the mapped drive only applied to files opened in single-user mode. In other words, if more than one user has the file open, access was much faster via the PRO/5 Data Server. Here are the results of another test program that EXTRACTed a record, modified it, and wrote it back to the file 10,000 times: 
SINGLE USER 
13 Seconds – via a mapped drive as the only user with the file open 
58 Seconds – via PRO/5 Data Server as the only user with the file open 
MULTIPLE USERS 
2006 Seconds – via a mapped drive as the second user with the file open 
61 Seconds – via PRO/5 Data Server as the second user with the file open 

This shows that when only one user at a time modified the file, file access via a mapped drive was faster than via the PRO/5 Data Server. By contrast, if more than one user opens the file (a very common scenario in most applications), access via the PRO/5 Data Server was 3288% faster than via the mapped drive. 

3) When multiple users attempted to EXTRACT a record in the file, priority levels were uneven when accessing the file via a mapped drive. To illustrate, we re-ran the above test, but this time two users were actively EXTRACTing and WRITEing multiple records. Additionally, the test only made 1,000 iterations. 
10 and 12 Seconds – via PRO/5 Data Server with two users 
354 and 354 Seconds – via mapped drive with two users 

When the clients accessed a mapped drive, they would noticeably take turns for several records in a row. However, this was not the case when they accessed the file via the NT Data Server. Additionally, when both clients accessed the same record, the behavior of the mapped drive deteriorated. In this case, one session ran completely through its 1,000 iterations while the other continued to fail with its EXTRACT. After the first session was completed, then the second session could EXTRACT records and run the test. Again, this did not happen when accessing the file via the PRO/5 Data Server. With the PRO/5 Data Server, both clients finished at approximately the same time as neither one had exclusive access to the file during the test. 

To resolve the stated problem, BASIS recommends a data server for any application where multiple users need to access the same database. While mapped drives are ideal for one user, data servers are designed to manage multiple users with efficiency, speed, and data integrity. 

Last Modified: 06/21/2005 Product: PRO/5 Data Server

BASIS structures five components of their technology into the BBx Generations.