SQL Server 2019: Performance issue saving 10MB of text to db.

Question

I have a mobile app that saves images to the server where they are stored in the file system. Some devices are producing bugs in this area and sometimes take a long time to process from the client side, so we turned on trace logging to see where the issues and bottlenecks are.

We have custom trace logging that records info entering and leaving every method which we can turn on/off at runtime for trouble-shooting and this gives us great visibility various things.

In this case, when logging is turned on, we save the serialized photo into the db’s log table, and I found that it can take up to 3 seconds to save 10 mb of text to the database. This timespan is measured from the time the call enters the data access method to the time it leaves the data access method.

Compare this to calling the same data access method without the serialized image data, it takes about 40 milliseconds to execute (1.3% of the time)

Lastly, this is all taking place on my dev PC so there is no issue with internet or network constraints. I assumed that 10mb of text would not cause much of a performance hit since it’s already in a variable being passed directly into the db, but evidently, SQL Serve is slow in writing 10mb of text to a table row (The table column type is varchar(max)). I find this very interesting. Is there a way to configure this to make writing large text faster?

Answer 1

Indeed, there is. If your files are that size on average, there is all reason to look into FILESTREAM. That is, you make the column a FILESTREAM column and then you use the OpenSqlFilestream to initiate writing with the Win32 API. The programming is certainly a little more advanced, but you can expect a good performance improvement.