Performance of a Database Application

Database applications are everywhere, on the web, on PCs, on our mobile phones. They are so common and important that we learn CRUD tutorials when meeting any new piece of technology: JS Frameworks, Node.Js, .Net, Php, Mobile development etc.

Especially in the business world where I come from (but also e-commerce and general internet sites), the operations of an application are highly dependent on a database, and the extracted data.

In the past one year I have been working on small or mid-size applications for business, automating processes in finance department. Main technologies used are C#, WPF/MVVM, Dapper ORM and MS SQL Server database(stored on Azure).

In the learning process, my lessons to increase the general performance are:

1. Using Dapper instead of Entity Framework. This ensures that all operations on data are controlled by stored procedures written by me, where I control the performance of Queries. Yes, you can see correctly: absolutely all table operations are done by manually written SQL stored procedures. Well, MS SQL server lets you generate some SQL code based on the tables/queries constructed, but all the evidence shows that Dapper with its manually written Queries is way faster than Entity Framework. See: https://www.youtube.com/watch?v=Et2khGnrIqc

2. All my applications contain some movement of data around existing tables. One of the greatest discoveries is the SQL Bulk Insert, that reduced significantly my writing time to the database. See: https://stackoverflow.com/questions/18841000/sql-bulk-copy-insert-in-c-sharp#18927213

3. Using of async/await in C# code. This allows some long processes to be executed asynchronously, which increase the overall performance of my app. I am still studying this, but there are some good results in my app. https://www.c-sharpcorner.com/article/async-and-await-in-c-sharp/

4. Being more responsive/suggestive on the user interface. Just implementing a Progressbar, together with a calculation/display of remaining time of the operation, gives the impression to the user that things are under control and executing properly.

5. Bulk upload of data to the app. I have excel files as input to populate some of the tabular data of the database. For this, I use VBA to insert data to the database. (the excel sheets are protected and VBA locked with password).

6. Database design, using relationships, normalization. I don't need to teach this to most of you, this is pretty standard in DB apps, and is well documented on the internet also.

7. Using and filtering Lists/Collections. I tend to use a combination of lists in C# - with data populated from Dapper (see point 1), and then filter the lists with LINQ, which is a very useful language feature of C#.

The above ideas are used currently in my applications, but, for sure, with more experience I can refine them, as I am still in progress of learning and experimenting.

My future study areas to further increase the efficiency of my database applications are:

1. learn Entity Framework Core, and perhaps use it sometimes, especially in Asp.net core projects;
2. better design of app flow and structure;
3. using excel file uploads, not from VBA, but by directly uploading excel files to extract data in the app;
4. application of async/await even more frequently;
5. substituting the current legacy ReportViewer system with something newer/more efficient reporting solution;
6. maybe considering an in-memory database also in some cases;
7. use a SPA or Blazor for Web applications - I'm experimenting with these.

What do you think, what's your approach to increase the efficiency/speed of your DB apps?