5 Reasons Your Applications Are Slow

Top 6 Reasons Your Applications are Slow

Jeff Garbus,  Alvin Chang 

We spend a lot of our lives tuning applications that people complain are too slow. In no particular order, here are some of our findings.

Poor indexing #1 – Unused Indexes, Missing Indexes can cause problems

While I’ve said, “in no particular order,” I do have to say this one is usually first. When applications go through Q/A / Stress test, there is often a lot more horsepower than there is data. As a result, the memory and CPU combination mask the otherwise bad performance. Once the application hits production, larger volumes of data are not managed as effectively.

On the plus side, you can almost always add an index (or indexes) without causing other application side effects.

Warning: Do NOT automatically add indexes as recommended by a DBMS’ tuning advisor; they often miss opportunities, and also often significantly over index by recommending multiple similar indexes rather than one enveloping one.

Be wary of overindexing as too many indexes can also create overhead that will cause processes to slow.

Bad queries #2 – Too much data returned by a query

Sometimes you are simply bringing too much data back from the database to the front end. I saw a search recently that brought about a half million rows of data back to the end user. I asked, “What is the user going to do with that much data?” Answer: “They are going to look at the first few rows and refine the search.”

This unnecessarily stresses the disk CPU, memory, and the network.

Easiest solution: Bring back only the data the user is going to work with. Perhaps the first few hundred rows. Save tie, disk resources, and network resources.

Bad queries #3 – Overuse of temporary tables

Many applications use temporary tables incorrectly or are wasteful with them. For example, they are used

  • When the programmer wants to avoid joins (which the server is very good at!);
  • Are filled with lots of data, then rows are deleted (why load the in the first place?);
  • Or too many columns are used (why select * when the columns aren’t being used?) – this increases network bandwidth, as well as making the table unnecessarily big
  • Joining temp tables is another way developers often misuse server resources. Without indexes, this is very costly

 

Avoid temporary tables

 

Bad Queries #4 – Attempting to do it all in one Giant Query.

Sometimes the opposite can also be true. When attempting to write a query for a process, Developers can get stuck in the mindset that a single query can solve all possible conditions of a query.  This leads to large complicated queries that in addition to being difficult to decipher. Can also generate excessive numbers of worktables as it attempts to place large subsets of data into worktables.

Large Reports #5 Combine reporting and transactional activity

It is very common to allow reporting off highly transactional databases. The problem is that reporting creates shared locks on resources, and transactions can not modify the data while the locks are held. In addition, reports are often ad hoc, so that the load on the server is unpredictable.

Easy solution: replicate production data to a reporting server. If replication or other high availability is unavailable, use dump/load to keep day=old data for reporting purposes (this is often sufficient).

#6 Don't allow direct downloads of data

Some companies allow “super users” (also sometimes called “analysts”) to download production data, real time, to applications like Microsoft Access. In addition to being a likely security violation, this also creates blocking issues for the online users.

Solution: Data replication, as above.

 

About Jeff and Alvin.

Jeff Garbus founded Soaring Eagle Consulting 20 years ago, and Alvin has been his right hand for almost 30 years now. Together they have authored or coauthored 20 books and dozens of articles on Database Management. Soaring Eagle Consulting is an On Shore HIPPA and PCI compliant remote database management company that is available for projects and consulting work on Architecture, Performance and Tuning, Scalability, application development, migrations and 24x7 full operational support. Do your DBAs need a best friend? Jeff, Alvin, and the On Shore GURU level database team are here to help you!