מסד מרובי נתונים

[24sharon]

מסד מרובי נתונים

יש לי טבלה שאמורה להכיל מליוני רשומות.
ומקושרת לטבלאות רבות ושונות

עם אפשרות של סינון מתקדם (כולל IN למינהם וכולל אפשרויות מיון)

אני מתלבטת מה הכי נכון בצפי לעבודה מהירה עם נתונים

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

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

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


תודה

 

[דוד גילי]

שקלת nosql ?

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

 

[24sharon]

אין לי אפשרות לשנות

אני לא בעמדה של ההחלטות במקרה הזה אני מקבלת את הנתונים "AS IS"
&nbsp
במסגרת הנתונים יש צורך לבצע את הטוב ביותר
&nbsp
&nbsp

 

[דוד גילי]

לגבי הדילמה, EF או SP,

שמעתי מרבים וטובים, ש EF , sql server והשילוב ביניהם היום הוא כל כך טוב, שההבדל בביצועים לא יהיה כל כך גדול כמו שהיה פעם. פעם היה נכון לומר ש SP מהיר משמעותית מכל דבר אחר. היום, כך הטענה, שאילתת SQL פשוטה (שזה מה שבסוף EF שולח), יכולה להיות מהירה בסדרי גודל של SP, וכל הסיפורים על "קומפילציה" של ה SP שגורמת למהירות רבה יותר, כבר לא נכונים.
אגב, מה יותר פשוט מבדיקת ביצועים? תוקעים 10 מיליון רשומות דמה, כותבים SP שמסננת נתונים ידועים מראש, ואת אותה שאילתה כותבים ב linq ומודדים זמנים.
במקומות שבהם אני קובע, וכל עוד זה לא טילים שעפים בחלל, אני מעדיף לעבוד עם EF, לטובת קריאות ונכונות הקוד, קלות בדיבאג, וכן הלאה.
אל תשכחי לעדכן אותנו, שנדע...
&nbsp

 

[arielRuuubi]

+1

 

[יוסף צור]

ההבדל גדול מאד

לא בגלל הקומפליציה אלא בגלל סיבות אחרות.
EF הוא לא רק מחולל SQL, אלא יוצר מערכת שלמה מעל הקוד שלך.

ראשית, באתחול. לEF לוקח כמה שניות[!] כדי לבנות את המודל. אם במודל יש לך מחלקה Product, אז EF יוצר מחלקה בשם Product_AF0AF1A012A שיורשת מהמחלקה המקורית, ועוטפת את כל הפרופרטיז. כל זה לוקח הרבה זמן.

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

 

[יוסף צור]

לדעתי

אם היישום גם משנה את הנתונים, מוסיף או מוחק רשומות, בוודאי שעדיף EF ולא לכתוב קוד ידני.
&nbsp
אבל אם היישום הוא מערכת דוחות ורק קורא נתונים, אין צורך בEF, ואפשר להשתמש במיקרו ORM כמו dapper.
כך אני עשיתי, עד שנמאס לי ועברתי לEF. אני מעדיף לכתוב הרבה LINQ בתוך היישום מלכתוב SQL מחוץ ליישום.

 

[ziv1f]

אין הרבה יתרון לפרוצדורות בהיבט של יעילות

השיפור העיקרי כאשר כותבים פרוצדורה הוא בחסכון ביצירת ה-Prepared Statement כלומר בקימפול של ה-SQL ויצירת ה-Query Plan

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

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

עכשיו לגבי השאלה שלך: סינון כמויות גדולות מאוד של נתונים זה עסק מורכב, וצריך לבדוק כמה פרמטרים בנוגע לטבלה הזו כאשר ממטבים את הביצועים בנוגע אליה, כמו למשל כמות הכתיבות בדקה מול כמות הקריאות (שאילתות) ממנה. טבלה שקוראים ממנה הרבה יותר ממה שכותבים אליה למשל, אפשר להוסיף הרבה אינדקסים על השדות של הטבלה, וזה יאיץ מאד את השאילתה. בנוסף אם יש עמודות שאת יודעת שהשימוש באינדקסים שלהן ישפר את הביצועים, יש דבר שנקרא Optimizer Hint שאפשר להוסיף לשאילתה, שזה למעשה הוראות למנגנון שמייצר את ה-QueryPlan למשל להשתמש באינדקס או אינדקסים שמתוך האלוגריתם הפנימי שלו הוא לא היה בוחר להשתמש בהם. לרוב האלגוריתמים האלה חכמים מאד, אבל בשאילתות מורכבות הם לפעמים מפספסים דברים יחסית טריוויאלים, ולפעמים יש לנו פשוט מידע בנוגע לנתונים שאנחנו מעוניינים לנצל ואז זה מאד פשוט לשימוש ומאד משפר את הביצועים. בנוסף לפעמים דווקא השימוש באינדקסים מפחית ביצועים, ואז אפשר לבקש full-table-scan במקום להשתמש באינדקס.

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

בברכה,
זיו

 

[Royi Namir]

כמה דברים

אם ה POV שלך זה שליפה מהירה אז יש כמה דברים.

( לגבי EF אם להיות פדנטים, היא תמיד תיהיה יותר איטית מ LOW LEVEL כמו DAPPER, אין מה לעשות . יש כמה שכבות בדרך שמקמפלות את ה LINQ ל EXPRESSION TREE ושולחות את זה ל SQL , דרך אגב גם ב WIRE יעברו הרבה יותר נתונים - אבל זה זניח ובואי נשים את זה בצד , שלא נדבר שיש המון פונקציות שפשוט אי אפשר לעשות(באופן טבעי) ב LINQ וב SQL)http://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq כן אפשר - והרבה יותר מהר , PIVOT , WINDOW FUNCTIONS , LEAD LAG , )
=================
ראיתי שאת אומרת IN.
אחד מה BAD RACTICES זה להשתמש ב IN.
פשוט לא עושים את זה.
למה ? כי ה SUB QUERY שבתוך ה IN מייצרת את כל הרשומות קודם ואחרי זה בודקת.
אפציה אחרת זה JOIN
=============
אינדוקס טוב של הטבלה לפי CLUSTERED INDEX שעל פיו נעשה החיתוך - יעזור המון ( ואני אומר המון)
================
אם אין לך בעיה לקרוא DIRTY DATA , אפשר להשתמש ב WITH NO LOCK ב שאילתות , זה גם יאיץ קריאות
================
PARTITIONS
אפ יש לך מליוני על מליוני רשומות אפשר לחלק את זה לטלבאות
מ 1 עד 500K, טבלה A
..500K עד 1000K... טבלה B

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

 

[יוסף צור]

אין הבדל בין IN לJOIN

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

 

[יוסף צור]

תיקון

royi צודק במקרה הזה:

SELECT * FROM Transactions 
WHERE productId IN 
	(SELECT NotPrimaryKeyColumn FROM Products 
	WHERE SupplierId = 2)

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

SELECT * FROM Transactions 
WHERE productId IN 
	(SELECT ProductId FROM Products 
	WHERE SupplierId = 2)

במקרה הזה IN וגם WHERE עושים את אותה פעולה, ואז IN עדיף כי הוא נקי וברור מWHERE.
כאמור, זה המקרה השכיח והרגיל.

 

[24sharon]

אני מאוד מעריכה את התגובות

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

במקביל שאלתי גם שם
http://stackoverflow.com/questions/...ork-vs-sql-stored-procedure/35540231#35540231

אז אם נתעלם גם מהאנגלית שלי וגם מהציניות בתגובה.

הייתי רוצה להתייחס לנושא הדפדוף, ההמלצה שלו זה לא לבצע דפדפוף (אם הבנתי נכון)
אני רגילה לבצע את זה באמצעות TAKE וSKIP של EF
אשמח לשמוע מה דעתכם בנושא הזה

 

[דוד גילי]

דעתי

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

 

[24sharon]

כן גם אני התפלאתי מאוד מנושא הדפדפוף

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

 

[Royi Namir]

להיפך , כשחושבים על זה זה לא אמור להפתיע

SQL צריך למספר את הרשומות בשביל לדעת באיזה PAGE את נמצאת.
הוא לא יודע את זה.
והוא עושה את זה על ידי ROWNUMBER OVER....
&nbsp
לאמת האמיתה - תהיתי בדבר , ואני כמובן לא יכול להישאר בחוסר ידע וביטחון בדבר - ולכן פניתי לחבריי ה DBA ב SO .
&nbsp
ושאלתי אותו על זה מהתחלה :
&nbsp
תקראו את התשובה שלו
http://chat.stackexchange.com/transcript/message/27796323#27796323
&nbsp
מכל מלמדי השכלתי ושוב תודה על רענון הנושא.
&nbsp

 

[יוסף צור]

כמה הערות

1. Paging. העצה "אל תעשה זאת" היא עצה מוזרה. אם זה מה שצריך, זה מה שעושים. יש בעיית ביצועים? קנה שרת חדש וחזק.

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

// filter using join
var trans1 = (from t in context.Transactins
                join p in context.Products on t.ProductId equals p.Id
                join s in context.Suppliers on p.SupplierId equals s.Id
                where s.SupplierType == 1
                select t).ToArray();

Console.WriteLine($"query 1 returns {trans1.Length} rows.");

// filter using contains [=Exists]
var selectedSuppliers = from s in context.Suppliers
                        where s.SupplierType == 1
                        select s.Id;

var selectedProducts = from p in context.Products
                        where selectedSuppliers.Contains(p.SupplierId)
                        select p.Id;

var trans2 = (from t in context.Transactins
                where selectedProducts.Contains(t.ProductId)
                select t).ToArray();

Console.WriteLine($"query 2 returns {trans2.Length} rows.");

// filter using navigation properties, translated to join
var trans3 = (from t in context.Transactins
                where t.Product.Supplier.SupplierType == 1
                select t).ToArray();

Console.WriteLine($"query 3 returns {trans3.Length} rows.");

כל זה כאשר הjoin נעשה על המפתח הראשי [כמו tran.ProductId = product.ProductId], אז אין הבדל בין IN לJOIN, שניהם מתפרקים לsemi join.

אם רוצים לעשות IN בלי מפתח ראשי, זה עלול לגרום בעיית ביצועים ועושים זאת כדלהלן:

var items = (from p in context.SomeTable
                where SomeColumn = 2
                select p.SomeOtherColumn)
                .Distinct()
                .ToArray();

var trans2 = (from t in context.Transactins
                where items.Contains(t.SomeProperty)
                select t).ToArray();

זה דומה לרעיון הטבלה הזמנית שהציעו לך בSO, אבל הרבה יותר פשוט וקל.
שימי לב שJoin לא יעזור במקרה הזה.

3. כאשר הנתונים לקריאה בלבד, כדאי להוסיף AsNoTracking.

4. אם יש Joinים שחוזרים עליהם הרבה פעמים, כדאי לעשות View. במקום לעשות אלף פעמים join בין product וtransaction מגדירים בדטהבייס View ומוסיפים לEF אוסף <DbSet<ViewTran.

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

 

[24sharon]

בעיקרון הIN לא אמור להיות רשימה יותר מידי ארוכה

ולכן אני ניסיתי לבצע תת שאילתא של OR

זה הקוד שלי

       var predicate = PredicateBuilder.True<Summon>();
        if (!string.IsNullOrEmpty(param.id))
        {
            predicate = predicate.And(m => m.id== param.id);
        }
        if (!string.IsNullOrEmpty(param.subjects))
        {
            var subjectsPredicate = PredicateBuilder.False<Summon>();
            string[] subjects = param.subjects.Split(',');
            for (int i = 0; i < subjects.Length; i++)
            {
                subjectsPredicate = subjectsPredicate.Or(m => m.SubjectId ==subjects[i]);
            }
            predicate = predicate.And(subjectsPredicate);
        }

משום מה אם קיים ערך בנתון אני מקבלת שגיאת indexOutOfRangeException

מה הדרך ליצור תת שאילתא כזו?

 

[24sharon]

אוקיי נפתר

את מי שמעניין
הלולאה צריכה להיות כך

 foreach (string key in subjects)
                {
                    subjectsPredicate = subjectsPredicate.Or(m => m.SubjectId == key);
                }

ולא להתייחס לערך במערכת subjects

תודה

 

[יוסף צור]

למה לעבוד בכזו צורה?

למה אינך משתמשת בLinq אלא כותבת בעצמך את הExpressions?
הפחידו אותך יותר מדי מIN הנורא והאיום, שכאמור הוא טוב ומצוין ואין לו תחליף.

הנה דוגמא פשוטה:

var names = new string[] { "A", "B" ,"C"};
var products = context.Products
                .Where(p => names.Contains(p.Name))
                .ToArray();

הlinq הזה מתורגם לSQL הזה:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[SupplierId] AS [SupplierId]
    FROM [dbo].[Products] AS [Extent1]
    WHERE ([Extent1].[Name] IN (N'A', N'B', N'C')) AND ([Extent1].[Name] IS NOT NULL)

אוי ואבוי! געוואלד! יש לי IN בשאילתה!
נריץ את השאילתה הזו עם Execution plan, וזה מה שנקבל:

 

[יוסף צור]

אגב

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

if (productIdFilter.HasValue) {
    query = query.Where(x => x.ProductId == productIdFilter.Value);
}

if (priceFilter.HasValue) {
    query = query.Where(x => x.Price == priceFilter.Value);
}

אבל אם רוצים לעבוד עם פרוצדורות, צריך להעביר לפרוצדורה את כל הפרמטרים, ולפלטר עם כולם:

SELECT
	*
FROM Transactions
WHERE (@productId IS NULL OR ProductId = @productId)
AND (@price IS NULL OR price = @price)

זה גם קוד מסובך ומכוער, וזה גם בעיית ביצועים אדירה, בהפרש של אלפי אחוזים!
כי במקום לעשות SEEK, השרת עושה SCAN.