Shedding Some Light

We have customers calling occasionally to share with us their observation that our application we sold them is running slow. In the cases where we know the application is performing fine at other locations, we have to begin the process to determine what is different about their environment and circumstances and how this is different from the average customer who has our application working optimally. We are basically looking for things like slow computers, slow networking, over worked “server” computer, and flooded network traffic. As developers, we understand there are numerous factors in performance of an application and interaction with data. While data size can make an impact, I’ve seen large files perform just fine for our customers.

Note: All the questions assume you have done the standard performance diagnostics with your application using the Visual FoxPro Coverage Profiler to identify and remove all the slow parts. All queries (both SQL Select in-line code and local views) for local data are all optimized and checked using SYS(3054). The same goes for remote views, SQL Passthrough and CursorAdapter code for SQL server data (any SQL Server, PostgreSQL, MySQL, etc.) using the performance tools they provide.

I generally like to focus on the other external factors when it comes to performance.

Here are some of the questions I ask:

1. What is the network configuration with respect to the workstations and networking speed of each connection? How fast is the network card on each computer: 10 mbit, 100 mbit, or 1000 mbit? 1000 mbit networking technology is inexpensive, yet we run into 10 mbit connections deep into the 21st century. It is simple to math to understand the impact.
   .
2. What version(s) of Windows are running in your location on each computer? We have seen some performance issue with computers where there is an XP machine accessing the networked computer that runs Windows 7 or Server 2008/2012.
   .
3. How much memory is on the “server” computer (Windows Server or workstation where software is installed in peer-to-peer)? What operating system is on the “server” computer?
   .
4. Is the “server” computer 32-bit processor or 64-bit processor? What about the workstations?
   .
5. How fast is the hard drive where the application is installed? Do you know if the drive is a 7800 rpm drive or better? We do find that slower drives do create performance problems in peer-to-peer networked situations.
   .
6. Are you running the application on a server computer or a standard/regular workstation computer? Also, is the main computer a part of the normal operations or does it sit in the back and not get used by anyone?
   .
7. If the main computer where the application is installed is a Windows Server, is it running anything else like Exchange, SQL Server, or anything else? How many processors are on the server?
   .
8. What ant-virus or security software is installed? Are the data folders excluded from the scans? If I was given $10 each time anti-virus software was being overly aggressive with the data, well, my retirement account would be in excellent shape.
   .
9. Where are the Windows TEMP files being written for the workstation? They should be pointed to the local computer, not a server if one is in use.
   .
10. Are any of the other machines connected to the network through a wireless connection, or are each of the other computers wired into a switch/hub using an Ethernet cable? We do not support wireless networks for our applications, the computers must be hard wired. Wireless connections, while reliable for surfing the Internet, are not reliable for database applications.
    .
11. What is the brand and model of the network switch/hub and how fast is it? We often run into old infrastructure that has worked for years/decades, so why would it stop working today? Hardware breaks eventually, but for some reason people think it only breaks for other people.
    .
12. What other programs are running on the “server” computer besides our application when our application is running? I am looking for the memory hogs and the other application that have lots of background processing. People are amazed how much memory Web browsers consume when you leave them open for days and have tons of tabs set up to refresh for the news junkies.
    .
13. Drive mappings or UNC to access main computer from workstation? I am never sure if this impacts performance, but it can help me generally get an idea of when someone sets things up themselves or have a professional IT group set things up.
    .
14. How many drive mappings on the workstation? Another question to see if a professional set the computers up. Also, it helps me understand if there might be other applications accessing the network.
    .
15. When was the “server” hard drive with our applications data defragmented? What is the level of defragmentation? Defragmentation is not much of a problem with larger drives now, but can obviously impact the data if it is scattered all over a drive.

Other things that cause slow performance:

1. How long has the application been in service? How big is your application data? Inefficient and non-optimized queries obviously run slower with bigger data sets. I think all developers have overlooked this once or twice before, and production is going to reveal this fairly quickly once the users start recording information.
   .
2. How frequently do you run the reindex routine? Indexes do get corrupt from time-to-time in the natural course of using the application. We know people who go months without reindexing. Good for them. On average, our reindexing routine takes a few minutes and we are not in the position where our applications are running 24×7 and cannot be down for this brief time. So we recommend to some of our customers to reindex daily, and others on a less frequent schedule.
   .
3. Bad Ethernet wires or a bad port on the hub/switch often cause retries sending data over the network. Difficult to track down when more and more computers are used on the network.
   .
4. Any chance you are streaming music through the network from Pandora, Spotify or another music service? Anything that creates network traffic will slow a networked application down.
   .
5. Is there anything else that is running over the Internet to a computer other than standard browsing or file sharing? I’ve actually found a couple of customers who had people playing games over the network, watching their latest soap opera and Netflix binge watching at lunch, and even during work hours. Does that hurt application performance, um, sure.

Anything else we could be sharing with the local IT people? What are your experiences?