הבלוג של גרי רשף

01/07/2010

פניה לטבלאות באמצעות Recursive CTE

Filed under: Uncategorized — תגיות: — גרי רשף @ 10:13

בפוסט קודם שלי הדגמתי כיצד ניתן לפנות לטבלאות באמצעות CTE רקורסיבי.
איציק בן-גן פירסם השבוע אתגר מעניין (כרגיל): נתונות שתי טבלאות ויש למצוא לכל ערך מהראשונה את זה הקרוב אליו ביותר מהשניה.
כתבתי פתרון המתבסס על CTE רקורסיבי, שיש לו בעייה פעוטה- הוא איטי להחריד, וזמן הריצה עלול להגיע לשעות, בשעה שפתרון חלופי שהוצע שם מסתיים תוך כשניה..

מדוע זה קורה? נשווה שני פתרונות לשליפת נתונים מטבלה- הפתרון המתבקש בעזרת Select * From Tbl והפתרון המתחכם באמצעות CTE רקורסיבי:

SET STATISTICS Time ON;

SET STATISTICS IO ON;

Select    *

From    AdventureWorks.Production.TransactionHistoryArchive;

With T As

(Select    Row_Number() Over(Order By TransactionID) Mispar,

        *

From    AdventureWorks.Production.TransactionHistoryArchive),

T1 As

(Select    *

From    T

Where    Mispar=1

Union All

Select    T.*

From    T

Inner Join T1

        On T.Mispar=T1.Mispar+1)

Select    *

From    T1

option (MaxRecursion 0);

SET STATISTICS Time Off;

SET STATISTICS IO Off;

והפלט הטכני:

———————————————————————————————————————————————————

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 0 ms.

(1069 row(s) affected)

Table 'ProductInventory'. Scan count 1, logical reads 9, physical reads 2, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 264 ms.

———————————————————————————————————————————————————

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 0 ms.

(1069 row(s) affected)

Table 'Worktable'. Scan count 2, logical reads 6415, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ProductInventory'. Scan count 2, logical reads 5069, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 750 ms,  elapsed time = 1102 ms.

———————————————————————————————————————————————————

שורה תחתונה- זמן הריצה היה ארוך במקרה של CTE פי ארבעה (264 – 1102 באלפיות שניה),

השימוש ב-CPU היה 0 לעומת 750,

והסיבה לכל זה הוא מספר ה-Logical Reads (קריאת נתונים מהטבלה שב-Cache) שגדל מ-9 לכ-11500=5069+6415.

כאן זה עוד בזול, וכשמצרפים Joins – הפער הולך וגדל.

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: