1

We have sap table PRCD_ELEMENTS which is:

  1. Partitioned (on KNUMV column)
  2. Have PK aligned with partition schema
  3. Have non clustered index on rowid column which is aligned with partition schema
  4. Table has around 3 billion of records

This table has could have even 300 000 changes (for example 200 000 deletes and 50 000 inserts and 50 000 updated) per minute. Query to delete (is parametrize) row looks like:

delete from [sap].[PRCD_ELEMENTS]
where [rowid] = @P1

Also we are using 3rd party tool to integrate data to this table (so we can not easily sql for insert/delete/update of this table). Unfortunately performance of delete data is not good enough. Our idea was to create column store version of table PRCD_ELEMENTS with name PRCD_ELEMENTS_CS (also partition on same column). Columnstore deletes are much less performant than deletes on row store table. Deletes looks the same:

delete from [sap].[PRCD_ELEMENTS_CS]
where [rowid] = @P1

Query store data (columnstore): enter image description here

Query store data (row store): enter image description here

Questions:

  1. Why columnstore is much less performant in terms of deletes ?
  2. Do you have any ideas what can we do to have deletes faster (we can not easily modify delete query) ?
7
  • Have you tried specifying the KNUMV partitioning column in the DELETE query?
    – Dan Guzman
    Dec 18, 2023 at 12:10
  • Unfortunate I can not change delete query for now so only rowid is in where statement in delete query.
    – adam.g
    Dec 18, 2023 at 12:12
  • Is rowid unique?...200,000 deletes per minute are of a single row per delete? Columnstore is better for more static data, and helps with improving read performance. Large amounts of deletes are not efficient in a columnstore. Why is 0.72 milliseconds (less than 1 millisecond) not fast enough for a delete operation, and what do you need it to be then?
    – J.D.
    Dec 18, 2023 at 13:02
  • yes, rowid is uniqe (but we have just non clustered index on this column). I mean not efficient means that we have latency around 1-2h to have this table up to date. Our maximum is 30 minutes so speed should be at least doubled.
    – adam.g
    Dec 18, 2023 at 13:19
  • Small deletes in columnstore are generally not good since they don't actually get deleted right away. (Lookup Deltastore) learn.microsoft.com/en-us/sql/relational-databases/indexes/… , Columnstore is good for analytical workload, but will in general not be very good for small lookups. Dec 18, 2023 at 13:34

0

Your Answer

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