I’ll give an example. At my previous company there was a program where you basically select a start date, select an end date, select the system and press a button and it reaches out to a database and pulls all the data following that matches those parameters. The horrors of this were 1. The queries were hard coded.
-
They were stored in a configuration file, in xml format.
-
The queries were not 1 entry. It was 4, a start, the part between start date and end date, the part between end date and system and then the end part. All of these were then concatenated in the program intermixed with variables.
-
This was then sent to the server as pure sql, no orm.
-
Here’s my favorite part. You obviously don’t want anyone modifying the configuration file so they encrypted it. Now I know what you’re thinking at some point you probably will need to modify or add to the configuration so you store an unencrypted version in a secure location. Nope! The program had the ability to encrypt and decrypt but there were no visible buttons to access those functions. The program was written in winforms. You had to open the program in visual studio, manually expand the size of the window(locked size in regular use) and that shows the buttons. Now run the program in debug. Press the decrypt button. DO NOT EXIT THE PROGRAM! Edit the file in a text editor. Save file. Press the encrypt button. Copy the encrypted file to any other location on your computer. Close the program. Manually email the encrypted file to anybody using the file.
First of all, lack of ORM isn’t bad. It’s not a good or bad thing to use them out not use them. What’s bad is not sanitizing your query inputs and you don’t need an ORM to do that.
I think the worst thing I’ve seen is previous devs not realize there’s a cost to opening a DB connection. Especially back when DBs were on spinning rust. So the report page that ran one query to get the all the items to report on, then for each row ran another individual query to get that row’s details was probably one of the slowest reports I’ve ever seen. Every DB round trip was at minimum 0.1 seconds just to open the connection, run the query, send back the data, then close the connection. So 10 rows per second could be returned. Thousands of rows per page has people waiting several minutes, and tying up our app server. A quick refactor to run 2 queries instead of hundreds to thousands and I was a hero for 10 min till everyone forgot how bad it was before I fixed it.
It’s the round trips that kill you.
Oracle drivers for .NET are fun. Have a user client application which uses quite a lot of data, but a few thousand rows are fetched some queries. It’s way too slow for any larger query, turns out for the batch query kind of work we do, the default FetchSize for Oracle is just a performance killer. Just throw it to 128 MB and it doesn’t really hurt at all.
Worst thing i’ve seen though, apart from the 150 line long dynamic sql stored in our database, was probably a page in our program that loaded about 150 rows from the database. Normally we do create a new connection for each query, but it’s fine since Oracle has a connection pool. Whatever millisecond is trumped by the round trip. But imagine a UI so badly written, it did 4 separate database queries for EACH row it loaded into the UI list. Useless things like fetching a new ID for this row in case it is changed, reading some data for the row i think, and more. Thing took a solid minute to load. There was so many bad patterns in that page that even during the PR for improving the speed it was just dealing with a mess because you couldn’t just rewrite the entire thing, so they had to make it work within the constraints. Horrible thing to work with.