1

I have this requirement to extract about 450 fields with most of it's fields length with varchar(300) from multiple tables for one single BI report.

For better performance, I tried to create one single table to store all the required fields. However, it exceed the SQL server max row size and table was created with errors such as cannot insert or update the table since it exceeded max row size limit. As such, It seems like we have to split the tables.

Then I am afraid for the extract performance. During extract time, we also need to apply data level security. Do you have any idea for better design to get better performance?

1
  • data level security - by that you mean row-level security ? Oct 4, 2021 at 11:19

1 Answer 1

0

450 fields for a single BI report - that seems a lot, really cumbersome report

Extracting so much text from SQL Server will always be "a bit" slow when it comes to transferring those rows over the network (you will see big ASYNC_NETWORK_IO waits)

Best solution for performance would be to check with end users of the BI report - do they really need so many columns in a report ? Having a lot of data to chose from sometimes can be good, but I doubt end users will use all of them

Check with them what they really need, and it might happen that significant amount of fields really can be removed from the report, which will improve the performance


update: if you go by 1 single table route, to resolve the max row size limit issue, you either have to:

a) reduce the varchar columns from 300 to 10-15 symbols. Of course you can do it only if your actual data allows it (if the text values in each column are small). This way you actually can have 450 columns in a row

or

b) if your text data in varchar fields is actually bigger, then, it is actually pushed from row into off-row data and row can be bigger than 8060 bytes. BUT - here is also limitation. You can't have more than ~335 such non-null varchar columns in a row (actual number will be less than 335). Read here for more details why you can't have 450 bigger varchar columns in a row, even if data pushed off-row, here: Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

Then, if you manage to reduce all varchar columns to 10-15 symbols, OR reduce number of columns to less than 335, you can create clustered columnstore index (CCI) on that single table.

CCI can improve reading speed from table significantly. But beware that depends on table usage patterns. There are a lot of gotchas with CCIs, and your situation is a tip of iceberg where a lot of things is not known to provide the precise solution

4
  • Thank you so much for your answer. Actually this is not for a single report. The objective here is to extract all related information which means blending data in DB and extract as one single data set and will be stored as a data source in BI server so for better performance in front-End. Yes. You are right. Current extract which has half of less fields take several hours. As such, we are planning to prepare and store the data in DB before extract. However, it is very huge and cannot fit into single table. As such looking for any better way to gain best performance for this kind of requirement
    – cool
    Oct 5, 2021 at 5:08
  • Hi Aleksey, Thank you so much for your advise. I test using Clustered Columnstore Index whether can fit all the columns. However, it couldn't. Still throw the same error.
    – cool
    Oct 11, 2021 at 3:51
  • Error message is "Warning: The table "test400fields" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit." Since it is prod table, I am afraid to continue. Any idea please?
    – cool
    Oct 11, 2021 at 4:54
  • @cool updated my answer - added new findings. Don't forget to vote up and mark as answer,if you find it useful Oct 11, 2021 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.