Pagination (edit)
Một số kinh nghiệm khi tối ưu hóa câu SQL Query
+ Không nên dùng nhiều bảng tạm khi truy vấn nhiều dữ liệu
+ Việc đầu tiên là xóa bảng tạm ngay lập tức và thay bằng Sub-Query
https://kipalog.com/posts/Mot-so-kinh-nghiem-khi-optimize-sql-query
https://azlogs.wordpress.com/2016/07/02/mot-so-kinh-nghiem-khi-optimize-sql-query-sua-bai-viet/
Fastest way: Keyset Pagination
SELECT ... FROM ... WHERE ... AND id < ?last_seen_id ORDER BY id DESC FETCH FIRST 10 ROWS ONLY
Better way
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
For SQL Server 2012: OFFSET + FETCH
http://dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch
-- CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 10
SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;
For SQL Server 2005: ROW_NUMBER
https://techtalk.vn/phan-trang-trong-co-so-du-lieu-sql-server.html
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
For SQL Server 2000: ROW_NUMBER
http://nguyennhatcuongit.blogspot.com/2017/10/dung-ajax-e-phan-trang-trong-datatable.html
WITH OrderedOrders AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM [dbo].[vSalesPerson]
)
SELECT RowNumber, FirstName, LastName, Sales YTD
FROM OrderedOrders
WHERE RowNumber > 50 AND RowNumber < 60;
For SQL Server 2000: WHERE
http://www.gacoder.info/hien-thi-va-phan-trangtim-kiem-trong-laravel-va-datatables/
-- Nhược điểm lớn nhất là nó đưa hết data vào bảng tạm trong tempdb database cho nên bộ nhớ sẽ được sử dụng tối đa cho mục đích phân trang.
DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20
DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1 -- 1020
DECLARE @orderedKeys TABLE (
rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
TableKey int NOT NULL
)
SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
SET ROWCOUNT 0
SELECT t.*
FROM Orders t
INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum
Kiến thức cần nắm vững
Variable tables
DECLARE @TableName TABLE
(
ID INT IDENTITY PRIMARY KEY NOT NULL,
Name VARCHAR(10) NOT NULL,
DOB DATETIME null
)
INSERT INTO @TableName
( Name, DOB )
VALUES (
'TONA', -- Name - varchar(10)
GETDATE() -- DOB - datetime
)
SELECT * FROM @TableName
Biến bảng là biến dữ liệu kiểu bảng, được khởi tạo và lưu trong RAM như một biến bình thường chỉ khác là nó kiểu bảng. Do đó biến bảng cũng đóng vai trò như một bảng tạm. Chỉ khác là bảng tạm chỉ tạo một lần và có thể sử dụng trong suốt phiên đăng nhập, còn biến bảng sẽ mất khi khi kết thúc đoạn truy vấn.
Temporary tables
Bảng tạm là các có cấu trúc và chức năng như một bảng cố định bình thường trong SQL Server. Nhưng thay vì tạo ra một bảng trong Database, bảng tạm được tạo ra và lưu trữ trong tempdb. Chúng ta thường tạo bảng tạm trong một câu truy vấn, trong xử lý của một procedure hoặc trong một function (chỉ sử dụng được biến kiểu bảng).
Local temporary table là bảng tạm được tạo ra và tồn tại trong 1 kết nối. Khi kết thúc kết nối thì bảng tạm này sẽ tự động được xóa. Tên của bảng tạm kiểu Local được bắt đầu bằng ký tử #.
Global temporary table là bảng tạm có phạm vi ảnh hưởng trong tất cả các kết nối và nó chỉ tự động được xóa đi khi tất cà kết nối đã được ngắt. Tên bảng tạm kiểu Global được bắt đầu bằng ##.
Bảng tạm được tạo ra trong Database Tempdb khác với Database của bạn vì thế có thể gây ra các vấn đề về hiệu suất. Do đó, bạn cần tính toán kỹ số lượng các Fields cần thiết phải lấy ra.
Bảng tạm (Local)
CREATE TABLE #LocalTmpTable
(
Id int, -- int identity(1 1) primary key not null,
Name nvarchar(50)
)
Bảng tạm (Global)
CREATE TABLE ##GlobalTmpTable
(
Id int, -- int identity(1 1) primary key not null,
Name nvarchar(50)
)
Thao tác với bảng tạm
SELECT Id, Name INTO #LocalTmpTable FROM Users WHERE ...
SELECT Id, Name INTO ##GlobalTmpTable FROM Users WHERE ...
