MapNetworkDrive: Extremely Poor Performance When Writing to Path

Hi All,

I'm attempting to use the MapNetworkDrive below from entire
code below with very poor performance results.

Basically, I have very small 73kb text files that are rewritten daily
to a network share using the MapNetworkDrive command to connect to the
valid \\servername\share path.

After successfully connecting the drive in the ASP code, the text file
write process begins and takes more than 15 minutes to write the first
file when the asp page is executed. The process finally times out
since I have set the Server.ScriptTimeOut = 900 for testing purposes
which limits the asp page execution to 15 minutes. In that entire
time, the first file is viewable on the network share but only
partially completed. The additioanl files never complete.

I have done some troubleshooting by manually mapping the share
(outside of the ASP code) using Windows Explorer and copied the same
file that is being created in the routine from the IIS server to the
mapped drive - results are miliseconds to copy - excellent performance
this way.

Any idea of what would cause this performance hit from the looks of my
code? The server is IIS W2K3 and latest version of IIS and updates is
installed. We run many other ASP processes on the server successfully.

'

Set objNetwork = CreateObject("WScript.Network")
'objNetwork.RemoveNetworkDrive "M:"
strDriveLetter = "M:"
strRemotePath = "\\nad2divdb1\E_PT"
strUser = "Username"
strPassword = "Password"
strProfile = "False" ' means do not store in profile leave as false.
objNetwork.MapNetworkDrive strDriveLetter, strRemotePath, strProfile,
strUser, strPassword
' WScript.Quit

'

Here is the entire page of code........











<%
' set timeout for longer processing (default is 90 seconds)
Server.ScriptTimeOut = 900 ' In seconds - 300s = 5m

' define variables
dim oConn ' ADO Connection
dim oRSc ' ADO Recordset - Courier table
dim cfedexSQLstr ' SQL string - Courier table - FedEx
extraction
dim cupsSQLstr ' SQL string - Courier table - UPS
extraction
dim cdhlSQLstr ' SQL string - Courier table - DHL
extraction
dim oRSn ' ADO Recordset - NAN table
dim nSQLstr ' SQL string - NAN table
dim objFSO ' FSO Connection
dim objTextFile ' Text File
Dim i, j, tmp ' variables used for the Text File
Build process
Dim strMonth, strDay, strYear ' variables from user selection of
month/day/year
dim strDate ' variable to store complete date
dim cSwitch ' switch for Courier field(s) to
prevent extra comma when two spaces between fields occur
dim cSubSwitch ' subroutine loop switch (used to
determine clear of oRSc recordset)
dim objNetwork ' WScript drive access Connection
dim strRemotePath, strProfile, strUser, strPassword ' Additional
WScript variables

' set and open ADO connection
set oConn=Server.CreateObject("ADODB.connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & "c:/
Database/QaTracking/QaTracking.mdb" & ";"

' set and define FSO connection and text file object location
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objNetwork = CreateObject("WScript.Network")
'objNetwork.RemoveNetworkDrive "M:"
strDriveLetter = "M:"
strRemotePath = "\\nad2divdb1\E_PT"
strUser = "Username"
strPassword = "Password"
strProfile = "False" ' means do not store in profile leave as false.
objNetwork.MapNetworkDrive strDriveLetter, strRemotePath, strProfile,
strUser, strPassword
'WScript.Quit

' Store User Month/Day/Year
strMonth = Request.QueryString("Month")
strDay = Request.QueryString("Day")
strYear = Request.QueryString("Year")
strDate = strMonth & "/" & strDay & "/" & strYear

' SQL strings for Courier and NAN tables
cfedexSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Fedex'
AND Courier.Date = #" & strDate & "#"
cupsSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'UPS' AND
Courier.Date = #" & strDate & "#"
cdhlSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Airborne'
AND Courier.Date = #" & strDate & "#"
nSQLstr = "SELECT * FROM NAN"

' Initial Switch values
'cSwitch = "Off"
cSubSwitch = "Off"

'*******************************
'//// BUILD FEDEX TEXT FILE ////
'*******************************
Response.ContentType = "text/plain"
'cSwitch = "Off"
'------------------------------
'//// OPEN FEDEX RECORDSET ////
'------------------------------
set oRSc=Server.CreateObject("ADODB.Recordset")
oRSc.Open cfedexSQLstr, oConn
'------------------------------
'//// OPEN FEDEX TEXT FILE ////
'------------------------------
'Set objTextFile = objFSO.CreateTextFile(Server.MapPath("textfile.txt",
2))
'Response.Write (Server.MapPath("textfile.txt") & "
")
Set objTextFile = objFSO.OpenTextFile("M:\FED000.txt",2)
'--------------------------------------------
'//// CALL SUBROUTINE TO BUILD TEXT FILE ////
'--------------------------------------------
Call TextFileBuildLoop

'*****************************
'//// BUILD UPS TEXT FILE ////
'*****************************
Response.ContentType = "text/plain"
'cSwitch = "Off"
'----------------------------
'//// OPEN UPS RECORDSET ////
'----------------------------
set oRSc=Server.CreateObject("ADODB.Recordset")
oRSc.Open cupsSQLstr, oConn
'----------------------------
'//// OPEN UPS TEXT FILE ////
'----------------------------
'Set objTextFile =
objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
'Response.Write (Server.MapPath("textfile.txt") & "
")
Set objTextFile = objFSO.OpenTextFile("C:\UPS000.txt",2)
'--------------------------------------------
'//// CALL SUBROUTINE TO BUILD TEXT FILE ////
'--------------------------------------------
Call TextFileBuildLoop

'*****************************
'//// BUILD DHL TEXT FILE ////
'*****************************
Response.ContentType = "text/plain"
'cSwitch = "Off"
'----------------------------
'//// OPEN DHL RECORDSET ////
'----------------------------
set oRSc=Server.CreateObject("ADODB.Recordset")
oRSc.Open cdhlSQLstr, oConn
'----------------------------
'//// OPEN DHL TEXT FILE ////
'----------------------------
'Set objTextFile =
objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
'Response.Write (Server.MapPath("textfile.txt") & "
")
Set objTextFile = objFSO.OpenTextFile("C:\DHL000.txt",2)
'--------------------------------------------
'//// CALL SUBROUTINE TO BUILD TEXT FILE ////
'--------------------------------------------
cSubSwitch = "On"
Call TextFileBuildLoop

'*******************
'//// SUBROUTINE////
'*******************
SUB TextFileBuildLoop
'---------------------------------
'//// WRITE TEXT TO TEXT FILE ////
'---------------------------------
'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"
'------------------------------------------------------
'//// BEGIN TEXT FILE BUILDING - LOOP THROUGH ROWS ////
'------------------------------------------------------
If Not oRSc.EOF Then
'----------------------------------------------------------- --
'//// THIS PART PRODUCES THE FIRST TOP ROW OF FIELD NAMES ////
'----------------------------------------------------------- --
' For i = 1 To oRSc.Fields.Count
' objTextFile.Write oRSc.Fields(i-1).Name
' If i < oRSc.Fields.Count Then
' objTextFile.Write " "
' End If
' Next
' objTextFile.WriteLine
' --------------------- // END // ----------------------------
While Not oRSc.EOF
For i = 1 To oRSc.Fields.Count
'------------------------------------------------------
'//// THIS PART ADDS QUOTES AROUND THE FIELD VALUE ////
'------------------------------------------------------
If oRSc.Fields(i-1) <> "" Then
tmp = oRSc.Fields(i-1)
' If TypeName(tmp) = "String" Then
' objTextFile.Write """" &_
'Replace(oRSc.Fields(i-1),vbCrLf,"""") & ""
' Else
'---------------------- // END // ---------------------
objTextFile.Write oRSc.Fields(i-1)
' End If
End If
' writes the space between fields
If i < oRSc.Fields.Count Then
' If cSwitch = "Off" Then
objTextFile.Write " "
' End If
End If
' If oRSc.Fields(i-1).Name = "Track" Then
' cSwitch = "On"
' Else
' cSwitch = "Off"
' End If
' loop through fields / stay on same record
Next
' go to next line in text file / move to next record
objTextFile.WriteLine
oRSc.MoveNext
Wend
End If
Set objTextFile = Nothing
If cSubSwitch = "Off" Then
Set oRSc = Nothing
End If
END SUB

'*****************************
'//// BUILD NAN TEXT FILE ////
'*****************************
Response.ContentType = "text/plain"
'----------------------------
'//// OPEN NAN RECORDSET ////
'----------------------------
set oRSn=Server.CreateObject("ADODB.Recordset")
oRSn.Open nSQLstr, oConn
'----------------------------
'//// OPEN NAN TEXT FILE ////
'----------------------------
'Set objTextFile =
objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
'Response.Write (Server.MapPath("textfile.txt") & "
")
Set objTextFile = objFSO.OpenTextFile("C:\NAN000.txt",2)
'---------------------------------
'//// WRITE TEXT TO TEXT FILE ////
'---------------------------------
'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"
'------------------------------------------------------
'//// BEGIN TEXT FILE BUILDING - LOOP THROUGH ROWS ////
'------------------------------------------------------
If Not oRSn.EOF Then
'----------------------------------------------------------- --
'//// THIS PART PRODUCES THE FIRST TOP ROW OF FIELD NAMES ////
'----------------------------------------------------------- --
' For i = 1 To oRSn.Fields.Count
' objTextFile.Write oRSn.Fields(i-1).Name
' If i < oRSn.Fields.Count Then
' objTextFile.Write " "
' End If
' Next
' objTextFile.WriteLine
' --------------------- // END // ----------------------------
While Not oRSn.EOF
For i = 1 To oRSn.Fields.Count
If oRSn.Fields(i-1) <> "" Then
tmp = oRSn.Fields(i-1)
'------------------------------------------------------
'//// THIS PART ADDS QUOTES AROUND THE FIELD VALUE ////
'------------------------------------------------------
' If TypeName(tmp) = "String" Then
' objTextFile.Write """" &_
'Replace(oRSn.Fields(i-1),vbCrLf,"""") & ""
' Else
'---------------------- // END // ---------------------
objTextFile.Write oRSn.Fields(i-1)
' End If
End If
If i < oRSn.Fields.Count Then
objTextFile.Write " "
End If
Next
objTextFile.WriteLine
oRSn.MoveNext
Wend
End If

Dim clientIP
clientIP = Request.ServerVariables("REMOTE_ADDR")

' define variable
dim objCDOMail
' create mail object
Set objCDOMail = Server.CreateObject("CDONTS.NewMail")
' compose message
objCDOMail.From = "CLI2-TRAINING " 'this format
authors the e-mail as a name of your choice
objCDOMail.To = "CLI2_Admin_LAN@adp.com;"
objCDOMail.Cc = ""
objCDOMail.Importance = 2 '(0=Low, 1=Normal, 2=High)
objCDOMail.Subject = "Package Tracking ADP4ME Export Notification for
" & strDate & ""
objCDOMail.BodyFormat = 0
objCDOMail.MailFormat = 0 ' CdoMailFormatMime 'use for MS Outlook mail
objCDOMail.Body = "" &_
"" &_
"" &_
"" &_
"" &_
"

The FedEx, UPS and DHL records for
" & strDate & " were processed successfully.

You may now close this browser
window.

Re: MapNetworkDrive: Extremely Poor Performance When Writing to Path

On Feb 5, 1:17 pm, "Billy" wrote:
> Hi All,
>
> I'm attempting to use the MapNetworkDrive below from entire
> code below with very poor performance results.
>
> Basically, I have very small 73kb text files that are rewritten daily
> to a network share using the MapNetworkDrive command to connect to the
> valid \\servername\share path.
>
> After successfully connecting the drive in the ASP code, the text file
> write process begins and takes more than 15 minutes to write the first
> file when the asp page is executed. The process finally times out
> since I have set the Server.ScriptTimeOut = 900 for testing purposes
> which limits the asp page execution to 15 minutes. In that entire
> time, the first file is viewable on the network share but only
> partially completed. The additioanl files never complete.
>
> I have done some troubleshooting by manually mapping the share
> (outside of the ASP code) using Windows Explorer and copied the same
> file that is being created in the routine from the IIS server to the
> mapped drive - results are miliseconds to copy - excellent performance
> this way.
>
> Any idea of what would cause this performance hit from the looks of my
> code? The server is IIS W2K3 and latest version of IIS and updates is
> installed. We run many other ASP processes on the server successfully.
>
> '
>
> Set objNetwork = CreateObject("WScript.Network")
> 'objNetwork.RemoveNetworkDrive "M:"
> strDriveLetter = "M:"
> strRemotePath = "\\nad2divdb1\E_PT"
> strUser = "Username"
> strPassword = "Password"
> strProfile = "False" ' means do not store in profile leave as false.
> objNetwork.MapNetworkDrive strDriveLetter, strRemotePath, strProfile,
> strUser, strPassword
> ' WScript.Quit
>
> '
>
> Here is the entire page of code........
>
>
>
>
>
>
>
>
>
>
>
> <%
> ' set timeout for longer processing (default is 90 seconds)
> Server.ScriptTimeOut = 900 ' In seconds - 300s = 5m
>
> ' define variables
> dim oConn ' ADO Connection
> dim oRSc ' ADO Recordset - Courier table
> dim cfedexSQLstr ' SQL string - Courier table - FedEx
> extraction
> dim cupsSQLstr ' SQL string - Courier table - UPS
> extraction
> dim cdhlSQLstr ' SQL string - Courier table - DHL
> extraction
> dim oRSn ' ADO Recordset - NAN table
> dim nSQLstr ' SQL string - NAN table
> dim objFSO ' FSO Connection
> dim objTextFile ' Text File
> Dim i, j, tmp ' variables used for the Text File
> Build process
> Dim strMonth, strDay, strYear ' variables from user selection of
> month/day/year
> dim strDate ' variable to store complete date
> dim cSwitch ' switch for Courier field(s) to
> prevent extra comma when two spaces between fields occur
> dim cSubSwitch ' subroutine loop switch (used to
> determine clear of oRSc recordset)
> dim objNetwork ' WScript drive access Connection
> dim strRemotePath, strProfile, strUser, strPassword ' Additional
> WScript variables
>
> ' set and open ADO connection
> set oConn=Server.CreateObject("ADODB.connection")
> oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & "c:/
> Database/QaTracking/QaTracking.mdb" & ";"
>
> ' set and define FSO connection and text file object location
> Set objFSO = CreateObject("Scripting.FileSystemObject")
>
> Set objNetwork = CreateObject("WScript.Network")
> 'objNetwork.RemoveNetworkDrive "M:"
> strDriveLetter = "M:"
> strRemotePath = "\\nad2divdb1\E_PT"
> strUser = "Username"
> strPassword = "Password"
> strProfile = "False" ' means do not store in profile leave as false.
> objNetwork.MapNetworkDrive strDriveLetter, strRemotePath, strProfile,
> strUser, strPassword
> 'WScript.Quit
>
> ' Store User Month/Day/Year
> strMonth = Request.QueryString("Month")
> strDay = Request.QueryString("Day")
> strYear = Request.QueryString("Year")
> strDate = strMonth & "/" & strDay & "/" & strYear
>
> ' SQL strings for Courier and NAN tables
> cfedexSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Fedex'
> AND Courier.Date = #" & strDate & "#"
> cupsSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'UPS' AND
> Courier.Date = #" & strDate & "#"
> cdhlSQLstr = "SELECT * FROM Courier WHERE Courier.Courier = 'Airborne'
> AND Courier.Date = #" & strDate & "#"
> nSQLstr = "SELECT * FROM NAN"
>
> ' Initial Switch values
> 'cSwitch = "Off"
> cSubSwitch = "Off"
>
> '*******************************
> '//// BUILD FEDEX TEXT FILE ////
> '*******************************
> Response.ContentType = "text/plain"
> 'cSwitch = "Off"
> '------------------------------
> '//// OPEN FEDEX RECORDSET ////
> '------------------------------
> set oRSc=Server.CreateObject("ADODB.Recordset")
> oRSc.Open cfedexSQLstr, oConn
> '------------------------------
> '//// OPEN FEDEX TEXT FILE ////
> '------------------------------
> 'Set objTextFile = objFSO.CreateTextFile(Server.MapPath("textfile.txt",
> 2))
> 'Response.Write (Server.MapPath("textfile.txt") & "
")
> Set objTextFile = objFSO.OpenTextFile("M:\FED000.txt",2)
> '--------------------------------------------
> '//// CALL SUBROUTINE TO BUILD TEXT FILE ////
> '--------------------------------------------
> Call TextFileBuildLoop
>
> '*****************************
> '//// BUILD UPS TEXT FILE ////
> '*****************************
> Response.ContentType = "text/plain"
> 'cSwitch = "Off"
> '----------------------------
> '//// OPEN UPS RECORDSET ////
> '----------------------------
> set oRSc=Server.CreateObject("ADODB.Recordset")
> oRSc.Open cupsSQLstr, oConn
> '----------------------------
> '//// OPEN UPS TEXT FILE ////
> '----------------------------
> 'Set objTextFile =
> objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
> 'Response.Write (Server.MapPath("textfile.txt") & "
")
> Set objTextFile = objFSO.OpenTextFile("C:\UPS000.txt",2)
> '--------------------------------------------
> '//// CALL SUBROUTINE TO BUILD TEXT FILE ////
> '--------------------------------------------
> Call TextFileBuildLoop
>
> '*****************************
> '//// BUILD DHL TEXT FILE ////
> '*****************************
> Response.ContentType = "text/plain"
> 'cSwitch = "Off"
> '----------------------------
> '//// OPEN DHL RECORDSET ////
> '----------------------------
> set oRSc=Server.CreateObject("ADODB.Recordset")
> oRSc.Open cdhlSQLstr, oConn
> '----------------------------
> '//// OPEN DHL TEXT FILE ////
> '----------------------------
> 'Set objTextFile =
> objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
> 'Response.Write (Server.MapPath("textfile.txt") & "
")
> Set objTextFile = objFSO.OpenTextFile("C:\DHL000.txt",2)
> '--------------------------------------------
> '//// CALL SUBROUTINE TO BUILD TEXT FILE ////
> '--------------------------------------------
> cSubSwitch = "On"
> Call TextFileBuildLoop
>
> '*******************
> '//// SUBROUTINE////
> '*******************
> SUB TextFileBuildLoop
> '---------------------------------
> '//// WRITE TEXT TO TEXT FILE ////
> '---------------------------------
> 'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"
> '------------------------------------------------------
> '//// BEGIN TEXT FILE BUILDING - LOOP THROUGH ROWS ////
> '------------------------------------------------------
> If Not oRSc.EOF Then
> '----------------------------------------------------------- --
> '//// THIS PART PRODUCES THE FIRST TOP ROW OF FIELD NAMES ////
> '----------------------------------------------------------- --
> ' For i = 1 To oRSc.Fields.Count
> ' objTextFile.Write oRSc.Fields(i-1).Name
> ' If i < oRSc.Fields.Count Then
> ' objTextFile.Write " "
> ' End If
> ' Next
> ' objTextFile.WriteLine
> ' --------------------- // END // ----------------------------
> While Not oRSc.EOF
> For i = 1 To oRSc.Fields.Count
> '------------------------------------------------------
> '//// THIS PART ADDS QUOTES AROUND THE FIELD VALUE ////
> '------------------------------------------------------
> If oRSc.Fields(i-1) <> "" Then
> tmp = oRSc.Fields(i-1)
> ' If TypeName(tmp) = "String" Then
> ' objTextFile.Write """" &_
> 'Replace(oRSc.Fields(i-1),vbCrLf,"""") & ""
> ' Else
> '---------------------- // END // ---------------------
> objTextFile.Write oRSc.Fields(i-1)
> ' End If
> End If
> ' writes the space between fields
> If i < oRSc.Fields.Count Then
> ' If cSwitch = "Off" Then
> objTextFile.Write " "
> ' End If
> End If
> ' If oRSc.Fields(i-1).Name = "Track" Then
> ' cSwitch = "On"
> ' Else
> ' cSwitch = "Off"
> ' End If
> ' loop through fields / stay on same record
> Next
> ' go to next line in text file / move to next record
> objTextFile.WriteLine
> oRSc.MoveNext
> Wend
> End If
> Set objTextFile = Nothing
> If cSubSwitch = "Off" Then
> Set oRSc = Nothing
> End If
> END SUB
>
> '*****************************
> '//// BUILD NAN TEXT FILE ////
> '*****************************
> Response.ContentType = "text/plain"
> '----------------------------
> '//// OPEN NAN RECORDSET ////
> '----------------------------
> set oRSn=Server.CreateObject("ADODB.Recordset")
> oRSn.Open nSQLstr, oConn
> '----------------------------
> '//// OPEN NAN TEXT FILE ////
> '----------------------------
> 'Set objTextFile =
> objFSO.CreateTextFile(Server.MapPath("textfile.txt"))
> 'Response.Write (Server.MapPath("textfile.txt") & "
")
> Set objTextFile = objFSO.OpenTextFile("C:\NAN000.txt",2)
> '---------------------------------
> '//// WRITE TEXT TO TEXT FILE ////
> '---------------------------------
> 'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"
> '------------------------------------------------------
> '//// BEGIN TEXT FILE BUILDING - LOOP THROUGH ROWS ////
> '------------------------------------------------------
> If Not oRSn.EOF Then
> '----------------------------------------------------------- --
> '//// THIS PART PRODUCES THE FIRST TOP ROW OF FIELD NAMES ////
> '----------------------------------------------------------- --
> ' For i = 1 To oRSn.Fields.Count
> ' objTextFile.Write oRSn.Fields(i-1).Name
> ' If i < oRSn.Fields.Count Then
> ' objTextFile.Write " "
> ' End If
> ' Next
> ' objTextFile.WriteLine
> ' --------------------- // END // ----------------------------
> While Not oRSn.EOF
> For i = 1 To oRSn.Fields.Count
> If oRSn.Fields(i-1) <> "" Then
> tmp = oRSn.Fields(i-1)
> '------------------------------------------------------
> '//// THIS PART ADDS QUOTES AROUND THE FIELD VALUE ////
> '------------------------------------------------------
> ' If TypeName(tmp) = "String" Then
> ' objTextFile.Write """" &_
> 'Replace(oRSn.Fields(i-1),vbCrLf,"""") & ""
> ' Else
> '---------------------- // END // ---------------------
> objTextFile.Write oRSn.Fields(i-1)
> ' End If
> End If
> If i < oRSn.Fields.Count Then
> objTextFile.Write " "
> End If
> Next
> objTextFile.WriteLine
> oRSn.MoveNext
> Wend
> End If
>
> Dim clientIP
> clientIP = Request.ServerVariables("REMOTE_ADDR")
>
> ' define variable
> dim objCDOMail
> ' create mail object
> Set objCDOMail = Server.CreateObject("CDONTS.NewMail")
> ' compose message
> objCDOMail.From = "CLI2-TRAINING " 'this format
> authors the e-mail as a name of your choice
> objCDOMail.To = "CLI2_Admin_...@adp.com;"
> objCDOMail.Cc = ""
> objCDOMail.Importance = 2 '(0=Low, 1=Normal, 2=High)
> objCDOMail.Subject = "Package Tracking ADP4ME Export Notification for
> " & strDate & ""
> objCDOMail.BodyFormat = 0
> objCDOMail.MailFormat = 0 ' CdoMailFormatMime 'use for MS Outlook mail
> objCDOMail.Body = "" &_
> "" &_
> "" &_
> "" &_
> "" &_
> "

The FedEx, UPS and DHL records for
> " & strDate & " were processed successfully.

You may now close this browser
> window.