The slowness entirely comes from the fact that ACID-compliant DBs typically have to wait for a process involving a physically moving part to complete before they can return a success code. This process is measured in milliseconds. Milliseconds. There is no voodoo you can do that make that fast.
Other than well-tuned indexes, probably the best thing you could do for SQL performance would be to trade the HDDs for SSDs and improve the IO ops/second by a factor of 50.
But then that is just a factor of 50. Will your database need to grow to handle a factor of 50 increase in usage over the next year or two? If it's already big enough to warrant this discussion, chances are the answer to that question is "yes". And, of course, SSD will also improve NoSQL performance.