2

Yesterday on one of SO chats I've been told I should never connect to database directly from the application and rather use DAL.

I've been told that: 1. Using something in the middle should improve the performance of app-db communication (which I hardly believe, since there's an overhead coming from additional layer between them) 2. It is more secure (in what terms?) 3. It gives better scalability 4. It allows cross-platform client application (not applicable, since it's written in C# and is only for Windows)

The biggest problem with current application is that 40-50 clients connected to database make it slows down massively.

So, my question is: how DAL can actually improve the performance(my main concern) of my system (in terms of server resource usage and client applications speed)?

  • Profile your application and see what's really going on under the covers. – devnull May 19 '15 at 7:21
  • 1
    Just nitpicking, but: not applicable, since it's written in C# and is only for Windows is not correct anymore. C# (or better, .Net) can be used in apps for linux and mac, too. – germi May 19 '15 at 8:41
  • What kind of app is this? Web app? Desktop apps connection to a central database? Is this over the internet or in-house? – JacquesB May 19 '15 at 8:46
  • It's a desktop client app connecting to central database within local network (in some cases it's single client app connecting to a database on the same workstation) – pzaj May 19 '15 at 8:51
  • 1
    I think it would help your question if you explain exactly what you mean by "DAL". The answers show there seems to be some confusion over your use of the term. – Eric King May 19 '15 at 15:08
0

Whether you use a DAL or not your application is still going to directly interact with the database.

It is generally just good practice to structure your code in such a way that things like data access is in a centralised "place" in your codebase.

I also generally abstract any data access to a set of methods defined on an interface.

If and when you decide to change your database you don't have to scoure you entire codebase for all the data access code in order to update it all.

Also by using an interface approch to abstract away the DAL code you can more easily write unit tests for the code that consumes the DAL output.

It's just good design to work this way.

  • Well, I've been told I should definitely use WCF, but I find it too much overhead to only communicate with database. I do use a wrapper around SqlClient class to create a single entry point for all database communication (I only use Stored Procedures). Do you believe I should change the approach and use a n-tier architecture? – pzaj May 19 '15 at 8:07
  • WCF is overkill if the app and client are on the same workstation. All Database interaction should be a DAL project. Then use a different connection string depending on the location of the database to connect to (local/remote) if it's all on a LAN. – Jammer May 19 '15 at 9:11
  • Simply wrapping the SqlClient still leaves DAL code littered around the codebase which isn't a good practice. – Jammer May 19 '15 at 9:12
  • Well, would you mind pointing me to an example implementation of DAL? Because I quite struggle to figure out how to implement it the way the code is not spread all around the code. – pzaj May 19 '15 at 10:31
2

You should use a DAL, but not for the reasons stated :) You performance problem will probably not be fixed by introducing a layer of indirection (even if it might be desirable for other reasons). You should look into what actually causes the performance problem. Some common problems:

  • The n+1 antipattern which causes far to many database queries. E.g. if you fetch a list of 100 items from the database, this should require one query, but if you by mistake make an individual query for each item, you suddenly get 100+ queries, which will kill database performance. You can detect what queries are actually executes via the database profiler.
  • Clients hold onto db connections too long, which causes the connection pool to be depleted. Eg. an open SqlConnection is stored in a field rather then closed immediately after use.

The .net framework already include a low-level DAL (classes like System.Data.SqlClient.SqlConnection if you use a SqlServer) which among other things handles connection pooling. This is pretty important for performance, and there is no reason not to use these classes to access the database.

I would guess you already use this layer, and you friend is taking about an additional layer of indirection between the .net data access library and the application logic - perhaps an ORM like the Entity Framework or NHibernate, or a hand rolled equivalent.

This will not help you with performance, security or scalability. It might make the application simpler and more maintainable, but will not solve your immediate problems.


Edit: OK you are talking about a web service layer. And you have desktop app clients connecting to a central database over a local network. So you are talking about turning a two-tier application into a three-tier application. This is not going to help you with performance problems. Rather it will just make it worse due to the overhead of an additional layer. Introducing a middleware layer might have other advantages though. For example you insulate the clients from the database which may improve security. But if you are going in that direction you might consider using a wep application instead, which have the same advantages, and is much easier to deploy and instrument than desktop apps.

  • indeed, you're right, as I stated in my comment to an answer above, I wrapped SqlClient with my database handler to centralize database access. What I was talking about referring to DAL was a middleware (WebAPI, WCF). I can't agree using it would solve problems, therefore I asked my question :) Your answer does clear everything for me, since You're right. The application must be re-designed anyway, since it indeed does way too many queries and hold onto connection too long. The whole discussion started with my question about MySQL vs SQL Server and then "you access db directly? Don't" – pzaj May 19 '15 at 8:26
  • Okay, middleware like WebAPI/WCF will definitely not solve you performance problems. You should only introduce such a layer if you really need it. – JacquesB May 19 '15 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.