mssql: FULL materialization strategy is extremely slow using MERGE INTO existing table ... ON 1 = 0

[martinburch]

Following SQLMesh's initial (fast) plan of a FULL model on the mssql adapter, subsequent SQLMesh runs, which rebuild the model completely, are very slow (hours or more).

This is caused by using the pattern MERGE INTO ... ON (1 = 0) existing table, like this

MERGE INTO [schema].[schema__table__1460139764] AS [__MERGE_TARGET__]
USING ( ... ) AS [__MERGE_SOURCE__]
ON (1 = 0)
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED THEN INSERT ( ... ) VALUES ( ... );

I can see this query is suspended in the MS SQL query engine with 100% CPU use for hours.

The materialisation strategy for FULL mssql models is not specified in the docs, but no other database uses MERGE for FULL.
https://sqlmesh.readthedocs.io/en/stable/concepts/models/model_kinds/?h=merge#materialization-strategy_2
I suggest using the Postgres method for mssql: DROP TABLE, CREATE TABLE, INSERT

How do I know the MERGE pattern is the problem?

When I terminate the stuck run process and then

TRUNCATE TABLE [schema].[schema__table__1460139764];

and re-run sqlmesh run then the run is as fast as the initial plan. So SQLMesh is still doing the MERGE INTO except now it's comparing 0 records.

The problem is one of squares: if I have a table of 2 million records, and I'm doing a full rebuild, I have 2 million new records which must be compared, and that's 4 trillion comparisons. Even if each comparison only took 1 millisecond, the comparisons would take over 100 years to complete!

[martinburch]

I've tried implementing the TRUNCATE TABLE hack in the model's pre-statement but SQLMesh is not happy, Could not render or parse jinja (yes, the jinja itself is valid see http://jinja.quantprogramming.com) for something like...

JINJA_STATEMENT_BEGIN;
  {% if runtime_stage == 'creating' %}
    IF OBJECT_ID('{{ this_model }}') IS NOT NULL TRUNCATE TABLE {{ this_model }};
  {% endif %}
JINJA_END;

(I also tried extra quotes, like IF OBJECT_ID(''{{ this_model }}''))

Very frustrating. Any idea why this wouldn't work? And when is the underlying bug likely to be resolved?

[georgesittas]

Hey, can you please run this with --debug and share the complete error message / stack trace that you see after implementing this workaround? Talking about the "Could not render or parse jinja" error.

Very frustrating. Any idea why this wouldn't work? And when is the underlying bug likely to be resolved?

We're likely to prioritize fixing this soon– appreciate the patience!

[martinburch]

Hey, can you please run this with --debug and share the complete error message / stack trace that you see after implementing this workaround? Talking about the "Could not render or parse jinja" error.

Unfortunately, I can't, because of #4505 I am extremely reluctant to write failing models. All the gateway's SQLMesh models and the metadata has to be wiped and re-created from scratch whenever there's a failure!

As a further workaround, I'm now truncating the table completely outside of SQLMesh. And this is just one step away from removing SQLMesh's responsibility for building the table entirely, which would allow us to truncate and rebuild in a transaction, for no downtime at all.

[erindru]

This is now fixed in v0.187.0