Hello Brain2000,

You are right mentioning that SQL Server licenses are expensive, and I admit that unzipping on the server is unusual and unexpected.

Like Ool2t5902 mentions, I use the function to...

I just published an article (https://www.sqlservercentral.com/articles/unzipping-word-documents-in-tsql) that explains how to natively unzip in T-SQL

You are right Phil. Images are heavily compressed. I did not express myself correctly as I meant that images are not compressed XML files. You are also right saying I...

• This reply was modified 6 months ago by  cmartel 20772.

I performed my tests on a local SQL Server 2019. I will experiment with all received suggestions during the holidays. Many thanks to all of you and happy holidays!

The following statement runs in 4 seconds and is definitely better:

SELECT
  B.ID,
  D.Value
FROM #Big B
LEFT MERGE JOIN (
  SELECT ID, dbo.GetValueInLanguage('fr', XmlValue) AS Value...

I want to thank you all for your suggestions. This case had me revisit some concepts of SQL Server I usually ignore or avoid.

My procedure declares a lot of variable...

Jonathan,

OPTIMIZE FOR UNKNOWN cannot be applied to a stored procedure.   EXEC myStoredProc WITH RECOMPILE does not make it faster.

In an earlier post, Scott suggested to add BEGIN TRANSACTIONS and COMMIT...

Jonathan,

There is no problem with the UPDATE. All indexes are created. Moreover, tables #SecuritiesClass and #SecuritiesClassRules only contain 3 records. By putting GETDATE() before and after the UPDATE, I can...

• This reply was modified 2 years, 8 months ago by  cmartel 20772.

Hi Ken,

For your knowledge, this is the actual statement that triggers the problem:

UPDATE SC
SET 
  SC.QuickEffectiveDate = SCR.EffectiveDate,
  SC.QuickNominalValueChangeTypeID = SCR.NominalValueChangeTypeID,
  SC.QuickHasRightToVote = SCR.HasRightToVote,
...

Thanks ratbak,

When I check "Include Actual Execution Plan", I am not getting a big plan, I am getting tons of single plans for every SELECT, INSERT, UPDATE or DELETE and...

Ken,

Compatibility level is 150. QUERYTRACEON 9481 has no effect.

Thanks.

homebrew01,

Of course I can fill a temp table and apply the WHERE clause on a "SELECT * FROM #TEMP" statement. This approach has been discussed in forums for ages. But...

• This reply was modified 3 years, 2 months ago by  cmartel 20772.

Jonathan,

All reports are based on a "body" statement and the WHERE and ORDER BY clause is added according to the user choices. The resulting statement is always executed with OPTION...

Frederico,

I also think that SQL Server is being lousy in this particular case. I would not mind if my "unoptimized" statement would run twice or three times slower but we...

Hi Scott,

like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both). Therefore,...