TTN_vOz
Senior Member
Không phải Load balancing mà là Replication
Các bác có query sql kiểu này không?
- Người tạo chủ đề Donald Trump USA_No1
- Ngày bắt đầu
conclave0
Senior Member
Bài toán của mình là dùng PBI direct query thẳng vào DB để xuất report cho 8-9 phòng ban xài. Số lượng table nhiều là vì nó là sổ cái, join với những bảng thông tin sổ cái, kết hợp với những bảng chứa thông tin doanh nghiệp, thông tinh sales, và join với những table của các phòng ban khác. Do có những phòng ban xài luôn data của những phòng khác.Trường hợp nào bác phải query nhiều vậy
Bài toán của bác trong trường hợp export data thì chắc chắn là nhanh hơn.
Còn trong page index, page show, edit, việc nhanh hơn có nhìn thấy ko ?
Khi dùng view thì mất 30p để ra được data trên PBI cho 1 năm (khoảng 1 tỉ dòng cho 5 năm, 1 năm khoảng hơn 80 tr). Vậy nên mình ETL data thẳng vào 1 table lớn và đánh index. Kết quả: 7 phút.
Vì table quá lớn, nên mỗi lần ETL mình đều drop và re-build index, ETL vào partition chia theo quý. Query debug nhanh, user xài PBI hài lòng.
Đúng là dễ control, nhưng sinh nhiều bảng dễ dẫn đến những table xài 1 lần quá nhiều, gây rác DB. Việc join nhiều bảng thì nó là đặc điểm của business mình đang làm (general ledger, asset price + flow, insurance event, cash flow, ...). 1 Report của những công ty mình làm thường sẽ join khoảng 5-10 tables trên core của họ.
Hiện tại thì VN mình thường theo 2 trường phái. Mỗi trường phái đều có có những quy tắc riêng, mình chỉ nêu vài keyword ở đây thôi.
Nếu thím đi theo đường DBRMS, thì khi design db nên chú trọng vào relationship, chuẩn hóa table, OLTP, transaction insert, isolation level.
Nếu làm với Data Warehouse thì nên chú trọng tới DWH design (Kimbal, Inmon), ELT tool + pipeline + audit, handle SCD, Cube.
hoacnguyengiap
Đã tốn tiền
Bài toán oltp với bài toán olap đương nhiên có cách tiếp cận khác nhau.
Làm report thì dùng dw, bảng nhiều cột là đúng rồi.
Còn bình thường, oltp chẳng ai thiết kế bảng mấy trăm col cả.
Làm report thì dùng dw, bảng nhiều cột là đúng rồi.
Còn bình thường, oltp chẳng ai thiết kế bảng mấy trăm col cả.
Agatha Christie
Senior Member
Với những nghiệp vụ CRUD bình thường thì việc tạo 1 table cả trăm column để tránh phải join nhiều bảng xong lúc query chỉ dám viết raw query lên vài item thì DB thiết kế quá tệ. Bơi ra khỏi cái ao làng đi các anh. Làm như tôi chưa từng làm nghiệp vụ ngân hàng bao h à, cỡ nào cũng split table dùng normalized và denormalized column dc hết nhé. Chưa kể mà bán product theo kiểu onpremise khách hàng đòi dùng db khác, hoặc muốn migrate sang 1 loại db khác, app nào viết raw sql có mà khóc tiếng Mán trong khi dùng ORM chỉ cần đổi driver cái 1, còn lại sửa chả hết nhiêu. 
Mấy thím làm report thì 1 bảng report lên tới hàng trăm column thì k nói rồi, vs cả k ai dùng ORM để làm report cả, 100% là raw query. Và thật ra k có thằng report nào dám dùng trực tiếp DB nghiệp vụ để build report cả, cỡ nào cũng có 1 bước build lại data
. Tôi k làm về mảng report, BI này nọ nhưng thật ra cũng k nắm dc idea của mấy ng viết report vs cái thiết kế cả trăm column và những câu sql k tưởng 
Mấy thím làm report thì 1 bảng report lên tới hàng trăm column thì k nói rồi, vs cả k ai dùng ORM để làm report cả, 100% là raw query. Và thật ra k có thằng report nào dám dùng trực tiếp DB nghiệp vụ để build report cả, cỡ nào cũng có 1 bước build lại data
. Tôi k làm về mảng report, BI này nọ nhưng thật ra cũng k nắm dc idea của mấy ng viết report vs cái thiết kế cả trăm column và những câu sql k tưởng 
ez-aqua
Senior Member
Vừa thấy mấy tranh cãi về vụ bảng 200 cột với tách thành nhiều bảng (mình đoán chắc ý là kĩ thuậtvertical partition) rất hay.
Mình sẽ giải thích về vấn đề này. Lưu ý là những thứ mình sẽ nói ở đây là với SQL Server của Microsoft, có thể nó sẽ không đúng với những database khác.
Có 1 số database (thường là các database cho application), có khuynh hướng seek key (Tìm 1 dòng hoặc 1 số dòng nào đó bằng 1 ID. Đánh index trên 1 bảng để seek row bằng key đó). Với những bảng/index này thì sẽ là dạng rowstore.
Mà rowstore thì dù bạn có select 1, 2 cột trên 1 row thì khi đọc 1 row thì bạn cũng phải đọc hết 1 row mà chính xác là phải đọc hết 1 page chứa row đó (IO của SQL Server thực hiện ở page level). 1 page trong SQL Server database có thể chứa tối đa là 8KB. Nếu 1 row có độ lớn hơn 8kb thì phải có 1 pointer dẫn đến 1 page khác,
https://docs.microsoft.com/en-us/sq...ents-architecture-guide?view=sql-server-ver15
Nếu 1 để 1 row quá dài thì bạn sẽ phải cần nhiều hơn 1 page để lưu 1 row. Bạn phải đọc row chính rồi dùng pointer để tìm đến các phần data còn lại.
Nếu khi bạn seek mà kết quả trả về nhiều row (VD bạn đánh nonclustered index lên cột ngày của 1 bảng rồi bạn seek range theo ngày chẳng hạn), hoặc bạn thực hiện table/index scan. Thì page càng nhiều thì cost IO càng lớn. Bạn có select 1 cột thì disk IO (có thể check bằng SET Statistics IO ON) vẫn thế. (Vì nó phải đọc nguyên 1 page).
Như vậy, người ta có thể nghĩ đến việc dùng kĩ thuật vertical partitioning để tách 1 thực thể ra thành nhiều bảng. VD là Email đi, và bạn có 1 non clustered index theo ngày. Nếu bạn ko tách làm 2 thì khi bạn select EmailID, SenderID rồi where theo ngày, SQL Server khi read phải read toàn bộ page của các dòng email. Rồi sau đó chỉ nhặt ra mỗi EmailID và SenderID. Tưởng tượng IO sẽ khủng khiếp thế nào nếu email nào cũng có header và content trung bình dài cỡ chừng tổng cộng 1000 kí tự.
Thì có thể người ta sẽ tách. VD tiêu đề và nội dung sẽ ra 1 bảng và có thêm EmailID, VD bảng đó sẽ là EmailContent. Còn 1 bảng chỉ có các key thường được truy cập thì có 1 bảng riêng VD bảng đó sẽ là EmailGeneral. Hầu hết trên mọi trường hợp thì sẽ chỉ làm việc trên bảng EmailGeneral. Còn khi nào cần hiện nội dung email thì mới vô bảng EmailContent và dùng EmailID để seek.
Tất nhiên để khắc phục tình trạng này người ta có thể dùng Filtered Index. Tuy nhiên Filtered Index thì có thể sẽ ra tình trạng tăng dung lượng ổ cứng và thêm index thì rõ ràng sẽ làm việc insert/update vào bảng đó lâu hơn. (Do nó còn phải maintain các nonclustered index nữa.)
Tuy nhiên với 1 số mục đích database, nó lại có đặc thù là không cần seek key, mà lại là scan toàn bảng hoặc 1 partition, rồi filter và group by trên 1 số field nào đó. (VD như các database về các mục đích analytics).
Với các database kiểu này, thường người ta sẽ sử dụng columnstore. Với columnstore, thay vì phải đọc nguyên 1 row hay nguyên 1 page chứa row đó, data sẽ được tổ chức theo dạng column, khi câu query cần column nào, nó sẽ chỉ đọc các page liên quan đến column đó.
https://docs.microsoft.com/en-us/sq...nstore-indexes-overview?view=sql-server-ver15
Như vậy vấn đề row có lớn hay không giờ không quan trọng nữa. Tuy nhiên, do yêu cầu của query trên các database này thường ko phải là seek key mà lại lại filter aggregate.
Giả sử ta chia đơn hàng hành 2 bảng, là Order Header và OrderDetail.
Giả sử ta có query là tìm ra top 10 Buyer theo Amount của những đơn hàng được thực hiện vào tháng 1 năm 2021. Bạn sẽ có query kiểu
SELECT TOP 10 BuyerName, SUM(Amount)
FROM OrderHeader
LEFT JOIN OrderDetail ON OrderHeader.OrderID = OrderDetail.OrderID
WHERE OrderDate> '2021-01-01' AND OrderDate < '2021-02-01'
GROUP BY BuyerName
Vấn đề cái trên chỉ là logical query. Còn SQL thực sự bên dưới sẽ phải: (Giả sử là columnstore index, không có partition)
Kinh nghiệm của mình khi làm việc với database, query cực chậm chậm thường chỉ có 2 nguyên nhân spill ram vì sort/hash (được tạo ra từ join (nghĩa là bao gồm cả In và Exists, vì thực tế 2 thằng này là semi và anti join), window function, aggregate) và naive nested loop O(n^2) (gây ra từ inequal join hoặc SQL Server chọn nhầm plan ngu) với cả inner và outer đều cực lớn
. Nested loop thường ko có spill, vì space complexity chỉ là O(1), vấn đề là nó cả inner với outer chỉ cần cỡ 1.000.000 row ở 1 mỗi bảng thì đảm bảo là nó chạy đến tết. (1.000.000x1.000.000 = 1000 tỉ thì phải)
Thế nên là với mấy database dạng analytics datawarehouse gì đó thì người ta de-normalize xuống 1 bảng vài trăm cột là vì vậy. Và với database application người ta dùng vertical partitioning hay bảng cha con kiểu Header-Detail cũng là vì vậy.
Mình hiện ko có database có thể chạy được columnstore để test nên ko demo execution plan được. Mong các bạn thông cảm
Mình sẽ giải thích về vấn đề này. Lưu ý là những thứ mình sẽ nói ở đây là với SQL Server của Microsoft, có thể nó sẽ không đúng với những database khác.
Có 1 số database (thường là các database cho application), có khuynh hướng seek key (Tìm 1 dòng hoặc 1 số dòng nào đó bằng 1 ID. Đánh index trên 1 bảng để seek row bằng key đó). Với những bảng/index này thì sẽ là dạng rowstore.
Mà rowstore thì dù bạn có select 1, 2 cột trên 1 row thì khi đọc 1 row thì bạn cũng phải đọc hết 1 row mà chính xác là phải đọc hết 1 page chứa row đó (IO của SQL Server thực hiện ở page level). 1 page trong SQL Server database có thể chứa tối đa là 8KB. Nếu 1 row có độ lớn hơn 8kb thì phải có 1 pointer dẫn đến 1 page khác,
https://docs.microsoft.com/en-us/sq...ents-architecture-guide?view=sql-server-ver15
Nếu 1 để 1 row quá dài thì bạn sẽ phải cần nhiều hơn 1 page để lưu 1 row. Bạn phải đọc row chính rồi dùng pointer để tìm đến các phần data còn lại.
Nếu khi bạn seek mà kết quả trả về nhiều row (VD bạn đánh nonclustered index lên cột ngày của 1 bảng rồi bạn seek range theo ngày chẳng hạn), hoặc bạn thực hiện table/index scan. Thì page càng nhiều thì cost IO càng lớn. Bạn có select 1 cột thì disk IO (có thể check bằng SET Statistics IO ON) vẫn thế. (Vì nó phải đọc nguyên 1 page).
Như vậy, người ta có thể nghĩ đến việc dùng kĩ thuật vertical partitioning để tách 1 thực thể ra thành nhiều bảng. VD là Email đi, và bạn có 1 non clustered index theo ngày. Nếu bạn ko tách làm 2 thì khi bạn select EmailID, SenderID rồi where theo ngày, SQL Server khi read phải read toàn bộ page của các dòng email. Rồi sau đó chỉ nhặt ra mỗi EmailID và SenderID. Tưởng tượng IO sẽ khủng khiếp thế nào nếu email nào cũng có header và content trung bình dài cỡ chừng tổng cộng 1000 kí tự.
Thì có thể người ta sẽ tách. VD tiêu đề và nội dung sẽ ra 1 bảng và có thêm EmailID, VD bảng đó sẽ là EmailContent. Còn 1 bảng chỉ có các key thường được truy cập thì có 1 bảng riêng VD bảng đó sẽ là EmailGeneral. Hầu hết trên mọi trường hợp thì sẽ chỉ làm việc trên bảng EmailGeneral. Còn khi nào cần hiện nội dung email thì mới vô bảng EmailContent và dùng EmailID để seek.
Tất nhiên để khắc phục tình trạng này người ta có thể dùng Filtered Index. Tuy nhiên Filtered Index thì có thể sẽ ra tình trạng tăng dung lượng ổ cứng và thêm index thì rõ ràng sẽ làm việc insert/update vào bảng đó lâu hơn. (Do nó còn phải maintain các nonclustered index nữa.)
Tuy nhiên với 1 số mục đích database, nó lại có đặc thù là không cần seek key, mà lại là scan toàn bảng hoặc 1 partition, rồi filter và group by trên 1 số field nào đó. (VD như các database về các mục đích analytics).
Với các database kiểu này, thường người ta sẽ sử dụng columnstore. Với columnstore, thay vì phải đọc nguyên 1 row hay nguyên 1 page chứa row đó, data sẽ được tổ chức theo dạng column, khi câu query cần column nào, nó sẽ chỉ đọc các page liên quan đến column đó.
https://docs.microsoft.com/en-us/sq...nstore-indexes-overview?view=sql-server-ver15
Như vậy vấn đề row có lớn hay không giờ không quan trọng nữa. Tuy nhiên, do yêu cầu của query trên các database này thường ko phải là seek key mà lại lại filter aggregate.
Giả sử ta chia đơn hàng hành 2 bảng, là Order Header và OrderDetail.
- Bảng OrderHeader có OrderID, OrderDate, BuyerName
- Bảng OrderDetail có OrderDetailID, OrderID, ProductID, Amount
Giả sử ta có query là tìm ra top 10 Buyer theo Amount của những đơn hàng được thực hiện vào tháng 1 năm 2021. Bạn sẽ có query kiểu
SELECT TOP 10 BuyerName, SUM(Amount)
FROM OrderHeader
LEFT JOIN OrderDetail ON OrderHeader.OrderID = OrderDetail.OrderID
WHERE OrderDate> '2021-01-01' AND OrderDate < '2021-02-01'
GROUP BY BuyerName
Vấn đề cái trên chỉ là logical query. Còn SQL thực sự bên dưới sẽ phải: (Giả sử là columnstore index, không có partition)
- Quét toàn bộ (columnstore index scan) bảng Order, lấy ra tất cả các row có orderdate đạt chuẩn, project ra column BuyerName và OrderID. Số lượng row có sẽ giảm nhưng cũng vẫn sẽ lớn vì số đơn hàng trong 1 tháng không thể ít.
- Quét Lấy toàn bộ (columnstore index scan) bảng Detail. (project ra column OrderID, Amount). Bảng có bao nhiêu row là ra hết bấy nhiêu. (Vì có filter bớt được cái gì đi đâu, do điều kiện filter là OrderDate chỉ có ở bảng Header chứ ko có ở bảng detail). SQL Server có thể áp dụng Bitmap operator để giảm số lượng row trước khi vào Hash join operator, tuy nhiên bitmap chỉ xuất hiện khi query chạy parallel và tất nhiên vẫn sẽ tốn kém resource.
- Join 2 bảng. Physical join operator là giả sử là hash join (trường hợp này thường sẽ là hash join vì nhanh nhât O(n) so với nested loop (O(n^2) - Ở đây ko có rowstore index có sort nên không thể là nested loop - index seek O(nlogn). Còn merge join thì lại chậm hơn hash (O(nlog(n))))). Thì hash join mọi người cũng biết có độ phức tạp là O(n). Nghĩa là 2 bảng càng lớn thì join càng khủng. Ngoài ra hash có space complexity là O(n). Khi hash join thì 1 trong 2 bảng sẽ được chọn ra để build hash table. Thường thì SQL Server sẽ thông minh và sẽ chọn bảng nhỏ hơn. Nhưng vấn đề là khi vertitcal partition hay kiểu bảng cha con Order-Detail thì 2 bên cái nào cũng lớn cả. => Xài rất nhiều ram -> Spill - Nguyên nhân chủ yếu nhất khiến 1 câu query chạy cực chậm.
- Join xong lấy đủ các data cần thiết (BuyerName từ bảng Header và Amount từ bảng Detail) rồi thì mới thực hiện aggregate (thường là hash aggregate vì nhanh hơn 0(n) so với Stream Aggregate (cần sort, sort thì chậm O(nlogn)).
Kinh nghiệm của mình khi làm việc với database, query cực chậm chậm thường chỉ có 2 nguyên nhân spill ram vì sort/hash (được tạo ra từ join (nghĩa là bao gồm cả In và Exists, vì thực tế 2 thằng này là semi và anti join), window function, aggregate) và naive nested loop O(n^2) (gây ra từ inequal join hoặc SQL Server chọn nhầm plan ngu) với cả inner và outer đều cực lớn
. Nested loop thường ko có spill, vì space complexity chỉ là O(1), vấn đề là nó cả inner với outer chỉ cần cỡ 1.000.000 row ở 1 mỗi bảng thì đảm bảo là nó chạy đến tết. (1.000.000x1.000.000 = 1000 tỉ thì phải)Thế nên là với mấy database dạng analytics datawarehouse gì đó thì người ta de-normalize xuống 1 bảng vài trăm cột là vì vậy. Và với database application người ta dùng vertical partitioning hay bảng cha con kiểu Header-Detail cũng là vì vậy.
Mình hiện ko có database có thể chạy được columnstore để test nên ko demo execution plan được. Mong các bạn thông cảm
Tệp đính kèm
Sửa lần cuối:
Agatha Christie
Senior Member
Cảm ơn thím đã thông não vụ tại sao report phải dùng table khủng vậy
ez-aqua
Senior Member
Ý bác là 10 column từ 10 table 20 column à.
Chuyện nhiều cột hay không với mình nó không nhiều ý nghĩa. Nếu mà bạn có 100 cột hay 1000 cột mà data toàn NULL thì IO vẫn vậy. (Hoặc có thể nó tăng nhưng cực ít).
Chứ chỉ cần 2-3 cột thôi mà có 1 cột nvarchar max và lưu cả 1 cuốn tiểu thuyết trong đó thì nó lại là 1 vấn đề rất khác.
. Vấn đề nó là thế này. Row nặng mà thì sẽ cần nhiều page để lưu. Mà page sẽ quyết định IO cost.VD mình có query và bảng sau
Giả sử mình UPDATE lại giá trị của bảng là 1 cái string dài khủng bố rồi chạy lại câu query.
IO đã tăng vọt. Bạn chú ý là bảng của mình đang là rowstore (Dòng Storage)
Cho dù query của bạn không select cái field dài bằng cuốn tiểu thuyết kia thì IO vẫn sẽ rất cao. (Chú ý, bạn chủ thớt select * thì vấn đề có thể mà bạn ấy sẽ gặp rắc rối là network movement chứ ko phải là IO cost. Vì bạn ấy sẽ mang dữ liệu ko cần thiết truyền từ database sang tầng application. Ở đây mình đang nói về IO)
Vấn đề ở đây là thường nhiều col nó sẽ có khuynh hướng khiến cho row bạn nặng chứ không phải cứ nhiều col sẽ thì dở. Nhiều col mà toàn NULL thì IO cost vẫn sẽ như vậy thôi.
Bạn thử dập con columnstore vào cái bảng lớn đó xem sao. Nếu hiện nay mà bạn đang rowstore và bạn chuyển qua columnstore, bạn có partition theo quý và query của bạn là dạng filter aggregate (nghĩa là ko xuất ra report dạng detail mà chỉ là doanh số group by gì đó) thì mình nghĩ có khi kết quả sẽ là theo giây luôn đấy.
Nói chung là nói về performance, tốt nhất là phải đọc được execution plan để hiểu chính xác nhất. Chứ kinh nghiệm truyền miệng kiểu EXISTS nhanh hơn IN, hay mấy vụ nhiều column thì chắc chắn sẽ chậm thì ko đúng đâu. (IN với EXISTS nhìn trong exists trong plan đều là semi join, cost y chang nhau thì làm gì có chuyện nhanh hơn với chả chậm hơn
).Bảng phẳng siêu lớn thì nó cũng có cái hay và cũng có cái dở của nó. Bảng phẳng thì eliminate hoàn toàn được join thì performance chắc chắn là best rồi. Nhưng với mình mình thích dimensional modeling hơn. Dimensional modeling làm đúng thì sẽ chỉ là join bảng số lượng nhỏ với bảng lớn, và nếu join kiểu này thường cũng chẳng bao giờ có vấn đề gì về performance. Time và space complexity đúng là O(n) nhưng space thì bảng nhỏ làm hash thì vô tư về vụ spill RAM, còn time thì cũng thoải mái vì hash join bảng lớn với bảng nhỏ nó distribute bảng nhỏ rồi chạy parallel đa luồng probe vào bảng nhỏ đã được distribute vào từng thread nhanh vô địch. Lại được bao nhiêu lợi ích khác từ các bảng dimension độc lập.
Để biết PowerBI direct query nó tạo ra câu query gì và câu query thực sự đang được SQL Server chạy thế nào thì bạn có thể xem thử cái này
Thấy query nào mà trông nó kì kì, không giống người viết thì chính là query do PowerBI tạo ra.
Rồi chạy thử xem execution plan nó chạy ra sao rồi phán là chuẩn nhất.
Đang thất nghiệp lessor nằm nhà bắt đầu từ hôm nay nên giờ rảnh lắm. Bạn nào cần thì mình hướng dẫn cách đọc execution plan cho.
Sẵn tiện ai đó săn đầu người thấy cần thì inbox cho mình
.
Sửa lần cuối:
Hellscream
Đã tốn tiền
MSSQL thì không rõ làm được không. Chơi Multi Master thì nhiều thằng làm được mà :-?
Hướng dẫn đọc luôn đi thím.
Chủ đề tương tự
