I have Microsoft SQL Server 2017 and a SQL query that I need to run in less than 1 second but currently it takes 37 seconds. The table has about 25 million rows. My query is pretty simple, actually, and has no joins. It basically does division of one column over another column to compute about 8 percentages. And it uses lag/lead to compare to day before/after, which obviously hurts. That's all in a subquery. Then, in an outer query, I do a WHERE clause to return about 1000 rows where certain percentage goals were met. I have to do the math against all 25 million rows so I can't filter that down more before doing math. The problem is that my query needs to run about a hundred thousand times per day. Obviously, the query needs to be under a second. I have about 3 indices. I've also run Database Tuning Advisor which suggested another index; I created that but suddenly my query ran SLOWER -- about 1.5 minutes each.
My hardware is: a single proc with 4 cores seen as 8 logical processors. Intel i7. 16 GB RAM. Hard drive setup is SSD. Database size is about 10 GB. Wired network is fast/local.
How do I make a real dent in this time? To me, querying 25 million records is not that big of a deal. I'm surprised it takes 37 seconds (or 1.5 minutes with the suggested index). Yeah, I could go out and buy a 4 processor server with 128 GB RAM and maybe I should but would that make enough of a difference? Or is there some better way to get results here? If I'm missing some workaround with indexing, then I'm hoping someone can just give me a hint about common problems and maybe I don't need to buy new hardware. Or if you have experience buying hardware for lightening fast SQL Servers, please tell me if it really makes a difference going from 37 seconds to 1 second. I don't want to spend thousands on a new server only to find that I go from 37 seconds to 25 which still won't be acceptable. I just don't know where is the best place to invest time and money from here. I don't even know if it is normal to expect <1 second results when a table has millions of records.