learned tricks:
Following SQL clause impact query performance:
1. Parses JSON textby OPENJSON() syntax
SELECT * FROM [dbo].[table] WHERE column in (SELECT value FROM OpenJson('["a","b","c"]') )2. Timestamp format
CREATE TABLE [dbo].[table](
[test1] nvarchar NULL,
[test2] date NULL,
[test3] datetime NULL,
ON [PRIMARY]
GOSELECT * FROM table
WHERE [test1] = '2020-01-01 00:00:00.000'
WHERE [test3] = '2020-01-01 00:00:00.000'Observed query speed [test3] >>[test1]
3. Update in SQL process speed is lower than insert with specified column new constant value
4. Prefer to use NOT NULL in create schema. NULL cost extra space or other unexpected outcome
5. C# nuget System.Data.sqlclient have bug, using MS.data.sqlclient 3.0
6. SQL sub-query not recommend to use complex clause (e.g. case when (A) AND (B) then xxx else xxx), due to it will spent more time than just filled another column value before do sub-query
7. Organize JSON ARRAY
SELECT '['+STRING_AGG(column,',') FROM table+']'8. Trim white space
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(column, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))9. For loop
10 If exist update or insert pattern
IF EXISTS ()
BEGIN
UPDATE...
END ELSE
BEGIN
INSERT INTO
END 11. Column store index
12. Link server
13. Bulk insert
14. SQL Not In (another table) slow down query speed in large table
If table A & B exist an identity column – hashMD5
Recommend to use LEFT JOIN to exclude
SELECT * FROM A
LEFT JOIN B
ON A.hashMD5 on B.hashMD5
WHERE B IS NULL15. MS SQL not support array data type
PostgreSQL support this datatype
16. Temp table query speed higher than Common Table Expression (CTE) for intensive compute job
Reference:
https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16