en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
פבר28

Written by: ronen ariely
28/02/2011 08:33 RssIcon

בחירת רשמות כפולות בטבלה

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

לדוגמה: יש לנו טבלת שמות ואנחנו רוצים למצוא את כל השמות המופיעים בטבלה יותר מפעם אחת. ז"א אנחנו רוצים למצוא רשומות בהם יש לנו כפילות של שמות (שם פרטי וכן שם משפחה).

הרעיון הבסיסי הראשון בו נעבוד יהיה שימוש ב Row_number לפי השדה שרוצים לבדוק בו כפילויות (נניח שדה Name)

select
    [Name],Row_number() over(partition by [Name] order by [Name]) as [RowNum]
from @Tbl

לאחר שמיספרנו את הרשומות שלנו כל מה שנשאר זה לקחת את כל הנתונים הכפולים של Name (ז"א רשומות בהם Row_number גדול מ 1). שיטה זו קלה למימוש אבל האם היא גם יעילה?

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

1. שימוש באגריגציה פשוטה ובפונקציה COUNT הבסיסית על מנת לספור רשומות בעלות אותו שם.

2. שימוש ב Self JOIN ע ידי INNER JOIN

3. שימוש בפונקציית החלון COUNT (שימוש ב COUNT + OVER)

3.1 נציג שאילתה תוך שמוש ב CTE רק לשם הדגמה.

3.2 נפעיל שאילתה דומה תוך שימוש בשאילתת משנה / שאילתה פנימית (sub query)

* שאילתות אלו זהות לחלוטין מבחינת תוכנית ההרצה.

4. שימוש בפונקציית החלון ROW_NUMBER

את כל השאילתות שלנו נפעיל במספר מקרים שונים ונציג את תוצאות היעילות של השאילתות על פי תוכניות ההרצה שהשרת בנה לכל שאילתה.

א. שימוש בטבלה נקייה ללא אינדקסים וללא סידור של הנתונים שאנו בוחרים בסיום

ב. שימוש בטבלה נקייה ללא אינדקסים אבל ננסה הפעם להוסיף סידור של התוצאות שחוזרות (ORDER BY)

ג. שימוש בטבלה עם אינדקסים המתאימים לבחירות שלנו ועם סידור הנתונים שאנו מוצאים

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

הגיע הזמן להתחיל...

DDL+DML

פיתחו את ה SSMS -> גשו למסד הנתונים של הנסיונות שלכם (אצלי יש מסד נתונים מיוחד למשחקים בשם QQ) -> ייצרו טבלה זמנית והכניסו אליה נתונים.

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

use QQ
GO
 
CREATE TABLE #MyTable(ID INT IDENTITY,FirstName VARCHAR(10),LastName VARCHAR(10))
GO
 
INSERT INTO #MyTable(FirstName,LastName)
    SELECT 'A','1' UNION ALL
    SELECT 'A','1' UNION ALL
    SELECT 'A','2' UNION ALL
    SELECT 'A','2' UNION ALL
    SELECT 'A','3' UNION ALL
    SELECT 'A','4' UNION ALL
    SELECT 'A','5' UNION ALL
    SELECT 'A','6' UNION ALL
    SELECT 'B','1' UNION ALL
    SELECT 'B','2' UNION ALL
    SELECT 'C','1' UNION ALL
    SELECT 'C','2' UNION ALL
    SELECT 'C','2' UNION ALL
    SELECT 'C','3' UNION ALL
    SELECT 'C','4'  UNION ALL
    SELECT 'C','5'
GO

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

-- 1. using group by
with MyCTE (FirstName,LastName,MyCount)
as (
    select FirstName,LastName, count(*)
    from #MyTable
    group by FirstName,LastName
)
select #MyTable.* from MyCTE
left JOIN #MyTable on #MyTable.FirstName = MyCTE.FirstName and #MyTable.LastName = MyCTE.LastName
where MyCTE.MyCount > 1
--ORDER BY MyCTE.LastName,MyCTE.FirstName
GO
 
-- 2. Using Self JOIN
SELECT t1.ID,t1.FirstName,t1.LastName
FROM   #MyTable t1
INNER JOIN #MyTable t2 ON t1.FirstName = t2.FirstName AND t1.LastName = t2.LastName AND t1.ID != t2.ID
--ORDER BY t1.LastName,t1.FirstName
GO
 
-- 3.1 using Window Function (count) & CTE
with MyCTE
as (
    select ID, FirstName,LastName, count(*) over (partition by FirstName,LastName) as NumberCount
    from #MyTable
)
select * from MyCTE
where NumberCount > 1
--ORDER BY LastName,FirstName
GO
 
-- 3.2 Using Window Function (count) & sub query
select * from (
    select ID, FirstName,LastName, count(*) over (partition by FirstName,LastName) as NumberCount
    from #MyTable
) Tbl
where NumberCount > 1
--ORDER BY LastName,FirstName
GO
 
-- 4. using row_number
-- ** we dont get the first row, but only the duplicates ones
select * from
(
    select
        FirstName,LastName, ROW_NUMBER() over (partition by FirstName,LastName order by FirstName,LastName) as NumberCount
    from #MyTable
) Tbl
where NumberCount > 1
--ORDER BY LastName,FirstName
GO

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

 PercentageQuery Type - no index + no order by 
25%
using group by 
13%
Self JOIN 
21%
using Window Function (count) & CTE 
21%
Using Window Function (count) & sub query 
20%
using row_number 
  

הורידו את הסימון של הערה בשורות של ORDER BY בשאילתות ונבדוק מה התוצאה במצב ללא אינדקסים עם סידור התוצאות:

 PercentageQuery Type - no index + With order by
17%
using group by 
19%
Self JOIN 
25%
using Window Function (count) & CTE 
25%
Using Window Function (count) & sub query 
14%
using row_number 
  

ניתן לראות שהשאילתה האחרונה שהיתה קודם גרועה מאוד קפצה למיקום של השאילתה המיטבית. הוספת סידור התוצאות שינתה לנו את התוצאות בצורה קיצונית. דיי ברור שהשיטה האחרונה הפכה ליותר יעילה מפני שבשימוש בפונקציה row_number היה לנו למעשה פעולה של SORT בכל מקרה. למעשה לא נוספה לנו בשאילתה זו שום פעולה לתוכנית ההרצה בעוד שלשאילתות האחרונות נוספה לנו פעולה כבדה של מיון הרשומות.

הגיע הזמן להוסיף את האינדקסים שלנו לטבלה. הריצו את השאילתה הבא:

-- INDEXS:
-- t1.LastName,t1.FirstName: for the order by
CREATE index MYQQ_Index on #MyTable (FirstName,LastName)
GO
 
-- Create Clustered Index
CREATE CLUSTERED INDEX MYQQ_CLUSTERED_Index ON #MyTable(ID ASC) ON [PRIMARY]
GO

נריץ את השאילתות שלנו שוב במצב של עם אינדקסים ועם סידור ונבדוק את התוצאות:

 PercentageQuery Type - With index + with order by
25%
using group by 
25%
Self JOIN 
18%
using Window Function (count) & CTE 
18%
Using Window Function (count) & sub query 
18%
using row_number 
  

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

נבטל את הסידור של התוצאות המתקבלות (החזירו את הסימון של הערה בחלק של ה ORDER בשאילתות) והריצו שוב את השאילתות שלנו:

 PercentageQuery Type - With index + no order by
25%
using group by 
34%
Self JOIN 
14%
using Window Function (count) & CTE 
14%
Using Window Function (count) & sub query 
12%
using row_number 
  

מי שמציץ בתוצאות יכול לא להאמין לשינויים שיש לנו. ההבדל ענק בין ההרצה הראשונה בלי אינדקסים ובלי סידור וההרצה האחרונה עם אינדקס ובלי סידור התוצאות. שאילתות שהיו מיטביות קודם הפכו לכבדות בצורה קיצונית. השאילתה הכי יעילה בהרצה הראשונה הפכה לשאילתה הכי גרועה בהרצה הנוכחית (שאילתה 2) מ 13% היא קפצה ל 34%.

ראו את השאילתה האחרונה במצב הנוכחי. מדוע עם אינדקס דווקא האפשרות האחרונה כל כך יעילה גם כאשר בוחרים לא לבצע סידור
הרי ללא אינדקס זו האפשרות הכי גרועה?
ההסבר פשוט: הוספת האנדקס תרמה כל כך הרבה בגלל ההתאמה לחלק של partition by בחלון התוצאות של פונקציית ה ROW_NUMBER שלנו. בכל חלון אנחנו מקבלים תוצאה שרק בתוכה אנחנו צריכים לבצע את הסידור אבל הסידור המרכזי שנעשה קודם היה הסידור בשביל ה partition וחלק זה כבר אינו נחוץ. תוכנית ההרצה שקיבלנו היא מיטבית בהבדל ניכר.

מסקנות:

ניתן לקבוע לפי התוצאות שבכל המקרים שהצגנו אנחנו יכולים לעבוד עם ROWNUMBER פרט למקרה בו אנחנו גם לא צריכים סידור וגם אין לנו אינדקס מתאים למיספור no index + no order by. במקרה זה האפשרות המיטבית בצורה דיי קיצונית היא שימוש באפשרות של Self JOIN.

להצגת תוכניות ההרצה המלאות ניתן להעזר בקישורים הבאים:

א. ללא אינדקסים וללא סידור

ב. ללא אינדקסים ועם סידור

ג. עם אינדקסים ועם סידור

ד. עם אינדקסים וללא סידור

.

.

.

.

.

בדיקות נוספות על טבלה נקייה ללא אינדקסים:

--מטעמי אבטחה אני תמיד עובד תחת מסד נתונים ריק שנועד לצרכים אלו
use qq
go
 
-- נגדיר משתנה טבלה זמנית לצורך הדוגמה
declare @Tbl as table ([ID] int IDENTITY(1,1), [Name] nvarchar(100))
   
-- נכניס לטבלה נתונים עם כפילויות
insert into @Tbl ([Name]) values('a')
insert into @Tbl ([Name]) values('s')
insert into @Tbl ([Name]) values('d')
insert into @Tbl ([Name]) values('c')
insert into @Tbl ([Name]) values('a')
insert into @Tbl ([Name]) values('a')
insert into @Tbl ([Name]) values('c')
------------------------------------------------------
-- נבדוק את כל הנתונים שיש לנו
select * from @Tbl
------------------------------------------------------
-- אנו רוצים למצוא את כל הנתונים בהם יש שם שכבר מופיע ברשומה אחרת
-- הרעיון הוא מספור כל הרשומות לפי שדה השם בסידור לפי שדה השם
-- למעשה אין חשיבות לסידור מפני שכל מה שרוצים זה את הכפילות
select [Name],Row_number() over(partition by [Name] order by [Name]) as [RowNum]
from @Tbl
------------------------------------------------------
-- נבחר רק את הנתון של השמות שמצאנו שבהם יש כפילויות
select distinct [Name] from (
      select [Name],Row_number() over(partition by [Name] order by [Name]) as [RowNum]
      from @Tbl
) Tbl
where [RowNum] > 1
------------------------------------------------------
-- כמובן שבמקרה האחרון הפעולה כבר אינה מצדיקה את השימוש ב
-- ROW_NUMBER
-- אולי וניתן לעשות שימוש פשוט ב
-- group by
select [Name],COUNT(*) from @Tbl group by [Name]
having COUNT(*) > 1
 
-- או בצורה הבאה ללא
-- having
select [Name]
from (
      select [Name],COUNT(*) MyCount from @Tbl group by [Name]
) Tbl
where MyCount > 1
 
-- לשם המשך התצוגה נראה כיצד בשיטה הראשונה
-- נבחר את כל הנתונים שבהם יש שם זה כי הרי אלו הכפילויות שלנו
select ID from @Tbl
where [Name] in (
      select  distinct [Name] from (
      select [Name],Row_number() over(partition by [Name] order by [Name]) as [RowNum]
      from @Tbl
      ) Tbl
      where [RowNum] > 1
)