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

Question

שלום לכולם,

הייתי בראיון עבודה לא מזמן והתבקשתי ליצור טבלא בעלת (בין השאר) עמודה מסוג nvarchar ולהכניס לשם 1000 ערכים בעלי אורך ותוכן אקראי לחלוטין. ניסיתי להשתמש ב-RAND() וב- NCHAR(), אבל הבעיה הוא ש-RAND() נותן לי את אותו ערך 1000 פעמים, אלא עם כן אני מריץ אותו בלולאה ואני רוצה להימנע מלולאות. הבעיה עם NCHAR() שהוא מחזיר רק תו אחד ואני צריך אורך אקראי. יש למישהו רעיון?

Answer 1

היי,

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

לדוגמה:

select

top 1000 CAST(ABS (CHECKSUM (NEWID ()))% 10000 AS NVARCHAR(1000))

from

sys.objects a

cross

join sys.objects b

Answer 2

דרך אגב, שכחתי כמעט

בהצלחה עם הראיון איבן :-)

---

[Personal Site] [Blog] [Facebook]

Answer 3

היי תומר,

תודה רבה, אבל זה לא כל כך עובד. אני מקבל מספרים בעלי 4 ספרות בלבד אני לא מבין איך זה אמור לעבוד בדיוק. guid הוא בינארי, checksum מחזיר int, את המספר הזה אתה אופך למחרוזת עם cast... לא ברור בכלל.

חוץ מזה, אני צריך לקבל רשומות בעלי אורך אקראי, לא לקבוע את האורך מראש...

היי רונן,

תודה רבה, מאוד אשתדל! :)

Answer 4

הרעיון של השיטה הוא לייצר מס' רנדומליים, לכן כאשר אני מבצע פעולת % (שארית חלוקה) 10000 אני אקבל בצורה רנדומלית מספרים בתווך שבין 0-9999.

לכן ככל שתגדיל או תקטין את מס' החלוקה כך אורך המחרוזת הסופית ישתנה. את התוצאה המספרית תוכל לשרשר למחרוזת טקסט כלשהי וזה יתן לך אורך ותוכן אקראי.

אם לא ידוע מראש מהי אורך המחרוזת המקסימלית אז שיטה זאת לא תעזור לך.

Answer 5

איבן אתה רוצה לייצר רק מספרים או שרשרת תווים?

---

[Personal Site] [Blog] [Facebook]

Answer 6

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

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

בדוק את הבלוג

http://ariely.info/Blog/tabid/83/EntryId/121/Using-side-effecting-build-in-functions-inside-a-UDF-your-function.aspx

מצד שני שינוי קטן בקוד שהביא תומר יבצע את ההתאמה למה שאתה צריך. בדוק את הקוד הבא:

select top 1000 CAST(ABS (CHECKSUM (NEWID ()))AS float)
from _ArielyAccessoriesDB.dbo.Numbers

-- השימוש במודולו נועד רק בשביל להגביל את התוצאה אבל בדוגמה למעלה לא הגבלתי
-- כאן למשל אני מגביל את התוצאה לערכים מאפס ועד ל 99999
select top 1000 CAST(ABS (CHECKSUM (NEWID ()))% 1000000 AS float)
from _ArielyAccessoriesDB.dbo.Numbers

---

[Personal Site] [Blog] [Facebook]

Answer 7

הי איבן,

מה דעתך על זה?

SELECT TOP (1000)
	LEFT (CAST (NEWID () AS NVARCHAR(36)) , ABS (CHECKSUM (NEWID ())) % 36 + 1)
FROM
	master.dbo.spt_values;

בהצלחה!

-----------------------------
גיא גלנצר
יועץ ומדריך SQL Server
Madeira - SQL Server Services
http://www.madeira.co.il

Answer 8

הרעיון של השיטה הוא לייצר מס' רנדומליים, לכן כאשר אני מבצע פעולת % (שארית חלוקה) 10000 אני אקבל בצורה רנדומלית מספרים בתווך שבין 0-9999.

לכן ככל שתגדיל או תקטין את מס' החלוקה כך אורך המחרוזת הסופית ישתנה. את התוצאה המספרית תוכל לשרשר למחרוזת טקסט כלשהי וזה יתן לך אורך ותוכן אקראי.

אם לא ידוע מראש מהי אורך המחרוזת המקסימלית אז שיטה זאת לא תעזור לך.

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

רונן,

הרעיון שלך לשים את RAND() בתוך פונקציה הוא נחמד, אבל איך זה שונה מלולאה? אם אני לא טועה, אתה מפעיל את הפונקציה על 1000 שורות, כלומר מריץ אותה 1000 פעמים, נכון?

גיא,

הבעיה עם השאילתא שלך, שהמחרוזות שמתקבלות הן הקסדצימליות (0-9, A-F) ולא unicode. חוץ מזה היא אכן עונה על כל התנאים.

Answer 9

אני אתן לך דוגמה מקווה שתבין,

אם אתה רוצה רשומות באורך מקסימלי של 5 אז אתה מגדיר בשארית חלוקה (%) 100000 כך תוכל לקבל מס' בתווך 0-99999

ואז אם תקבל את הספרה 3 זה יהיה באורך 1 ואם תקבל 222 זה יהיה באורך 3, כמובן שהמקסימום יהיה באורך 5.

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

SELECT

SumString+RandNumber

לכן השתמשתי בפונקצייה CAST בשאילתה המקורית שלי.

Answer 10

תבדוק לבד או תעקוב אחרי הבלוג :-)

אם אתה מריץ את הפונקציה המובנית RAN אתה מקבל את אותו ערך תמיד, אבל אם אתה מריץ את הפונקציה שלך (שלי) של ה RANDOM אז אתה מקבל בכל רשומה בתוצאה מספר שונה רנדומלי. זה ההבדל!

עדיין אני לא מצליח להבין דרך אגב ולא ענית לי: האם אתה רוצה לקבל שרשרת רנדומלית של תווים (ז"א גם אותיות) או מספר רנדומלי? אם כל מה שאתה צריך זה מספר רנדומלי אז יש לך בשרשור כבר כמה פתרונות מבוססים NEWID או שימוש בפונקציה RANDOM שלך (פחות יעיל).

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

---

[Personal Site] [Blog] [Facebook]

Answer 11

שלום,

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

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

אם מצאת פתרון אחר, נשמח אם תשתף אותנו על מנת שאחרים אשר מגיעים לפורום יוכלו להעזר בו.

תודה,

צוות הפורומים.

---

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

Answer 12

היי איוון,

מצורפת שאילתה שמחזירה מחרוזת רנדומלית. השאילתה מחזירה שתי עמודות, הראשונה נקראת RandomString1 והיא מחזירה מחרוזת קצרה יחסית, העמודה השנייה נקראת RandomString2 והיא ארוכה יותר. אני מקווה שזה יעזור. בכל מקרה בהצלחה בראיון!

חקיקת יוסי 

;WITH cte_1
AS
(
SELECT 
   ROW_NUMBER() OVER (ORDER BY NEWID() ASC) AS RN,
   t.name
FROM
   sys.tables AS t
CROSS JOIN
   sys.tables AS tt

),
 cte_2
AS
(
SELECT 
   ROW_NUMBER() OVER (ORDER BY NEWID() ASC) AS RN,
   t.name
FROM
   sys.columns AS t
CROSS JOIN
   sys.columns AS tt

)

SELECT 
    cte_1.name + cte_2.name AS RandomString1,
    REPLICATE(cte_1.name + cte_2.name,CASE WHEN ABS (CHECKSUM (NEWID ())) % 4 = 0 THEN 1 ELSE ABS (CHECKSUM (NEWID ())) % 4 + 1 END) AS RandomString2
FROM 
    cte_1 
INNER JOIN
    cte_2 
ON 
    cte_1.RN = cte_2.RN

Answer 13

יוסי אני מאוד ממליץ לך לא לעבוד בשיטה זו אם אתה מחפש רנדומליות מלאה

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

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

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

---

[Personal Site] [Blog] [Facebook]

Answer 14

איוון,

קיבלת כאן כל מיני הצעות לפתרון, ולפי מה שהבנתי ממך, אף אחת לא ממש עונה על הצרכים שלך.

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

תודה!

-----------------------------
גיא גלנצר
יועץ ומדריך SQL Server
Madeira - SQL Server Services
http://www.madeira.co.il

Answer 15

שלום לכולם,

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

גיא,

בהחלט, אך תשובה שנותנה פה לא ממש עונה על השאלה.

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

SELECT top 1000
       LEFT (
             NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
          + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
             
          , 1 + CAST(29 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT)
          ) AS Str
from sys.columns a CROSS JOIN sys.columns b

Answer 16

הי,

הנה פתרון קצת יותר אלגנטי...

SELECT TOP (1000)
	String
FROM
	sys.columns AS T1
CROSS JOIN
	sys.columns AS T2
CROSS APPLY
	(
		SELECT TOP (ABS (CHECKSUM (NEWID () , T1.object_id)) % 30 + 1)
			NCHAR (ABS (CHECKSUM (NEWID () , T1.object_id)) % 1000)
		FROM
			sys.columns
		FOR XML
			PATH (N'')
	)
	AS
		RandomCharacters (String)
ORDER BY
	NEWID () ASC;
GO

-----------------------------
גיא גלנצר
יועץ ומדריך SQL Server
Madeira - SQL Server Services
http://www.madeira.co.il

Answer 17

היי גיא,

הפיתרון שלך הוא בהחלט יותר אלגנטי, רואים שלמדת הרבה אתמול (כמו כולנו) :)

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

תודה רבה!

Answer 18

איבן,

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

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

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

בוא נראה מה הבעיה בפתרון שלכם:

1. איטי השוואה על 10 אלף רשומות הפונקציה שלי מחזירה תשובה תוך אפס שניות והפונקציה שלכם תוך 5 שניות

* לא מדוייק כי לא בדקתי זמן אלא רק מה שה SSMS מראה למטה

* הוספתי כמה שורות של יצירת תווים כדי שאוכל להחזיר שרשרת על עד 100 תווים

2. השיטה הזו איומה מבחינת נוחות וגמישות

מה אם מחר תרצה לבנות שרשרת של 4000 תווים כדי למלא טבלה של מליון רשומות

4000 תווים יחייב אתכם לשנות את הפונקציה ולהוסיף קוד ענק והכוונה ל

ע נ ק !!!

הפונקציה שלי גמישה לחלוטין ויכולה להחזיר כל אורך של שרשרת שרוצים

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

כן... הפונקציה שלי היא ב CLR כמובן :-)

הפונקציה תעלה לאוויר בבלוג בהיזדמנות ראשונה...

---

[Personal Site] [Blog] [Facebook]

Answer 19

צריך לערוך הרצאה של CLR ולהפנים את הקיצוניות בהבדלים והשיפור שניתן לקבל (לפעמים)!

אנשים משקיעים ימים ושבועות כדי לשפר 30% מהירות שאילתה בזמן שבמעבר ל CLR ניתן לשפר לפעמים בעשרות אלפי אחוזים.

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

לפעמים מדובר על שורה אחת של קוד או אפילו 2 מילים של קוד ב CLR כמו במקרה של SPLIT שהיא פשוט פונקציה מוכנה ב CLR (אדם מקרניק פיתח פונקציה יותר טובה ל SQL אבל את ההבדלים ניתן לראות גם בפונקציה המובנית).

---

[Personal Site] [Blog] [Facebook]

Answer 20

היי רונן,

1) מצטער שפספתי את השאלה שלך, אבל כן עניתי עליה בעקיפין, כאשר פסלתי את הפיתרון הראשון שגיא הציע. :)

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

Answer 21

הוא פתרון מעולה

כתבתי את זה בסיום בשביל המתח :-) ובשביל להדגיש את הבעיות בו קודם

אבל הוא לא פתרון לשרת חי אלא רק לשאלת מבחן. לשרת חי רוצים מיטוב+גמישות

הפונקציה כבר באוויר מאתמול, שמתי שרשור נפרד וגם הודעה בפייסבוק
עד לרגע זה כניראה שאף אחד לא מצא שזה מועיל אפילו או שאף אחד לא קרא את ההודעה :-(
חפש שרשור עם הכותרת SQL Random String using CLR

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

---

[Personal Site] [Blog] [Facebook]

Answer 22

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

מקווה שזה יועיל

http://social.technet.microsoft.com/wiki/contents/articles/21196.random-string.aspx

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

---

[Personal Site] [Blog] [Facebook]

Answer 23

רונן,

שיחקתי קצת עם הפונקציה שלך וקיבלתי בדיוק את אותו ה-execution plan (שזה הגיוני, היות והפונקציה שלך מחזרה שורה אחת) :

איפה אני טועה?

Answer 24

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

אם אני מכין 2 פונקציות של CLR שצריכות רק להחזיר את המספר 1

פונקציה A: שורה אחת return 1

פונקציה B: מבצעת לולאות על כל הטבלאות בשרת, מבצעת חישוב של 1+1+1...+1 ועוד כמה פעולות מתמטיות כאלה, בונה מערך של אלף נתונים באורך 10K תווים ועושה כמה לולאות נחמדות,  ובסוף מתעלמת מכל החישוב ופשוט מחזירה return1

האם תוכניות ההרצה יהיו שונות?
האם מהירות ההרצה (והמשאבים) תהיה זהה?

---

[Personal Site] [Blog] [Facebook]

Answer 25

רונן,

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

Answer 26

"לא משקפת את המציאות" -> "לא משקפת את המציאות במלואה" :-)

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

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

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

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

קח את השאילתה שלך. ובנה 2 שאילתות. באחת תשאיר רק 2 שורות קוד:

 + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))

ובשאילתה השנייה תשאיר 100 שורות קוד.

בדוק את תוכנית ההרצה הגרפית :-)
רמז: למרות שברור לך ההבדל הענק הרי שהתוכנית הגרפית זהה
אתה רוצה את ההסבר המלא יותר או שזה מובן באופן כללי מההסבר כאן?

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

---

[Personal Site] [Blog] [Facebook]

Answer 27

הבנתי אותך, אתה צודק. השוואתי את שתי הפיתרונות עבור 1000K שורות וקיבלתי אותה חלוקה באחוזים בתוכניות ההרצה כמו קודם, אומנם כשאני מריץ אותם בנפרד, אני רואה שהפיתרון שלך הוא פי 2 יותר מהיר.

תודה רבה!

Answer 28

תודה על התודה :-)

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

הערה חשובה: במקרה שלנו בגלל שמחזירים הרבה רשומות הרי שיש משמעות לכמות זכרון+מהירות הכתיבה לדיסק. צוואר הבקבוק אצלי במחשב במשרד בכלל לא היה הקוד של ה CLR. הקוד המתין לשרת ה SQL לכתיבה לדיסק שהיתה איטית ולכן בדקתי על שרת ושם קיבלתי את ההבדלים האמיתיים יותר (עדיין תמיד בכמות גדולה המגבלה היתה מהירות הכתיבה לדיסק כי השרת כותב ל tempdb).

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

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

---

[Personal Site] [Blog] [Facebook]