This is an additional post on the perils of network performance, hardware, and file placement as related to Dynamics GP’s client/server processing algorithm.

While I could use posting, reporting, or data entry as an example, I will use SmartList as an example, since it provides a generic platform for non-technical analysis.

Let’s consider a simple action in Dynamics GP – the version is irrelevant for this scenario, other than the fact that SmartList was introduced in early versions of Great Plains Dynamics and CS+ and is commonly used.

  1. A normal system – our baseline:
    a. You request a Payables Transaction SmartList.
    b. The request is sent to the SQL Server Database Engine.
    c. Relevant data returns to the user’s temp folder in small packets (25 records each) for processing and temporary storage pending query completion.
    d. Results display to screen for further user interaction.
  2. A normal client with active antivirus scanning enabled
    a. You request a Payables Transaction SmartList
    b. The request is sent to the SQL Server Database Engine
    c. Relevant data returns
    d. Packet 1 – antivirus scans packet to ensure it is safe, then writes and stores in temp
    e. Packet 2 – antivirus engine scans packet
    f. Packet 3-400 (assuming default ‘first 1000 records’)
    g. Results display to screen
  3. A client with a low-bandwidth NIC (100Mb, Wireless)
    a. You request a Payables Transaction SmartList
    b. The request is sent to the SQL Server Database Engine
    c. Relevant data returns, but is unable to process as fast due to data backed up waiting its turn to get through. Think of the data as water, with the server 1GB+ NIC as a garden hose. With the 100MB NIC we are trying to force all the water passing through the garden hose into a drinking straw. Once the water is shut off (SQL processing finished), the hose will empty, but held back by the diameter of the straw.
    d. Packets arrive and results display on screen slowly

4. A client (or server) with slow disk drives or drives busy with other activities
a. You request a Payables Transaction SmartList
b. The request is sent to the SQL Server Database Engine
c. Query runs on server, writes to temporary tables, but both reading and writing data are slowed by the rate the server disks are able to read, write, and process the request. This is most noticeable in 5400RPM drives, but still noticeable in 7200RPM HDDs. If multiple databases are written to simultaneously, performance drops even more. That’s why data and log files, and tempdb should be on their own physical drives or drive arrays.
d. Relevant data returns, but is again hindered by a drive that cannot write fast enough to keep up with the data returned, or writing is hindered by the disk being busy writing other processes to disk, calculating spreadsheets, rendering drawings, etc.

5. Client with roaming profiles
a. You request a Payables Transaction SmartList
b. The request is sent to the SQL Server Database Engine
c. Relevant data returns to the temp folder on a different server
d. Client queries and processes data in the temp folder across the network. This will take many bundles of data, and we’re now shuffling data to another server.
e. Data returns to client and displays on screen for further processing

As you can see from the above examples, there are numerous ways in which data can be slowed down, backed up, or bottlenecked. Keep in mind that any combination of the above scenarios is also possible.

It’s also easy to alleviate many of these trouble spots.
In scenario #2, create Antivirus exceptions for known temporary files.
* In the %temp% folder:
* TNT*.dat
* TNT*.idx
* TNT*.tmp
* In a %temp%\TNT* subfolder:
* ASI*.dat
* ASI*.idx

In scenario #3, make sure the NIC is a minimum of 1GB, and any hubs (old) or switches support 1GB throughput. Most new computers already comply. If you’re connecting to your network on a wireless connection, plug in a network cable. If your notebook doesn’t have a NIC port, there are USB adapters.

In scenario #4, the faster the drive, the better the performance. A Solid-State Drive is fastest. Increased productivity will quickly offset the additional cost of an SSD.

In scenario #5, if your IT requires roaming profiles, make sure your network is as fast as possible, but ask if it’s available to store temp files locally rather than on another server.