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
מרץ29

Written by: ronen ariely
29/03/2013 15:08 RssIcon

הקדמה

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

לשרתי SQL יש מנגנון לזיהוי המיקום הפיזי של כתובת הרשומה. בשרתי 2008 ומאוחר יותר נעשה שימוש בפרמטר %%physloc%% ובשרתי 2005 נעשה שימוש בפרמטר %%lockres%%. מדריך זה לא דן בפרמטרים אלו כי אם רק עושה בהם שימוש על מנת לבצע את המטרה שלנו. כדאי לציין בקצרה שפרמטר זה מפורמט ואם אנו רוצים להוציא ממנו נתונים כמו מספר הקובץ, מספר ה Page בהם נמצאת הרשומה בפועל נוכל לעשות שימוש בפונקציה המובנית fn_PhysLocFormatter.

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

DDL+DML

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


הפתרון הראשון שנציג יותר מוכר ולכן לא נדון בו לעומק מתבסס על שימוש בפונקציה ROW_NUMBER:

;WITH CTE AS
(
SELECT FirstName
    , LastName
    , ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName) AS Rnum
FROM #MyTable
)
DELETE FROM CTE WHERE Rnum <> 1

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

delete from #MyTable
where %%physloc%% > (
    select min(%%physloc%%)
    from #MyTable InTbl
    where InTbl.FirstName = #MyTable.FirstName and InTbl.LastName = #MyTable.LastName)
GO


** בדוגמה הנוכחית השימוש ב ROW_NUMBER יכול לתת תוצאה טובה יותר, בשל השימוש ביותר פעולות IO. הצורך ב SPOOL הניראה בתוכנית ההרצה ויצירת טבלה זמנית דינאמית עלולע לגרור את השרת לבצע הרבה יותר פעולות IO. תוכנית ההרצה לא תראה נקודה זו, ממלץ להגדיר statistics TIME וכן statistics IO למצב ON על מנת לחקור את הנושא יותר לעומק! לעומת זאת בדוגמה המופיעה בהמשך מתהפכות היוצרות, השימוש בפונהקציה ROW_NUMBER מחייב פעולה יקרה של SORT בעוד השמוש בפרמטר המיקם הפיסי של הרשומה נעשה בצורה ישירה.


נקודות חשובות - תוספת

* הבדיקות נעשו בשרת sql 2012

* בדוגמה המוצגת כאן ניתן לראות שכדי למצוא את הרשומות המיותרות שעשיתי שימוש בהשווה של "גדול מ" (<). ניתן למשל לבצע השווא שונה של Not In כפי שנעשה בקישור המצורף ואז בשאילתה הפנימית לעשות שינוי קטן תוך שימוש ב group by:

delete from #MyTable
where %%physloc%% not in
    (select Min(%%physloc%%) from #MyTable group by FirstName,LastName)
GO

שיטה זו מאוד לא יעילה והשוואה לשיטה שבחרתי מעל מראה בדוגמה שלנו שימוש ב 68% משאבים לעומת 32%. זה עוד כלום! בהרצה על דטה יותר רציני שנבנה עם לולאה ליצירת 100K רשומות כפי שהוצג בלוג שם ההבדלים היו 85% לעומת 15% כאשר השאילתה הנ"ל (הגרועה) דווקא התחילה לרוץ בצורה מקבילית כפי שניתן לראות בתוכנית ההרצה המצורפת

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

-------------------------------------------
-- http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server
-------------------------------------------

-- DDL
CREATE TABLE SeveralRows (
   Id         int        NOT NULL IDENTITY(1,1)PRIMARY KEY,
   InsertTime date       NOT NULL DEFAULT (GETDATE()),
   Category   varchar(2) NOT NULL
);
GO
 
-- DML
SET NOCOUNT ON
DECLARE @counter int;
BEGIN
   SET @counter = 0;
   WHILE @counter < 100000 BEGIN
      INSERT INTO SeveralRows (Category)
      VALUES (CONVERT(varchar, ROUND( RAND(), 1) * 10 ));
 
      SET @counter = @counter + 1;
   END;
END;
 
 
-------------------------------------------
-- Delete duplicates based on Category
-------------------------------------------
DELETE FROM  SeveralRows
WHERE
    SeveralRows.%%physloc%%
    NOT IN
    (
        SELECT MIN(b.%%physloc%%)
        FROM   SeveralRows b
        GROUP BY b.Category
    );
 
-------------------------------------------
DELETE FROM SeveralRows
WHERE
    %%physloc%%
    >
    (
    select min(%%physloc%%)
    from SeveralRows InTbl
    where InTbl.Category = SeveralRows.Category
    )
GO


   

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

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

קישורים

Physical location of a row in SQL Server
http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server