question

moondaddy-8531 avatar image
0 Votes"
moondaddy-8531 asked MelissaMa-msft edited

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

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?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @ErlandSommarskog, unfortunately, its more like a blob of text which includes the serialized image data as shown below:

 {
     "Isu_Id": "1b417cd2-8c9d-47d8-8ec4-2eb1a8ea50ab",
     "Isu_Rpt_Id": "119f398a-c464-42a4-bea9-dc7b2be50c9f",
     "Isu_Location": "New image, waiting to be processed   ",
     "Isu_Details": "Paint needs to be brighter",
     "Isu_Status": "OPEN",
     "IssueImage_Original": "http://cmtafr.crocodiledigital.net/IssueImagesOriginal/3C417CD2-8C9D-47D8-8EC4-2EB1A8EA50AB.jpg",
     "IssueImagePath_Report": "http://cmtafr.crocodiledigital.net/IssueImagesForReports/3C417CD2-8C9D-47D8-8EC4-2EB1A8EA50AB.jpg",
     "IssueImagePath_Thumbnail": "http://cmtafr.crocodiledigital.net/IssueImagesThumbnail/3C417CD2-8C9D-47D8-8EC4-2EB1A8EA50AB.jpg",
     "ImageData": "/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAMCAgMCAgMDAwMEAwMEBQgFBQQEBQoHBwYIDAoMDAsKCwsNDhIQDQ4RDgsLEBYQERMUFRUVDA8XGBYUGBIUFRT/.....

Any other recommendations?

0 Votes 0 ·

Not sure that I understand why this would matter? This JSON is 10 MB and you are writing it to a single column, or?

0 Votes 0 ·

@ErlandSommarskog , I see, I was thinking FILESTREAM was for just files, not blogs of random text. Then can I view data from the FILESTREAM column similar to a varchar column? Or is there a special conversion required? I have never worked with FILESTREAM

0 Votes 0 ·
Show more comments