[THI] Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu 65,532 dòng

[Nguyễn Duy Tuân]

Cuộc thi tạo sổ tổ hợp nhập xuất tồn trong Excel tốc độ nhanh nhất

MỤC ĐÍCH
Trao đổi học tập để cùng nâng cao trình độ lập trình VBA về tối ưu code chạy nhanh và rõ ràng.

ĐỐI TƯỢNG THAM GIA
Là tất cả các thành viên GPE từ thành viên thường đến các Admin của GPE
Tôi cũng tham gia. Thực tế tôi đã viết code lâu rồi để phục vụ công việc quản lý kho, bản thân thấy chạy khá nhanh nhưng vẫn tin nó chưa phải hoàn hảo.
Nếu code của ai tối ưu nhất hoặc rõ ràng nhất sẽ trình bày code và giải thích cặn kẽ kỹ thuật để làm được ra nó trong topic này để mọi người tham khảo và học hỏi.

GIẢI THƯỞNG
Giải thưởng là cho tất cả thành viên của diễn đàn GPE được các bài học tốt về lập trình VBA trong Excel trong việc làm sổ sách tổng hợp.

THỜI GIAN DỰ THI, GỬI BÀI VÀ CÔNG BỐ
Dự thi từ ngày 10/02/2014.
Bài gửi chậm nhất là 12hAM ngày 15/02/2014.
Thời gian công bố kết quả đánh giá 14h 17/02/2014
Tất cả các bài dự thi, kết quả đánh giá sẽ được upload lên trang đầu của topic này.

Các bạn nén file đáp án rồi gửi bài vào email:
duytuan@bluesofts.net hoặc email của một thành viên BQT GPE (tôi bổ sung sau)
(Tôi sẽ là người nộp sớm nhất không sợ copy của người khác )

ĐỀ BÀI:
Tôi cung cấp tập tin dữ liệu với 65,532 dòng cùng module chứa các hàm và thủ tục đo tốc tộ, cấu trúc lệnh.
Bảng dữ liệu:

Nếu các bạn thắc mắc về phương pháp lập sổ tôi sẽ giải thích bài sau
Cấu trúc code:
[GPECODE=vb]Sub DoThoiGian()
Dim T1@, T2@, Freq@, Overhead@
QueryPerformanceFrequency Freq
QueryPerformanceCounter T1
QueryPerformanceCounter T2
Overhead = T2 - T1
QueryPerformanceCounter T1

'Thủ tuc của bạn

LapSo 'Thủ tuc của bạn phải làm

'Kết thúc chạy, đo thời gian thực hiện
QueryPerformanceCounter T2
'Debug.Print (T2 - T1 - Overhead) / Freq * 1000; "milliseconds(ms)"
MsgBox "milliseconds(ms): " & (T2 - T1 - Overhead) / Freq * 1000
End Sub[/GPECODE]


DoThoiGian là thủ tục mẹ được gán vào nút lệnh "Thực hiện" trên bảng tính. Nội dung trong thủ tục này bạn không được sửa. Bạn cần phải tạo thủ tục LapSo để lập sổ tổng hợp NXT.

[GPECODE=vb]Sub LapSo()
'Code của bạn để tạo ra sổ
End Sub[/GPECODE]

Kết quả thực hiện phải ra được sổ có cấu trúc và dữ liệu như sau

Lưu ý, sổ mẫu đã được định dạng vì vậy bạn không cần viết code để định dạng để giảm các yếu tốt ảnh hưởng tới tốc độ của code.

(Nếu bạn không biết lập trình VBA có thể lập công thức Excel thông thường. Tuy nhiên nó có thể được dùng để so sánh giữa lập trình VBA "thiện chiến" thế nào với cách lập công thức Excel thông thường mà thôi).

[TIP]Hướng dẫn tính toán
Các thành viên lưu ý. Sheet "Setting" có thông tin về ngày lập sổ: Từ ngày...đến ngày với các name NGAY1, NGAY2. Điều kiện để lập sổ phải dựa vào thời gian và Loại_phieu

Lượng Tồn đầu = lượng nhập với ngày < NGAY1 - lượng xuất với ngày < NGAY1
Lượng Nhập trong kỳ = lượng nhập với ngày >= NGAY1 và ngày <= NGAY2
Lượng Xuất trong kỳ = lượng xuất với ngày >= NGAY1 và ngày <= NGAY2
Lượng tồn cuối = Lượng Tồn đầu + Lượng Nhập trong kỳ - Lượng Xuất trong kỳ

Tương tự khi tính giá trị...[/TIP]

TIÊU CHÍ ĐÁNH GIÁ
Tìm ra các code đạt tốc độ nhanh nhất. Các bài làm cố gắng trình bày dễ hiểu và kèm comment trong code để giải thích.
Tất cả các bài với các phương pháp khác nhau cũng sẽ đăng lên để chúng ta học được nhiều phương pháp từ đó có thể vận dụng linh hoạt trong các việc khác.

Xin nói trước với các bạn là ta có thể đánh giá ở mức tương đối. Tất cả các code sẽ chạy trên một máy tính. Excel sẽ được khởi động lại với mỗi code mới, mỗi code được chạy 3 lần rồi lấy tốc độ trung bình. Tất cả các bài dự thi được upload lên đây để tất cả mọi người tham khảo.

Với tinh thần cầu thị, tạo sân chơi chung cho mọi người tôi rất mong chúng ta cùng tham gia. Mong các thành viên đừng e ngại về trình độ của mình thế này thế khác, cứ xác định tham gia để học để biết mình đã làm được gì và cần cải tiến cái gì về lập trình VBA.

-----------------
Đã có bài tổng hợp kết quả test và các file có mã nguồn của các tác giả gửi. Các thành viên xem bài #175 để download.
-----------------

 

[Hoàng Trọng Nghĩa]

Đang đợi các thành viên khác viết xem để học hỏi thêm, tuy nhiên chưa thấy bài nào, mình xin mở đầu, như các bạn cũng đã biết tốc độ của nó sẽ kém hơn nhiều, nhưng ADO cũng là 1 giải pháp. Mong các bạn cải thiện để code ngày càng nhanh hơn.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[SLG],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0)) AS TONDK, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[THANH_TIEN],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0)) AS TTDK, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[SLG],0))) AS NHAP, " & _
"Sum((IIf([kho].[Ngay_CT] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS XUAT, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI"
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]

Thử với 1 triệu dòng, có khi đây là giải pháp tốt hơn! Khả năng chậm là do thời gian kết nối.

 

[Hai Lúa Miền Tây]

Thử với 1 triệu dòng, có khi đây là giải pháp tốt hơn! Khả năng chậm là do thời gian kết nối.

Em nghĩ nó sẽ không đáng kể, nó tốn thời gian nhiều là chổ group để cộng.

 

[VetMini]

Tôi không có đủ tài nguyên để chạy thử. Chỉ nhìn tổng quan trên code SQL.

1. Hình như code không đảm bảo giữ được thứ tự của mã hàng trước và sau khi tổng kết.

2. Bác thử chọn các dòng date2 kể về trước cho kho xem có cải tiến tốc độ không?

" FROM KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
" AND KHO.[Ngay_ct] <= #" & strNgay2 & "# " & _
" GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI"

(Nếu chọn trước thì phần IIF ở trên chỉ cần so với strNgay1 thôi chứ không cần phải so với strNgay2 nữa)

Mẹo nhỏ: trong cái string câu lệnh SQL, mỗi lần xuống dòng mới nguời ta chừa một khoảng trắng. Như vậy tránh được lỗi thường gặp khi quên chừa khoảng trắng ở dòng trước đó.

 

[Hoàng Trọng Nghĩa]

Mẹo nhỏ: trong cái string câu lệnh SQL, mỗi lần xuống dòng mới nguời ta chừa một khoảng trắng. Như vậy tránh được lỗi thường gặp khi quên chừa khoảng trắng ở dòng trước đó.

HLMT có chừa khoảng trắng cuối dòng mà VetMini? Vã lại, khi có thói quen trước hay sau, thì người ta sẽ không quên quá trình mình thực hiện được đâu.

 

[VetMini]

HLMT có chừa khoảng trắng cuối dòng mà VetMini? Vã lại, khi có thói quen trước hay sau, thì người ta sẽ không quên quá trình mình thực hiện được đâu.

Đây không phải là mẹo của riêng tôi. Tất cả những người chuyên viết string SQL động (dynamic sql string) đều biết mẹo này:
- Luôn luôn thêm một khoảng trống trước cái string được nối thêm, trừ phi thêm như thế gây ra việc ngắt từ.


====== Bổ sung thêm:

...khi có thói quen trước hay sau, thì người ta sẽ không quên quá trình mình thực hiện được đâu...

Câu này không áp dụng được đối với người chuyên copy code về chỉnh sửa lại. Như tôi chẳng hạn.

 

[Hai Lúa Miền Tây]

....

2. Bác thử chọn các dòng date2 kể về trước cho kho xem có cải tiến tốc độ không?

" FROM KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
" AND KHO.[Ngay_ct] <= #" & strNgay2 & "# " & _
" GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI"
....

Chưa thử nhưng hình như nó có gì đó khộng ổn.

 

[Hai Lúa Miền Tây]

Cải tiến lại chút khi chọn khoảng thời gian, chỉ lấy những mặt hàng trong khoảng thời gian thoả điều kiện, tốc độ tuỳ thuộc vào dữ liệu trong khoảng thời gian đó nhiều hay ít. Tuy nhiên tốc độ cũng chưa được ưng ý. Nhờ các bạn cải tiến thêm.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi, " & _
"Sum((IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[SLG],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS TONDK, " & _
"Sum((IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[THANH_TIEN],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTDK, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [LOAI_PHIEU]='N',[KHO].[SLG],0))) AS NHAP, " & _
"Sum((IIf([kho].[Ngay_CT]>=#" & strNgay1 & "# And [LOAI_PHIEU]='N',[KHO].[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS XUAT, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM (SELECT KHO.NGAY_CT, KHO.MA_VLSPHH, KHO.LOAI_PHIEU, KHO.SLG, KHO.THANH_TIEN " & _
"FROM KHO " & _
"WHERE KHO.NGAY_CT<=#" & strNgay2 & "#) AS A " & _
"INNER JOIN DMVLSPHH ON A.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi"
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]

 

[VetMini]

Chưa thử nhưng hình như nó có gì đó khộng ổn.

Tôi cũng chưa thử. Chỉ dùng trên nguyên tắc thêm một điều kiện vào trong mệnh đề JOIN để nó tự lọc. Cái này dùng được cho TSQL nhưng tôi chưa thử được trên Access nên chưa biết.

1. Lúc thêm cái subquery;
"FROM (SELECT KHO.NGAY_CT, KHO.MA_VLSPHH, KHO.LOAI_PHIEU, KHO.SLG, KHO.THANH_TIEN " & _
"FROM KHO " & _
"WHERE KHO.NGAY_CT<=#" & strNgay2 & "#) AS A " & _

Nếu bạn dùng "AS [KHO]" thì sẽ không phải chỉnh sửa các phần tên tự (alias) ở những chỗ khác.

2. Nếu trong khoảng thời gian trên mã hàng nào không có biến dộng thì nó sẽ bị mất. Thông thường thì báo cáo người ta phải báo cáo đầy đủ. Trường hợp này thì dùng RIGHT OUTER JOIN hữu lý hơn. Tôi chỉ nói theo thông lệ báo cáo, nhưng hình như đề bài chỉ báo cáo những mặt hàng có biến động cho nên dùng INNER JOIN như bạn là đúng rồi.

 

[Hai Lúa Miền Tây]

2. Nếu trong khoảng thời gian trên mã hàng nào không có biến dộng thì nó sẽ bị mất. Thông thường thì báo cáo người ta phải báo cáo đầy đủ. Trường hợp này thì dùng RIGHT OUTER JOIN hữu lý hơn. Tôi chỉ nói theo thông lệ báo cáo, nhưng hình như đề bài chỉ báo cáo những mặt hàng có biến động cho nên dùng INNER JOIN như bạn là đúng rồi.

Tôi đã biết vấn đề này, tuy nhiên theo yêu cầu đề tài cũng như "tăng tốc" khi chạy code nên tôi đã không dùng nó. Còn nếu như dùng nó thì tốc độ cũng sẽ bị giảm đi đó.

 

[VetMini]

Còn về vấn đề "không đảm bảo trật tự của mã hàng trong bảng mẫu báo cáo trước khi tổng hợp" như tôi đã nêu ra trước đây là đúng hay sai? Nếu đúng thì bạn có cách khắc phục chưa?

 

[Hai Lúa Miền Tây]

Còn về vấn đề "không đảm bảo trật tự của mã hàng trong bảng mẫu báo cáo trước khi tổng hợp" như tôi đã nêu ra trước đây là đúng hay sai? Nếu đúng thì bạn có cách khắc phục chưa?

Ở đây nó sẽ tự sắp xếp theo thứ tự tăng dần, theo bạn thì phải sắp xếp như thế nào mới hợp lý.

 

[Nguyễn Duy Tuân]

Đang đợi các thành viên khác viết xem để học hỏi thêm, tuy nhiên chưa thấy bài nào, mình xin mở đầu, như các bạn cũng đã biết tốc độ của nó sẽ kém hơn nhiều, nhưng ADO cũng là 1 giải pháp. Mong các bạn cải thiện để code ngày càng nhanh hơn.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[SLG],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0)) AS TONDK, " & _
"Sum(IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[THANH_TIEN],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0)) AS TTDK, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[SLG],0))) AS NHAP, " & _
"Sum((IIf([kho].[Ngay_CT] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [LOAI_PHIEU]='N',[KHO].[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS XUAT, " & _
"Sum((IIf([kho].[ngay_ct] Between #" & strNgay1 & "# And #" & strNgay2 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM KHO INNER JOIN DMVLSPHH ON KHO.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY KHO.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.DVI"
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]

Với giải pháp dùng SQL ta không tính thời gian kết nối CSDL:

1. Kết nối CSDL Excel
-> Thời gian bắt đầu
2. Thực thi SQL, nhận Recordset
3. Điền dữ liệu vào bảng tính
-> Thời gian kết thúc

 

[vodoi2x]

Cải tiến lại chút khi chọn khoảng thời gian, chỉ lấy những mặt hàng trong khoảng thời gian thoả điều kiện, tốc độ tuỳ thuộc vào dữ liệu trong khoảng thời gian đó nhiều hay ít. Tuy nhiên tốc độ cũng chưa được ưng ý. Nhờ các bạn cải tiến thêm.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi, " & _
"Sum((IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[SLG],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS TONDK, " & _
"Sum((IIf([kho].[Ngay_CT]<#" & strNgay1 & "# And [kho].[loai_phieu]='N',[KHO].[THANH_TIEN],0)-IIf([kho].[ngay_ct]<#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTDK, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [LOAI_PHIEU]='N',[KHO].[SLG],0))) AS NHAP, " & _
"Sum((IIf([kho].[Ngay_CT]>=#" & strNgay1 & "# And [LOAI_PHIEU]='N',[KHO].[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[SLG],0))) AS XUAT, " & _
"Sum((IIf([kho].[ngay_ct]>=#" & strNgay1 & "# And [kho].[LOAI_PHIEU]='X',[KHO].[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM (SELECT KHO.NGAY_CT, KHO.MA_VLSPHH, KHO.LOAI_PHIEU, KHO.SLG, KHO.THANH_TIEN " & _
"FROM KHO " & _
"WHERE KHO.NGAY_CT<=#" & strNgay2 & "#) AS A " & _
"INNER JOIN DMVLSPHH ON A.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi"
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]

HLMT đã cho vào test thực sự chưa, vì test thấy code sẽ lỗi, Lỗi do thiếu chỉ ra địa chỉ cho KHO, DMVLSPHH hay ở file của HLMT thì có đặt gì thêm???

 

[Hai Lúa Miền Tây]

Với giải pháp dùng SQL ta không tính thời gian kết nối CSDL:

1. Kết nối CSDL Excel
-> Thời gian bắt đầu
2. Thực thi SQL, nhận Recordset
3. Điền dữ liệu vào bảng tính
-> Thời gian kết thúc

--> Ta có thể tạo thêm 1 sub kết nối dữ liệu bên ngoài, khi cần kết nối lại ta có thể kết nối lại. Dùng cách này sẽ bỏ qua kết nối khi mỗi lần chạy code lần sau.

Như em đã nhiều lần đưa ra ý kiến ở trên, với việc tổng hợp = ADO trên cùng file thì tốc độ sẽ thua, nhưng nếu file data từ 1 file khác thì chưa chắc đã qua ADO.

HLMT đã cho vào test thực sự chưa, vì test thấy code sẽ lỗi, Lỗi do thiếu chỉ ra địa chỉ cho KHO, DMVLSPHH hay ở file của HLMT thì có đặt gì thêm???

Name theo file anh Tuân gửi đó anh

 

[vodoi2x]

Như em đã nhiều lần đưa ra ý kiến ở trên, với việc tổng hợp = ADO trên cùng file thì tốc độ sẽ thua, nhưng nếu file data từ 1 file khác thì chưa chắc đã qua ADO.



Name theo file anh Tuân gửi đó anh

File tôi test cũng có các name đó, nhưng báo lỗi muốn chạy thì phải gán địa chỉ vào hay excel2010 không cho vậy???

Còn việc nếu database ở file khác - khi đó cũng không tính kết nối (mở file) thì ADO cũng khó theo kịp, có lẽ vì đặc thù là ngoại vụ của Excel,

Tuy vậy với bài này tốc độ ADO vẫn chấp nhận được, và lồng SQL thì có cái hay nhất là gửi lệnh thực thi (SQL) theo string, nên nếu mở rộng thì dễ thay đổi, hay cho người dùng cuối có thể tự chọn kết quả mong muốn, thông qua đưa sql string

 

[Hai Lúa Miền Tây]

File tôi test cũng có các name đó, nhưng báo lỗi muốn chạy thì phải gán địa chỉ vào hay excel2010 không cho vậy???

Còn việc nếu database ở file khác - khi đó cũng không tính kết nối (mở file) thì ADO cũng khó theo kịp, có lẽ vì đặc thù là ngoại vụ của Excel,

Tuy vậy với bài này tốc độ ADO vẫn chấp nhận được, và lồng SQL thì có cái hay nhất là gửi lệnh thực thi (SQL) theo string, nên nếu mở rộng thì dễ thay đổi, hay cho người dùng cuối có thể tự chọn kết quả mong muốn, thông qua đưa sql string

Em cũng sử dụng O2010 đây mà anh.

 

[vodoi2x]

Em cũng sử dụng O2010 đây mà anh.

Với file này chạy được rồi, tốc độ khoảng 2300 mls (ở tại lap của tôi), thực tế thế là tốc độ chấp nhận được (chớp mắt 2 lần) + code theo ADO với SQL thế này code sáng và ngắn gọn (dĩ nhiên với người biết SQL)

Tôi test kết nối thì không mất đáng kể đâu khoảng 7-10mls cho kết nối mà thôi, như vậy có lẽ thời gian thực thi SQL mới là vấn đề -- vì xét cho cùng thì (1) thực hiện SQL là ngoại vụ, (2) bản thân SQL cũng phải dịch ngôn ngữ của chính nó (như thế VBA là thứ cấp, qua SQL lại là thứ cấp lần nữa) - nên tiêu tốn thời gian ở đây (nếu lệnh SQL càng yêu cầu thời gian như Groupby ở đây thì thời gian lại tăng thêm nữa)

 

[VetMini]

Với file này chạy được rồi, tốc độ khoảng 2300 mls (ở tại lap của tôi), thực tế thế là tốc độ chấp nhận được (chớp mắt 2 lần) + code theo ADO với SQL thế này code sáng và ngắn gọn (dĩ nhiên với người biết SQL)

Tôi test kết nối thì không mất đáng kể đâu khoảng 7-10mls cho kết nối mà thôi, như vậy có lẽ thời gian thực thi SQL mới là vấn đề -- vì xét cho cùng thì (1) thực hiện SQL là ngoại vụ, (2) bản thân SQL cũng phải dịch ngôn ngữ của chính nó (như thế VBA là thứ cấp, qua SQL lại là thứ cấp lần nữa) - nên tiêu tốn thời gian ở đây (nếu lệnh SQL càng yêu cầu thời gian như Groupby ở đây thì thời gian lại tăng thêm nữa)

SQL là ngôn ngữ để làm việc trên CSDL Liên Hệ. Từ bản thân, CSDL LH vẫn có vấn đề về tốc độ.

Khi dữ liệu được xếp chuẩn theo hàng cột để có thể sử dụng lý thuyết CSDL LH thì có những cái lợi sau đây:
- Có thể thiết lập từ điển dữ liệu (data dictionary), tiếng tổng quát hơn gọi là hạ tầng dữ liệu (metadata)
- Có thể tách biệt rõ 3 tầng (layers) truy xuất (data access), trình bày (presentation), và quy trình vận hành (business model)

Nếu không chú trọng các lợi điểm trên thì sử dụng bảng tính theo kiểu CSDL LH chỉ tổ chạy chậm mà thôi. Khi ấy ADO chỉ có thể coi như một thủ thuật dùng để đọc/viết file mà không cần mở.

...code sáng và ngắn gọn (dĩ nhiên với người biết SQL)...

Kết luận như thế là hơi sớm.
Muốn biết có gọn hay không thì phải export cái sheet data sang Access và dùng câu lệnh chạy thử. Kế đó chỉnh sửa cho đến lúc gọn ghẽ dễ trông nhất. Đôi khi phải hy sinh ngắn gọn để cho nó chạy nhanh. @HTMT: Access có công cụ đo hiệu quả câu lệnh không nhỉ?

 

[vodoi2x]

SQL là ngôn ngữ để làm việc trên CSDL Liên Hệ. Từ bản thân, CSDL LH vẫn có vấn đề về tốc độ.

Khi dữ liệu được xếp chuẩn theo hàng cột để có thể sử dụng lý thuyết CSDL LH thì có những cái lợi sau đây:
- Có thể thiết lập từ điển dữ liệu (data dictionary), tiếng tổng quát hơn gọi là hạ tầng dữ liệu (metadata)
- Có thể tách biệt rõ 3 tầng (layers) truy xuất (data access), trình bày (presentation), và quy trình vận hành (business model)

Nếu không chú trọng các lợi điểm trên thì sử dụng bảng tính theo kiểu CSDL LH chỉ tổ chạy chậm mà thôi. Khi ấy ADO chỉ có thể coi như một thủ thuật dùng để đọc/viết file mà không cần mở.


Kết luận như thế là hơi sớm.
Muốn biết có gọn hay không thì phải export cái sheet data sang Access và dùng câu lệnh chạy thử. Kế đó chỉnh sửa cho đến lúc gọn ghẽ dễ trông nhất. Đôi khi phải hy sinh ngắn gọn để cho nó chạy nhanh. @HTMT: Access có công cụ đo hiệu quả câu lệnh không nhỉ?

Ý sáng gọn ở đây, hiểu là nhìn nó ngắn và gọn hơn là viết code (thuần vba ví như array) để thực hiện việc lọc , group.... thế thui. Mà không bàn về cấu trúc lệnh sql đó,

Đúng là code ngắn chưa hẳn là tối ưu, mà cần quan tâm kết quả cuối cùng và đạt mục đích

 

[Hai Lúa Miền Tây]

Với file này chạy được rồi, tốc độ khoảng 2300 mls (ở tại lap của tôi), thực tế thế là tốc độ chấp nhận được (chớp mắt 2 lần) + code theo ADO với SQL thế này code sáng và ngắn gọn (dĩ nhiên với người biết SQL)

Tôi test kết nối thì không mất đáng kể đâu khoảng 7-10mls cho kết nối mà thôi, như vậy có lẽ thời gian thực thi SQL mới là vấn đề -- vì xét cho cùng thì (1) thực hiện SQL là ngoại vụ, (2) bản thân SQL cũng phải dịch ngôn ngữ của chính nó (như thế VBA là thứ cấp, qua SQL lại là thứ cấp lần nữa) - nên tiêu tốn thời gian ở đây (nếu lệnh SQL càng yêu cầu thời gian như Groupby ở đây thì thời gian lại tăng thêm nữa)

Với giải pháp dùng SQL ta không tính thời gian kết nối CSDL:

1. Kết nối CSDL Excel
-> Thời gian bắt đầu
2. Thực thi SQL, nhận Recordset
3. Điền dữ liệu vào bảng tính
-> Thời gian kết thúc

Theo em cũng nên tính cả thời gian kết nối. Nếu file data là 1 file khác file chứa code thì tốc độ kết nối cũng như truy vấn sẽ nhanh hơn so với kết nối và truy vấn với data cùng 1 file?

Nhờ anh chị em test dùm code sau xem tốc độ có được cải thiện thêm chút nào không? Chưa vừa ý lắm.

[GPECODE=sql]Sub LapSo()
Dim cn As Object, rst As Object, strNgay1 As String, strNgay2 As String
Dim mySQL As String
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strNgay1 = Format(Range("Ngay1"), "MM-dd-yyyy")
strNgay2 = Format(Range("Ngay2"), "MM-dd-yyyy")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
mySQL = "SELECT A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi, " & _
"Sum((IIf(a.[Ngay_CT]<#" & strNgay1 & "# And a.[loai_phieu]='N',a.[SLG],0)-IIf(a.[ngay_ct]<#" & strNgay1 & "# And a.[LOAI_PHIEU]='X',a.[SLG],0))) AS TONDK, " & _
"Sum((IIf(a.[Ngay_CT]<#" & strNgay1 & "# And a.[loai_phieu]='N',a.[THANH_TIEN],0)-IIf(a.[ngay_ct]<#" & strNgay1 & "# And a.[LOAI_PHIEU]='X',a.[THANH_TIEN],0))) AS TTDK, " & _
"Sum((IIf(a.[ngay_ct]>=#" & strNgay1 & "# And a.[LOAI_PHIEU]='N',a.[SLG],0))) AS NHAP, " & _
"Sum((IIf(a.[Ngay_CT]>=#" & strNgay1 & "# And a.[LOAI_PHIEU]='N',a.[THANH_TIEN],0))) AS TTNHAP, " & _
"Sum((IIf(a.[ngay_ct]>=#" & strNgay1 & "# And a.[LOAI_PHIEU]='X',a.[SLG],0))) AS XUAT, " & _
"Sum((IIf(a.[ngay_ct]>=#" & strNgay1 & "# And a.[LOAI_PHIEU]='X',a.[THANH_TIEN],0))) AS TTXUAT, " & _
"[TONDK]+[NHAP]-[XUAT] AS TONCK, " & _
"[TTDK]+[TTNHAP]-[TTXUAT] AS TTCUOI " & _
"FROM (SELECT KHO.NGAY_CT, KHO.MA_VLSPHH, KHO.LOAI_PHIEU, KHO.SLG, KHO.THANH_TIEN " & _
"FROM KHO " & _
"WHERE KHO.NGAY_CT<=#" & strNgay2 & "#) AS A " & _
"INNER JOIN DMVLSPHH ON A.MA_VLSPHH = DMVLSPHH.MA_VLSPHH " & _
"GROUP BY A.MA_VLSPHH, DMVLSPHH.TEN, DMVLSPHH.Dvi "
Set rst = cn.Execute(mySQL)
With Sheets("THNXT")
.[C12:M23].ClearContents
.[C12].CopyFromRecordset rst
End With
rst.Close: cn.Close
Set rst = Nothing: Set cn = Nothing

End Sub

[/GPECODE]