I am dealing with performance issues with a Windows app that uses SQL Server Express (2014) on the back-end.
I have managed to get this running a lot better primarily by reviewing the indexing SQL Server side but there is one particular report that is still running quite slowly.
Looking into what it is doing, it appears to be looping in the app and querying out thousands of very simple
SELECT * queries against one table
WHERE = Primary Key, so only retrieving one record in each case. And when I say IDENTICAL, I mean identical, it's not even varying the primary key to get different stuff, it is asking for exactly the same record back from the database apparently each time it needs it, up to a hundred times in only a few seconds.
This is an example report that takes about 10-15 seconds to run when the server is quiet- how many times the query runs I've added as a comment:
SELECT * FROM "Patient" WHERE "_Recno" = 35051 -- (runs 106 times) SELECT * FROM "Client" WHERE "_Recno" = 15607 -- (99 times) SELECT * FROM "SpeciesEntry" WHERE "_Recno" = 180 -- (97) SELECT * FROM "Table" WHERE "_Recno" = 9 -- (97) SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2615 -- (96) SELECT * FROM "Table" WHERE "_Recno" = 34 -- (96) SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2562 -- (84) SELECT * FROM "Table" WHERE "_Recno" = 33 -- (84) SELECT * FROM "Treatment" WHERE "_Recno" = 1682 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 1819 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 927 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 934 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 935 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 940 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 942 -- (33) SELECT * FROM "Treatment" WHERE "_Recno" = 944 -- (33) SELECT * FROM "OptionWP" WHERE "_Recno" = 103 -- (3) SELECT * FROM "OptionWP" WHERE "_Recno" = 54 -- (1) SELECT * FROM "PatientEstimate" WHERE "_Recno" = 8928 -- (1) SELECT * FROM "Phrase" WHERE "_Recno" = 9718 -- (1) SELECT * FROM "Table" WHERE "_Recno" = 4 -- (1) SELECT * FROM "BreedEntry" WHERE "_Recno" = 3283 -- (1)
The number after the query is the number of times that exact query is being executed, e.g. the query
SELECT * FROM "Patient" WHERE "_Recno" = 35051 is being executed 106 times, with that _Recno. There are actually 1,031 queries being executed to build this report (in this instance, it varies) - the 23 or so above are the distinct queries.
Now each query above runs very very fast, we are talking about a few tens of micro seconds in each case. In fact if you add up all 1,031 queries used to make this report the total amount of time used for all of them is only 59,193 microseconds, or only 59 milliseconds.
So the issue and the delay seems to be the overhead- although there is only about 59ms of actual database time in this the report takes about 10-15 seconds to run for the client as it is going back and forth with over 1,000 queries.
Note in most cases the client app and SQL Server are on the same machine, and multiple instances of the client are accessed through RDP. In a few cases the client is on a different machine on the LAN, and I imagine performance would be worse there. But you can take it in most cases that there should not be a network issue as both client app and SQL Server are on the same physical box.
The ten seconds is even sort of acceptable, the issue is that at busier times that can increase to as much as a minute or more.
Any thoughts on how to go about dealing with optimising this? If it was an app I had access to the source of obviously I'd replace all of this with a single or a few queries that used joins but that isn't an option, the app is a black box- all I can do is optimize from the SQL Server side.
Talking to the client, while performance is bad whether they are using it through RDP or a remote client app installation, performance is a lot worse with the remote client app, and this is more of an issue for them. So any suggestions regarding things I can look at to improve the performance there, with regard to the network or whatever, would be appreciated. One thing to note is that this SQL 2014 box is now virtualized, previously they were using I think 2008 or 2012 but it was not virtualized - they say this report was quicker then. They have other reasons for wanting it virtualized, moving it off virtualization is not an option.
It connects using Windows authentication and (I am pretty sure) TCP/IP. I don't think I'd be able to change this. It's not dropping and reestablishing the connections as far as I can make out, it appears to be using connection pooling at least.
I use Hibernate in my day job and I have come up against this sort of scenario before, with the ORM generating thousands of queries, and my usual solution there is to look at the fetching strategy (lazy vs eager loading) in the code or indeed in the case of reporting usually rewrite the whole thing in SQL. In this case though the software is what it is, a Windows executable, and there is nothing I can do about that, I can only address the SQL side.
My understanding is that the vendor is no longer supporting this particular version and has gone back to a version that uses flat files rather than SQL at all. That wouldn't work for the client - they have this DB integrated with various other things. It's niche software and like much of that sort of sw it's technically terrible on the back end but has functionality that the user in the niche needs. Anyway, I can't change the software, only what goes on on SQL Server. It was unusable, and I have got it usable, so have made progress working within those constraints.
There is no blocking or locking whatsoever, I have checked that. That was actually the main performance problem I fixed, but there has been nothing at all blocking long enough to get logged for the last month or so. Memory isn't really an issue as it is SQL Server Express so limited to 1GB anyway. From looking at it though I don't think it has a memory issue, disk if anything seems to be the biggest hardware choke point.