یوسف زالی
چهارشنبه 04 دی 1392, 12:13 عصر
سلام.
دوستان از دو روش متفاوت کوئری گرفته شده و نتایج این هاست:
کوئری ها
select *
from Tbl1 A
join (select * from Tbl2) B on A.id = B.fid
select *
from Tbl1 A
cross apply (select * from Tbl2 where A.id = fid) B
روش Cross Apply
(31911 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalOrderAddOns'. Scan count 15543, logical reads 93961, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrderInvs'. Scan count 8058, logical reads 16255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrders'. Scan count 1, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 608 ms, elapsed time = 1125 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
روش Join
(31911 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalOrderAddOns'. Scan count 2, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrderInvs'. Scan count 2, logical reads 107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrders'. Scan count 1, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 1721 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
کدام روش بهتر است و چرا؟
تعداد Read ها خیلی متفاوته ولی زمان اونی که Read بیشتر داره کمتره!
اصولا این دو روش رو مقایسه کنید.
ممنون.
دوستان از دو روش متفاوت کوئری گرفته شده و نتایج این هاست:
کوئری ها
select *
from Tbl1 A
join (select * from Tbl2) B on A.id = B.fid
select *
from Tbl1 A
cross apply (select * from Tbl2 where A.id = fid) B
روش Cross Apply
(31911 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalOrderAddOns'. Scan count 15543, logical reads 93961, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrderInvs'. Scan count 8058, logical reads 16255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrders'. Scan count 1, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 608 ms, elapsed time = 1125 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
روش Join
(31911 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalOrderAddOns'. Scan count 2, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrderInvs'. Scan count 2, logical reads 107, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SALOrders'. Scan count 1, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 1721 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
کدام روش بهتر است و چرا؟
تعداد Read ها خیلی متفاوته ولی زمان اونی که Read بیشتر داره کمتره!
اصولا این دو روش رو مقایسه کنید.
ممنون.