I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data.
Here my table structure :
CREATE TABLE [dbo].[tbl_set_Cart]( [ID] [int] NOT NULL, [AS400_CUSTOMER_COD] [nvarchar](50) NOT NULL, [AS400_LISTIN] [int] NOT NULL, [VALUE] [nvarchar](max) NOT NULL, [DELIVERY_COSTS] [nvarchar](max) NOT NULL, [ITEMS_COUNT] [int] NOT NULL, [ITEMS] [nvarchar](max) NOT NULL, [KIND] [int] NOT NULL, [CHECKOUT_INFO] [nvarchar](max) NOT NULL, [ISSUES] [nvarchar](max) NOT NULL, [LAST_CHECK] [datetime] NOT NULL, [USER_ID] [int] NOT NULL, [IMPERSONATED_USER_ID] [int] NOT NULL, [OVERRIDE_PRICES] [bit] NOT NULL, [HAS_ISSUE] [bit] NOT NULL, [IS_CONFIRMED] [bit] NOT NULL, [IS_COLLECTED] [bit] NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, CONSTRAINT [PK_tbl_set_Cart] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Here an example of an update statement
DECLARE @p0 AS INT = [cart_id]; DECLARE @p1 AS INT = [entry_count]; DECLARE @p2 AS NVARCHAR(MAX) = '..document..' --~8MB; UPDATE [dbo].[tbl_set_Cart] SET [ITEMS_COUNT] = @p1, [ITEMS] = @p2 WHERE [ID] = @p0
Executing this command on the same machine of the DB (a development environment so no other work load) I got this performance:
It really surprise me that it's that slow to load a text into SQL Server, so what I am asking is if there is a better way to achieve that.
The document is a big JSON that will not exceed the 10MB of data (I have done the benchmark reported above in the worst scenario) and I have no issue into converting it to a BLOB or other data structures if performance improve. If it's a better idea I can also use a stored procedure to upload the big text.
A Windows Server 2019 16 logical processor 2,1 GHz and Microsoft SQL Server Standard (64-bit) version 15.0.2070.41 with 4GB reserved RAM, 16 core licensed.
(it's a development only environment and in this case I am the only working with no other program or scheduled activity running.)
I have tested again a different cart (a little smaller one) here times (client statistics) and execution plan.
If I insert a new row it's faster:
For the insert statement I have used the data generated by SQL Server (task -> script table -> data only)
What I am expecting to achieve is an update time <0,5s
In my scenario json usually born small and then increase day by day till the size I am testing. May I start thinking to store them as file?
I have preallocated space in the log file. And check again that recovery model is set to simple. What I can do to further improve performance?