RevMedia

MSSQL_C Really Does the Work

The same principles can be applied at an even lower level of the bond technology. Ultimately, all access to SQL Server is converted to MSSQL_C calls. This is the C program that links Arev to the network named pipes facility. This takes more work for the programmer but much less for the bond. Connections and data segments must be handled directly in the program but can be released when processing is complete. This frees resources in Arev and in SQL Server which might improve overall performance on a loaded LAN. The source code is littered with MSSQL_C calls:

Equate statements can be used in place of the integer in the first MSSQL_C parameter. Only about 10 calls are used routinely so the numbers are easy to remember. Line 11 grabs the data segment from VERBS. Line 12 checks that the DBNMPIPE network named pipe utility is functioning. Line 13 connects to the server and line 14 sets the current database. The SQL INSERT statements are built the same as the previous example and match the syntax normally generated by the bond. Error handling is slightly different because MSSQL_C returns ample information about server connection problems. These errors are coded in errflag and explanatory text is stored in errstr. Line 46 closes the connection and frees the associated Arev and server memory.

Note that the DBPROCESS and MSSQL_C_DAT data segment variables are necessary for most of these calls. It is possible to have multiple connections open to the SQL Server. This is often done for routines that read the results of a complicated query and update individual rows from that query. Client server projects that rely heavily on MSSQL_C for performance often store open connection variables in labelled commons. This saves a few steps in each program and further improves performance at the expense of reserved server memory (only 42K per connection). This method gets down to the lowest level but requires the highest level of programmer proficiency. The test file took only 16 minutes which is 25% of the bonded file method. This approach is applicable to other Arev bonding tasks like complicated entry windows and custom popups.

SQL Server Bulk Copy

The last method requires an extra step outside of Arev but performs the best. The source records are parsed into text strings with each field separated by a tab (\09\) character. The lines are carriage return/line feed (\0A0D\) terminated. This format is compatible with the SQL Server BCP utility which is used for data loading. The code runs very fast since we do not wait for a response from SQL Server for each row. The combined time for Arev and BCP is only 8 minutes which is half that of MSSQL_C. There are also no server connections established from Arev. The code follows:

Line 40 is the bulk copy utility which is in the DOS path. BCP has numerous parameters that allow it to support various input formats. ASCII text, CSV, and internal data types are supported. An input script details the file layout and specific field data types. This script is not necessary if the data is loaded in character (/c) format which is tab separated strings. In this case, SQL Server converts each field into the proper internal format. BCP is often used to transfer data from one SQL Server to another where internal data formats are normally maintained. The other parameters are explained below:

   test..master_sql     full table name including database in data flow
                        direction, could be out
   c:\sql\master.sql    DOS data file created in Arev
   /c                   character data, separate with tabs
   /U sa                user login id
   /P                   password
   /S server            server name
   /m 1000              number of errors to allow

The errors can be written to a text file with the /e option. This log can be studied for data problems. The /b batchsize parameter is useful with very large imports because it commits rows at a specified interval. Otherwise, all rows must be read with less errors than specified with /m or the entire load is rejected. This transaction approach to data loading is central to the security and reliability that makes SQL Server so popular.

One other note is worth discussing. With many data files to process, consider running the Arev ASCII export on one machine and the BCP on another. Better yet, run BCP directly on the OS/2 system hosting SQL Server. The data will load much quicker when local pipes are used instead of named pipes (no network traffic).

Conclusion

All four data loading methods arrive at the same result. The data is transferred to SQL Server and ready for query and application processing. With a little work, the load time can be reduced dramatically. There are other issues like transaction logging that might effect your implementation. Run a few tests and develop your own approach. If you have questions, post them on Compuserve in the OS2/SQL section of the Revelation forum.