0

Appreciate this is a rather odd question, so I will try to clarify as much as possible. Please also be assured this is a question purely for my own education, I'm not about to rush off and do crazy things in our software on the back of it.

I have a customer requirement for a transaction time of <10ms on a system that is based around an SQL database - in our specific implementation it is Oracle DB. I'm aware that this is not a useful or meaningful requirement, so with my business hat on I'll be dealing with that. I fully expect that the requirement will be revised to something more useful and achievable.

However, I am curious on a technical level. Could you squeeze transaction time on an SQL DB down below 10ms? Lets be generous and say this is pure SQL execution time, no comms, no abstraction layers etc. Right now, running select 1 from dual on one of our systems gives a reported execution time of 10-20ms* and I'd assume that's about the simplest query possible. What if anything might you do to reduce that time (a) within Oracle/SQL or the server environment (b) by making a different tech choice? I'd assume maybe a higher clock speed on the CPU might help, but I wouldn't bet on it.

*EDIT: turns out I had erroneously included the fetch time in my measurement above, so the actual execution time for a trivial query is generally <10ms in our implementation. So in a sense I've answered the question, it is viable. But that doesn't invalidate the discussion below.

New contributor
SimonN is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    It's plausible that 10ms is the resolution of the clock, and it took much less time – Caleth Jun 30 at 15:51
  • Is there a requirement which says that you need a multi-user database? – Philipp Jun 30 at 15:58
  • 2
    In my experience, less than 10ms response times is associated with in-memory stores. The ping on my Internet connection is greater than 10ms. – Robert Harvey Jun 30 at 16:02
  • 2
    @JoelHarmon I am correctly using the word "meaningful," as in "having a meaning." I think you have taken it to mean "consequential" or "impactful." And I do see what you are getting at. But it misses the point. If there is a dispute over performance and payment, and it goes to court, you will need the terms to be unambiguous, or else the contract could be voided by a judge. And as it is currently written, the requirement is ambiguous. They could decide not to pay if even .0001% of transactions take longer than 10 ms (which IMO would be absurd). – John Wu Jun 30 at 22:09
  • 1
    @JohnWu My comment wasn't intended to be directed toward you. The querent referred to this as "meaningless" in both the question and comments, and said he "wouldn't be implementing this". My intent was to point out that ignoring this could have very serious consequences. I agree with all of your technical commentary on clarifying this requirement. I don't have the background to know what a judge or legal system might say about this, especially without knowing the jurisdiction. I think we are agreed that the querent must seek clarification on this. – Joel Harmon Jun 30 at 23:18
1

I am working on an implementation of SQL in C#, serving simple web pages, and a typical transaction ( which generates a page of HTML ) typically runs in 0.1 ms to 0.5 ms.

So it should be possible. Of course it helps if the SQL is pre-compiled as a stored procedure, and it depends what you are doing.

Another benchmark : it can summarise 5 million rows from a base table in well under one second. Microsoft SQL Server ( running on the same machine, my personal laptop ) is about twice as slow, and dramatically so for adding rows in T-SQL loop ( where it can be 20 times slower ). That's about all the performance comparison I have done so far ( and it may not be "fair" ).

| improve this answer | |
  • 1
    Interesting comparisons thanks. Have you designed your implementation specifically to minimise transaction time or is it just a byproduct of it being (presumbly) far lighter-weight than the big names? – SimonN Jun 30 at 19:04
  • Well yes, I have tried to minimise transaction times, I do fairly minimal optimisation, I only run one transaction at a time ( for now, at any rate ), and the compilation process is very simple and fast using C# closures. Hard to elaborate much in short comment on the design philosophy. – George Barwood Jun 30 at 19:10
1

Is a transaction time of <10ms for an SQL database viable? If so, under what conditions?

This is hardware specific, operating system specific, RDBMS specific, and SQL query specific.

Do you have a 128Gbytes of RAM computer with dozen of cores and terabytes of SSD? E.g. some AMD Ryzen Threadripper 3990X based motherboard with 512 Gbytes of RAM? Do you use SQLite? On such a computer (with Linux and PostGreSQL) with a 10Gbytes sized database and no rotating hard disk, you might achieve such a transaction time most of the time.

Is the database server on the same computer as the database client? Are they on the same continent? You could set up a PostGreSQL or MariaDB server running on some Linux VPS from OVH in France, and have the client application run in California (USA) on some Microsoft Windows machine connected to the Internet.

If your computer is on the moon (e.g. some autonomous robot there) and the database server on Earth, the electromagnetic signal from moon to earth takes more than 10 milliseconds.

If both database application and client application runs on the same hardware in the same room, without any rotating hard disk (so SSDs at least), you could usually achieve a 10 milliseconds delay.

Do you need a formal methods based proof that your transaction will always last less than 10 milliseconds?

You might be interested by Frama-C .... But you could spend half a million € (or US$) to get a formal proof, related to WCET. If you run an Oracle RDBMS, you could need to sign some NDA with Oracle. See also this draft report.

See also this answer.

I have a customer requirement for a transaction time of <10ms

Do you also have a good lawyer checking that requirement and your contract?

My recommendation is to add a clause such as "in 90% of the cases, the SQL query SELECT passwd_encr FROM tb_password WHERE passw_userid = (SELECT user_id WHERE user_email = $1) ORDER BY passw_mtime DESC LIMIT 1; will be served in less than 10 milliseconds", and to mention your database schema and the hardware running the application in your contract. BTW, that particular query is taken from this example.

Notice that a cheap RaspBerry Pi computer can run an SQL application. Most Android mobile phones applications are running SQLite.

PS. GNU/Linux and PostGreSQL or SQLite are open source. So in principle you might use a top500 supercomputer where your performance is achieved.

| improve this answer | |
  • "Do you have a 128Gbytes of RAM computer with dozen of cores and terabytes of SSD? E.g. some AMD Ryzen Threadripper 3990X based motherboard with 512 Gbytes of RAM?" No I don't, but would it help if I did? Would more RAM or more cores or faster storage reduce the time to handle a trivial query that requires no disk access such as the one I quoted? Its these factors I was interested in understanding (among others). – SimonN Jun 30 at 19:33
  • Intuitively, of course yes. But what would happen to you if it didn't? – Basile Starynkevitch Jun 30 at 19:33
1

Yes, absolutely it should be possible. From the SQL landing at the server, to a resultset exiting an elapsed time of 10ms should definitely be attainable for a wide swathe of OLTP-type queries, or stored procedure executions, even with cold caches. The equivalent of your trivial query on this laptop's SQL Server 2019 installation is reporting 0ms elapsed i.e. less than the clock resolution. Reading a row from an on-disk (well, SSD) table is <5ms from cold.

Of course there's a whole lot of "it depends" to be applied, as you are well aware judging by your various comments. For a real query the speed that IO can be processed will likely be the limiting factor. Then it will be the amount of RAM to cache data and system objects (plans etc.). Next will be the quality of the optimizer, what simplifications it can achieve, how good its cost model is, and how accurate are statistics on the tables. Core count is likely not significant for single-query execution times as the cost of orchestrating parallel execution of simple queries outweights an benefit. Having many cores increases system concurrency and throughput. This is for OLTP with three to four joins returning a handful of tables.

For large OLAP queries reading gigabytes from tens of tables then "no" is the answer. That said optimized engines such as ClickHouse are getting scary-good at it.

| improve this answer | |

Your Answer

SimonN is a new contributor. Be nice, and check out our Code of Conduct.

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.