2

We have a point-of-sale system that was developed using ado.net, our current concern is to make the application real fast in creating transactions (sales). Usually there are no performance concerns with high end PCs but with with low end PCs, the transactions take really slow.

The main concern is on saving transactions which usually calls a lot of stored procedures for inserting records within a single transaction. This usually takes time on low end PCs. We need to improve the performance since most clients will only use low end PCs to act as cashier machines. One solution we are thinking is to use entity framework for data access. The entire project is written using ado.net and development time if we shift to entity framework would be alot. Any suggestions?

whastupduck
  • 200
  • 1
  • 11
  • Are all those stored procedures etc. useful in any way at the moment the transaction is registered? Is it needed to get the receipt? If not: Can't you just move them to back-end processing? – Luc Franken Jul 05 '13 at 15:09
  • Yes they are all needed for the receipt – whastupduck Jul 05 '13 at 15:17
  • 3
    Ok, so where did you measure the most delay? In the end it comes down to how much to process and the processing power so somewhere you should reduce the amount of work to be done or the amount of power to do the work. If all steps are really required when making the receipt then the only option is to benchmark and improve small parts. Though most of the time fast improvements can be made by removing steps (like lowering stock) to a later moment so it doesn't influence the user experience. – Luc Franken Jul 05 '13 at 15:23
  • Okay thanks, I'll need to test some components aside the database connection – whastupduck Jul 06 '13 at 00:49
  • If it's slow with ADO.Net, it's going to only be slower with EF. AS others have stated, EF is an abstraction on top of ADO.Net. The exception being if EF is writing better SQL than your current code. – RyanJMcGowan Sep 01 '13 at 08:59

4 Answers4

6

I would like to point out that Entity Framework (full name: ADO.NET Entity Framework) is an ORM (Object Relational Mapper) that uses ADO.NET under the hood for connecting to the database. So the question "should we use ADO.NET or EF?" doesn't really make sense in that respect. Unless you re-architect your application, adding EF to the mix is simply adding another layer on top of ADO.NET.

I sincerely doubt that ADO.NET is the source of your performance problems, however. It sounds like the problem exists in your stored procedures themselves.

I think Luc Franken's comments are on the right track, though. You need to measure and determine exactly where the delays are happening. Then concentrate on fixing exactly that problem. Anything else is groping around in the dark.

Community
  • 1
Eric King
  • 10,876
  • 3
  • 41
  • 55
3

If you are looking for performance stay away from EF. It is the slowest ORM out there and uses a lot of memory to keep the database metadata. Most benchmarks out there show that -

Otávio Décio
  • 5,299
  • 23
  • 34
  • Would you suggest we keep the ado.net data classes? – whastupduck Jul 05 '13 at 15:18
  • If by ado.net data classes you mean DataTables and such, I don't think so. These are the most bloated classes I know. If performance is your problem I would start looking at microOrms such as Dapper that runs this very site. – Otávio Décio Jul 05 '13 at 15:29
  • 4
    While I have no objection to stating that "EF is slower than ADO.NET", I disagree with absolute statements like "slowest ORM out there". Consider [this benchmark](http://blog.staticvoid.co.nz/2012/3/24/entity_framework_comparative_performance) which shows roughly equivalent performance, and [this one](http://www.outofmemory.co.uk/entity-framework-5-dramatically-faster-in-net-4-5/) which shows why there might be different results in different benchmarks. It very much depends on what the test consists of. – Bobson Jul 05 '13 at 17:49
  • 1
    ADO.Net is abstracted by EF. I doubt EF is the "slowest out there" because there's too many factors such as what type of query is being called and how well an ORM can bundle a database query into one call. The consensus I've been witnessing in a rather lengthy LinkedIn discussion is to just use your favorite ORM, test it, find the queries that are unacceptably slow and optimize just those queries with better ORM calls or replace those methods with ADO.Net. – RyanJMcGowan Sep 01 '13 at 08:42
3

You say you are calling a "lot of stored procedures". If every call includes a seperate trip to the database, that is your performance issue, because trips to the database are always expensive, no matter what they do. You should have one stored procedure to save a transaction. If that procedure has to call other procedures, fine, as long as you don't have to make a seperate trip to the database.

KennyZ
  • 161
  • 3
  • No, we only have one connection to the database and all stored procedure calls use this connection. We have different stored procs because we save in a lot of tables. – whastupduck Jul 06 '13 at 00:46
  • 1
    I could be wrong, but I don't think opening and closing the connection is the only expensive part of running a stored proc. I do know for sure that there is no limit of one table update per stored proc. You might try just combining two of those and doing some before and after profiling. – KennyZ Jul 08 '13 at 13:12
0

How necessary is it that each transaction require a database call?

At what point is the db insertion being performed? Is there one call per transaction or is it inserting with every addition to the order causing a major lag throughout the user's interaction?

Can the db call be handled through private HTTP requests to a dedicated server on the client's own LAN?

Perhaps a better approach would be to save the transactions in a repository and post them to the server all at once using the Unit of Work pattern that is triggered as soon as there is a pause of more than 5 seconds. Or perhaps the slow client machines could post to a service that can handle the database insertions.

What else could be slowing the machine? Is the GUI processor intensive? It's a POS, not a graphic designer's portfolio piece.

RyanJMcGowan
  • 103
  • 4