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?

Comments

[Dayvster 🌊]

ANSI SQL is a thing. You do not need to write DB-specific code if you do not want to. The database-independance of EF is overrated at best, Microsoft only supports SqlServer and Sqlite (and Azure stuff) so you're at the mercy of whoever creates the provider, and many of those are not so invested in the Microsoft ecosystem so do an "adequate" job. you might as well write ANSI SQL and be truly DB independant.

Correct, I myself also never fully trust 3rd party maintainers. But for the most part I have not been dissappointed with the big DB providers yet.

And your comment about developing using SQL being slower than EF... no, sorry. Learn the tools you use. Learn SQL and you will be able to write all your EF in SQL just as quickly (or, if slower, writing a query in SQL rather than EF is measured in tens of seconds not tens of minutes)

I still have to disagree. Mainly because with EF you can create an absract repository that will handle 90% of your CRUD operations.

A dev that gets the wrong algorithm and turns a O(1) query into an O(n) one will not be solveable with any number of high-perf servers.

Agree, but we both know that's an edge case.

Coders who only now EF and drag in lists of entities to merge and process on the client make poor performance that would be massively improved if they knew SQL and let the DB do that hard work for them.

Agree.

[Dayvster 🌊]

There's nothing wrong with EF bit it's best used for the simple stuff, fetching a single record or list. When you start to join, or have complex where clauses or need to combine data, then SQL is always the way to go.

if you're looking for performance gains or development time?

I'd argue that an important part of EF and it's key selling point, that you are completely independant from database drivers.

Which in turn ensures that if you or your company ever wishes to migrate or switch or offer your clients alternative databases, you can do so with minimal effort.

[Dayvster 🌊]

Database independance is a false promise. The number of times I have ever heard of anyone changing DB provider can be counted on 1 finger. (that was Oracle to SQL Server, and 99% of the SQL remained unchanged - unless you're getting real fancy, your SQL will not change much, if at all.)

We have not had problems with that yet. However that evidence is purely anecdotal and subjective.

Performance matters

Agree but with a sidenote that developent time matters a lot more in most cases.
A single developers yearly salary could pay for a high performance server 10 times over.

EF doesn't generate bad SQL generally

Sometimes it does to be perfectly fair.

but complex stuff is always best done in the systems native language - and for DBs, that's SQL

Completely agree.

Once you know SQL, you can write a sproc or SQL as quickly as you can EF

Disagree.

For some queries (such as updating a set of records, the SQL can be much faster.

Completely agree.

[Zoltan Halasz]

I agree. My razor pages application uses EF core, it does the job. Here I need simple queries and Updates. The complex part with dapper goes in my WPf app where I have the business logic.

[Dayvster 🌊]

So EF is fine, but just like everyone writing web front-ends should know javascript, everyone working with a DB should know SQL.

And I fully agree with you on that.