Today we are talking to Tornado’s Chief Technical Officer, Todd Niec. Todd has been with Tornado Technologies for over 17 years. As our CTO, Todd is highly knowledgeable in programming, promoting security, and speeding up a database. We wanted to pick his brain on common business owner questions that team Tornado often receives. Our President, Mark Roshon will be interviewing Todd to better understand and answer these common questions.
The Interview
Should you update an old database?
Mark: “Hi Todd, I talk to many businesses that are using a program with an old database or old technology. Sometimes they are running old Access programs and it’s running fine. However, they are concerned about whether they should continue using it. Would you recommend that they continue using the old program or should they upgrade?”
Todd: “Certainly it depends on the situation. If the program that you are using does not require changes, then Access is usually fine. The program won’t suddenly stop working one day. On the contrary, if the program you are using requires changes and is key to your business, then it would make sense to upgrade to new software. Once you update your program, there are typically other advantages. Such advantages include storing more data and being able to access data in new ways such as web interfaces. If the program is a minor part of your business that is not likely to change, I would not worry about upgrading it.”
How can you speed up a slow database?
Mark: “I get this question a lot. Businesses have a database that starts to slow down. They aren’t sure how to speed up their database. Is there anything you would recommend to speed up the system?”
Todd: “Well, there are lots of options. In short, it depends on what the slowdown is coming from. The first and easiest resolution to consider would be to run the database on a faster machine or a machine with larger memory. That’s kind of the quick and dirty. It may not be super cheap but it’s also in a way that will speed things up at least partly and with very little effort. It’s usually pretty safe and easy to do. After that, it takes some expertise to diagnose the problem and solve it. It could be that the table usage has changed from the original design. This might require a change to the design to make access to those tables more efficient. Some ways to speed things up include adding additional indexes which is very easy to do or materializing views. All in all, I would recommend reaching out to a professional.”
Will indexing help speed up a database?
Mark: “I’ve heard that indexing can increase database performance. Does indexing really help that much?”
Todd: “It can help enormously. That doesn’t mean that it’s always the solution. An example where indexing could help is if you’re randomly searching through a gigantic table for a non-indexed value. If that table is relatively small that may go very fast. But there are times that the table grows too large, causing a huge bottleneck and very suddenly slowing down that database. Adding an index can completely speed that up significantly. It can be much faster than it had been originally even when the table was small. At times indexing can speed up a database tremendously. However, I wouldn’t always count on indexing to create a faster database.”
Should you keep your SQL Server up to date?
Mark: “Should you make sure that you’re using the latest version of SQL Server, even if everything is running fine?”
Todd: “Yes. Older versions of SQL Server can be vulnerable to hacking. You want to be using a version that is being actively supported and patched by Microsoft. Luckily, most features of SQL Server are backward compatible. What that means is that you can upgrade a project to a new version of SQL Server with virtually no changes to the system. It’s just a matter of deploying a new database and copying data into it and things like that. So that might be a couple of days’ worth of work but it’s not usually a major rewrite or anything like that.”
Stored procedures?
Mark: “Several years ago, DB developers used stored procedures to speed things up, is that still true today?”
Todd: “There are reasons to use stored procedures at times, but generally not for speed. In the past stored procedures could improve speed in cases where calculations were performed on the data from the database. For example, the database might store all sales you have ever completed, and you just want to view the quarterly totals. The database needs to pull out every sale and total it up by quarters, pull out 10,000 records and come up with a few dozen records. Back in the day, this took a long time to transfer the data on the network. These days networks have become so fast and transferring data takes minimal time. Network traffic is not the bottleneck, the CPU and memory usage are bottlenecks. Thus, these days it makes sense to pull all data off the database machine and have a different machine crunch the numbers. Nowadays stored procedures are usually not needed and could be detrimental for speed.”
To be continued…
Stay tuned for the second part of Todd’s interview where he answers more of Mark’s commonly asked questions. Such questions including what is a sanitized query? Is Oracle a good fit for most businesses? What do you think of My SQL as a database?
About Us
As a custom software Cleveland company, Tornado Technologies offers a wide range of solutions for businesses large or small. Whether it’s mobile app development, web app development, material optimization, or legacy application support, the Tornado Team is happy to work with you and your business.
