2022-03-18

SQL Server 使用執行計畫進行SQL性能分析

前言

最近因為排序分頁等問題要分析性能，因此就來記錄一下SQL性能分析。
在此使用的是SQL Server的SSMS的執行計畫去分析效能。

實作

1.先開啟執行計畫，就上面兩個黃色標起來的地方

2.再來執行SQL語法。

SELECT 
    T.Id,
    T.Name,
    T.Priority,
    T.UserId,
    U.UserName
FROM Todos T
LEFT JOIN Users U
ON U.Id = T.UserId 
    AND U.IsValid = 1
WHERE 
    T.IsValid = 1 
    AND T.UserId = 3
ORDER BY T.Id

3.結果底下會出現一個區塊Execution Plan

4.查看每一個語句

5

(撰寫中…未完待續…)

參考資料

2022-03-17

Database►PostgreSQL

PostgreSQL 建立自動遞增PK Table

前言

因為發現用PGADMIN建立Table速度太慢(超久的Loading)，因此最後就習慣用指令去建立Table了。
以下為在PostgreSQL建立一個遞增+1的SEQ欄位，且為Table的PK。

CREATE TABLE (
    SEQ SERIAL PRIMARY KEY,
    NAME CHARACTER VARYING(100) NULL,
    REMARK TEXT,
    IS_VALID BOOLEAN NULL,
    CREATED_ON TIMESTAMP WITHOUT TIME ZONE NULL,
    UPDATED_ON TIMESTAMP WITHOUT TIME ZONE NULL,
)

2022-03-16

Database►PostgreSQL

PostgreSQL 反條件式NOT

前言

最近遇到要添加反例的案件，需要把整個條件反著寫。
但AND/OR反著寫就很違反閱讀習慣，於是找到了能用NOT這語句去概括整條件句，結果就可以變反。

如果有一表如下:

price_record
id | price
---+------
1  | 100 
2  | 200
3  | 300

IN:數值在這集合裡面。

SELECT * FROM PRICE_RECORD PR 
WHERE NOT 
    (
        -- 寫原本要反例的判斷邏輯
        PR.PRICE IN ('100','200')
    )

結果:

price_record
id | price
---+------
3  | 300

2022-03-16

Database►PostgreSQL

PostgreSQL IN/NOT IN 判斷語句

在這主要介紹PostgreSQL IN、NOT IN的用法
IN:數值在這集合裡面。
NOT IN:數值在不再這集合裡面。

如果有一表如下:

price_record
id | price
---+------
1  | 100 
2  | 200
3  | 300

IN:數值在這集合裡面。

1 2	SELECT * FROM PRICE_RECORD PR WHERE PR.PRICE IN ('100','200')

結果:

id | price
---+------
1  | 100 
2  | 200

NOT IN:數值在不再這集合裡面。

1 2	SELECT * FROM PRICE_RECORD PR WHERE PR.PRICE NOT IN ('100','200')

結果:

1
2
3

id | price
---+------
3  | 300

2022-03-11

Database►PostgreSQL

PostgreSQL Select解析JSON

(撰寫中。)

參考資料

https://www.twblogs.net/a/5ee9f9bc29a78f6b0110a168

2022-03-11

Database►PostgreSQL

PostgreSQL Select 取得昨天、近幾天資料

前言

最近遇到在PostgreSQL取得近幾天資料的問題，於是在思考是否有比較直覺得寫法?
後來發現用Between或>=都可以。

以下有一個table:

tb_record

seq | record_date(character varying(8))
---- -----
1   | 20220101
2   | 20220103
3   | 20220305
4   | 20220308
5   | 20220310

比如今天是，20220310則:

取得昨天到今天的資料

between寫法:

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE BETWEEN TO_CHAR(NOW() - INTERVAL '1 DAY','YYYYMMDD') AND TO_CHAR(NOW(),'YYYYMMDD')

大於等於寫法:

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE >= TO_CHAR(NOW() - INTERVAL '1 DAY','YYYYMMDD')

結果:

1	5 \| 20220310

取得近五天資料

between寫法:

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE BETWEEN TO_CHAR(NOW() - INTERVAL '5 DAY','YYYYMMDD') AND TO_CHAR(NOW(),'YYYYMMDD')

大於等於寫法:

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE >= TO_CHAR(NOW() - INTERVAL '5 DAY','YYYYMMDD')

結果:

1
2
3

3   | 20220305
4   | 20220308
5   | 20220310

總結

其實就是使用NOW()函數與INTERVAL條件去組日期區間去篩選(O)。
並且搭配一些內部的轉型。
以上面結果就是都轉成YYYYMMDD的字串去比較。

2022-03-10

Database►PostgreSQL

PostgreSQL Left Join Lateral 用法

前言

最近碰到一個PostgreSQL搜尋與句的問題。
就是怎樣在PostgreSQL達到類似SQL Server的Outer Apply的寫法。
因為需要在原本一對一的表，再去LEFT JOIN一個一對多資料。
並且把一對多資料取得一個結果存進一對一的表，像是Boolean、數量。

目標:
一張是訂單主表，一張是訂單明細表
想以主表為主，取得有幾張明細表數量。
Table如下:

Table ORDER

SEQ
---- 
1
2

Table ORDER_DETAIL

SEQ  | ORDER_SEQ | NAME 
---------------------------
1    | 1         | 訂單明細1
2    | 2         | 訂單明細2
3    | 2         | 訂單明細3

使用 Lateral :

SELECT 
    O.SEQ AS ORDER_SEQ,
    RS.ORDER_DETAIL_COUNT
FROM ORDER AS O
LEFT JOIN LATERAL(
    SELECT COUNT(OD.SEQ) AS ORDER_DETAIL_COUNT FROM  ORDER_DETAIL AS OD 
    WHERE   OD.ORDER_SEQ = O.SEQ
) AS RS ON TRUE

結果

ORDER_SEQ | ORDER_DETAIL_COUNT
------------------------------
1         | 1
2         | 2

不用 Lateral:

SELECT 
    O.SEQ AS ORDER_SEQ,
    RS.ORDER_DETAIL_COUNT
FROM ORDER AS O
LEFT JOIN ORDER_DETAIL AS OD on OD.ORDER_SEQ = O.SEQ
GROUP BY O.SEQ

結果

ORDER_SEQ | ORDER_DETAIL_COUNT
------------------------------
1         | 1
2         | 2

總結

用LATERAL會比較直覺，就不用最後再去GROUP BY。
如果想繼承上面LEFT JOIN表的條件，那LATERAL優勢就會出來，會顯得簡單很多。

參考資料

https://www.cnblogs.com/ricklz/p/12122808.html

2022-03-07

Code►Javascript

Javascript 雙問號、可選鏈用法

在寫Javascript時會看到??的語法或?.的用法。
在這稍微做解釋:

?.可選鏈:
作用:可以簡化寫法，因為Javascript如果強制叫出不存在的物件會出現錯誤，所以原本寫法都要寫很長去檢查物件屬性是否存在。

const user = {data:{address:"my address"}}
// 原本寫法 output:my address
const userAddress = user.data && user.data.address
// 替換如下 output:my address
const userAddress = user.data?.address

可以減少巢狀物件取的屬性的程式碼。

??:
作用:左方為null或undefined則會直接回傳右方的值。

const detail = {data:{address:"my address"}}
// 原本寫法 output:10
const detailAmount = (detail.data.amount && detail.data.amount) ? detail.data.amount : 10 ;
// 替換如下 output:10
const detailAmount = detail.data?.amount ?? 10;

上面因為找不到amount所以接回傳10，有的話則回傳amount

總結

感覺未來的Javascript會充斥著「??」、「?.」
到時候又配上Javascript函式寫法感覺閱讀性會複雜了。(雖然簡化語法，但學習門檻變高)
看自己手上的專案斟酌吧。

2022-03-04

Database►PostgreSQL

PostgreSQL撰寫stored procedure範例

前言

在這篇中介紹如何撰寫一個PostgreSQL的stored procedure。

Procedure模板

以下是一個固定的模板，我們將以此模板來撰寫。

create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$

procedure_name : 是prodcedure的名子
parameter_list : 放傳進參數。可以選擇兩種方式:inout、out
plpgsql : 使用的prodcedure內的語法，可以改sql或C等等，看自己用哪種方式寫
$$:可以看到有被各一個$$圍住的區塊就是body，寫語法的區塊就在$$內。

補充:
stored prodcedure沒有回傳值，所以並不會有return expression;的寫法，但你可以加return;讓程式執行到一半停止執行。

POSTGRESQL文件:
A procedure does not have a return value. A procedure can therefore end without a RETURN statement. If a RETURN statement is desired to exit the code early, then NULL must be returned. Returning any other value will result in an error

實作

實際撰寫

目標:
撰寫一個傳送用戶ID回傳用戶資料的procedure。

users資料表:

因為PostgreSQL的stored procedure沒有回傳值，所以就要用參考的方式去拿值。
以下用INOUT refcursor refcursor去當作參考。
IN user_id integer則是要取得資料的使用者id。

CREATE OR REPLACE PROCEDURE get_user_data
(
	IN user_id integer,
	INOUT refcursor refcursor
) 
LANGUAGE 'plpgsql'
AS $$
DECLARE
  str_sql varchar;                                                    
BEGIN
  str_sql := 'SELECT * FROM users WHERE id = '|| user_id ;
  OPEN refcursor FOR EXECUTE str_sql;                             
END;
$$

|| : 是字串的連結符號
傳進一個user_id跟參考的物件。

建好後可在左方PGADMIN看到

之後可以用call來執行。
建立一個區域，宣告一個RECORD物件用來印資料、一個refcursor物件用來參考
接著跑loop迴圈，將每個out_ref放進rcd，再用raise notice印出。
如果沒資料則用IF判斷式離開。

DO $$
DECLARE
	rcd RECORD;
	out_ref refcursor;
BEGIN
	call get_user_data(2,out_ref);
	loop
		fetch in out_ref into rcd;
		IF NOT FOUND THEN
			EXIT;
		END IF;
		raise notice 'user_id=%', rcd.id;
		raise notice 'user_name=%', rcd.name;
  	end loop;
	close out_ref;
END;
$$ LANGUAGE plpgsql;

結果:

總結

PostgreSQL的prodcedure並不好寫，因為他沒辦法直接有返回值。要用參考的方式。他不像SQL Server一樣簡單，能直接Select完畢，因為並不具備回傳值。要回傳值就得寫他的function，就不是prodcedure了。

然後，如果想參考SQL Server的prodcedure我之前也有寫一篇:SQL-Server-Stored-Procedure實作範例可以參考。

參考資料

2022-03-03

Database►SQL Server

SQL Server Left Join只取第一筆做法Outer Apply

前言

最近剛好有要在SQL Server中Left Join N筆結果排序後只取得第一筆的需求。
發現Left Join資料結果會有問題，所以發現了Outer Apply作法能夠達到，因此筆記一下。

實作

需求:
撈出每位使用者還沒完成的最早一筆任務。
並以Priority做排序，如果遇到相同層級則以最早新建的資料為主。

Todos資料表:

Users資料表:

sql:

SELECT 
	A.UserName,
	B.Name TaskName,
	B.Id TaskId,
	B.Priority
FROM Users A
	OUTER APPLY
	(
		SELECT TOP 1 
			Name,Priority,Id
		FROM Todos 
		WHERE 
			A.Id = UserId 
			AND Todos.IsValid = 1 
			AND IsComplete = 0
		ORDER BY Priority ASC,Id ASC 
	) B
WHERE
	A.IsValid = 1

執行結果:

總結

SQL Server有些特殊語法，有時真的得在實作時才會真的碰到。(坑)

UM's Coding Note

I record my coding experience at this place🤙

SQL Server 使用執行計畫進行SQL性能分析

前言

實作

參考資料

PostgreSQL 建立自動遞增PK Table

前言

PostgreSQL 反條件式NOT

前言

PostgreSQL IN/NOT IN 判斷語句

PostgreSQL Select解析JSON

參考資料

PostgreSQL Select 取得昨天、近幾天資料

前言

總結

PostgreSQL Left Join Lateral 用法

前言

總結

參考資料

Javascript 雙問號、可選鏈用法

總結

PostgreSQL撰寫stored procedure範例

前言

Procedure模板

實作

總結

參考資料

SQL Server Left Join只取第一筆做法Outer Apply

前言

實作

總結