**Problem**
We have a table that has just 44 rows, and *one* of the columns is
varbinary(max)
.
This column stores XML files converted to varbinary(max)
.
Average length of data in this particular column is over 8000 symbols per row.
Average row size is 17 KB (26 KB if you take into consideration total size of table clustered + nonclustered + unused space).
Values of varbinary
strings are very similar, only small differences between them.
Total table size is small: sp_BlitzIndex
reports 1.1MB; 0.8MB LOB for clustered, 0.1 MB for nonclustered.
Developers claim that reading out whole table from application, takes up to 20 seconds.
They would like to reduce the time it takes to read all rows.
**What I've tried**
For me (in SSMS over VPN) it takes about 3-6 seconds to read 44 rows from this table.
For comparison, other tables of comparable size (~1.1 MB) with thousands of rows take 1-3 seconds.
First thing I recommended was to replace varbinary(max)
with xml
data type.
However developers don't know how long it will take them to achieve this and when this can be done.
Second, I rebuilt the table with (data_compression=page)
, but it did not compress the column (used sp_BlitzIndex
and sp_spaceused
to verify).
**Questions:**
How can we speed up reading data from this table for application and SSMS?
Should it be columnstore indexes, converting column to XML, any other option, or anything that needs to be checked on infrastructure side (and how)?
**Update:**
CREATE TABLE [dbo].[MySlowTable](
[ID] [int] primary key IDENTITY(1,1) NOT NULL,
[CarrierID] [int] NOT NULL,
[MailClass] [int] NULL,
[LabelSize] [varchar](10) NULL,
[LabelType] [varchar](30) NULL,
[Template] [varbinary](max) NOT NULL,
[TemplateName] [varchar](50) NULL)
CREATE NONCLUSTERED INDEX [index_Label] ON [dbo].[MySlowTable] (
[CarrierID],[MailClass],[LabelSize],[LabelType] )
Query that app runs:
SELECT [ID],[CarrierID],[MailClass],[LabelSize],[LabelType],[Template],[TemplateName]
FROM [DB].[dbo].[MySlowTable]
Execution plan is simple clustered index scan with no warnings
In properties I see that only wait is ASYNC_NETWORK_IO
with varying wait time 5000-38000 ms (now it became slower in SSMS over VPN)
For tables of comparable sizes (1.1 MB) with thousands of rows, ASYNC_NETWORK_IO wait is smaller than for table in question
I assume *sometimes* network over VPN can can be slow for my SSMS, but how can it be slow for Azure App querying DB server 1 MB table that is not geographically far, that is good question
Asked by Aleksey Vitsko
(6195 rep)
Jun 15, 2021, 03:12 PM
Last activity: Jun 16, 2021, 10:53 PM
Last activity: Jun 16, 2021, 10:53 PM