0

For one of our application, we have really large volume SQL table which has 100 million rows and we are using Azure SQL managed instance. On daily basis users will either insert new records or updates very old records. This is the core table where all other systems are referring through SQL replication to other on prem servers. So the real problem is,

  1. Users will refer this table on daily basis to do CRUD operations.
  2. Other down stream systems also refers this table data in on prem from Azure.
  3. Reports and UI listing screens are being used this table very frequently to pull the data by joining other tables. The performance is always going down when this table is getting joined with other tables.

ABC Mapping Table has 100 Million rows with less than 10 columns. This table has below columns along with date and audit columns. ABCID is primary key of ABC table

  • A is primaryKey of A table

  • B is primary key of B table

  • C is primary key of C table

  • A Table has less than 20k records and 20 columns.

  • B Table has 1500 rows with 20 columns.

  • C Table has 150k rows with 30 columns.

In UI, we are showing almost 30 columns from all these 4 tables along with filters. We added clustered columnstore, non clustered columns store, NC with covering everything but not able to improve the performance. Filter search criteria from UI will be any of the column value from all the 4 mentioned tables. Especially on C table which has more columns.

What is the best way to handle this large table for CRUD operation, real time reporting as well data reference to on prem systems?

The below things are tried

  1. Created Indexed view by combining all these tables and added different indexes. Improvement is there in query performance but it causing performance issue in base table during CRUD operation.
  2. Created new de normalized replicated table by combining all these table as view and added index in replicated table, no performance issues of base table but read performance is not good in de normalized table.

Please suggest good options to handle all these scenarios.

James Z
  • 12,209
  • 10
  • 24
  • 44
Venkat
  • 1
  • 2
  • Have you looked at the execution-plans for your queries? What pricing-tier is your managed-instance? Why are you using MI instead of normal Azure SQL? – Dai Feb 20 '21 at 06:18
  • Please post your **actual** `CREATE TABLE` statements (including `CREATE INDEX`), example `SELECT` queries with poor performance, and your slow DML. Are you using hints and locks? Are you doing any multi-row DML operations? Have you considered using `MERGE INTO`? Are you seeing deadlock errors? – Dai Feb 20 '21 at 06:19

0 Answers0