דיפדוף יעיל
לעיתים אנו רוצים להציג למשתמשים מידע רב מבסיס הנתונים, ולא רוצים להציג את כולו בדף אחד. אז נכון שקיימים הפקדים של הדוט נט שחלקם מכילים כבר אופציה לדיפדוף, אבל זה בעצם דיפדוף "מדומה", הם לא באמת מושכים את המידע שאנחנו צריכים מהדטה בייס, הם מושכים את כל הרשומות, ומציגים לנו את מה שאנחנו צריכים, לכן, אם יש לי מיליון רשומות ואני רוצה להציג 50 כל פעם, אזי כל פעם שאני מדפדף דרך הקונטרול, הוא מושך את כל המיליון רשומות מהדטה בייס ופשוט מציג את ה 50 הבאות. אז השיטה הזו טובה כשרוצים בזריזות לבנות איזה דף עם כמה נתונים, אבל לשרת אמיתי שמשרת עשרות אלפי לקוחות צריך למצוא שיטה טובה יותר.
הכתבה תתרכז בצד של השאילתות לדטה בייס ולא בצד הלקוח. בפעם הבאה נדבר על ישום אג`קסי של הדפדוף ואז גם ניתן יהיה להוריד את הפרוייקט.
השיטה הקלה, אם נתמזל מזלנו ואנחנו לא צריכים אפשרות למיין, או מאפשרים מיון רק לפי עמודה שהיא ייחודית כמו אינדקס, אז פשוט נבנה את הדיפדוף כך שימשוך כל פעם את מספר הרשומות שאנחנו רוצים עם תנאי שמספר האינדקס יהיה גדול (או קטן במקרה של מיון מהגדול לקטן) ממספר כלשהו - האינדקס האחרון מהדף הקודם ( שאותו נשמור בדף או בסשן ). לדוגמא:
ניצור טבלה בשם t1 עם 3 עמודות:
מספר השורות שאנחנו רוצים להציג בדף
const int rowsPerPage=2;
משתנה שמכיל את מספר המזהה של השורה האחרונה שמשכנו (בפעם הראשונה הוא יהיה 0 כמובן):
int lastid=0;
השאילתה :
sql:Sel ect top [rowsPerPage] from t1 where id>[lastid] order by id
MySql : Sel ect * from t1 where id>[lastid] order by id limit [rowsPerPage]
כעת נשמור לנו את המספר המזהה של הרשומה האחרונה שמשכנו -3 את המספר כאמור ניתן לשמור או בסשן (לא מומלץ) או בדף עצמו - למשל בלחצן שמעביר לדף הבא - להוסיף לו את הפרמטר הזה.
כשהלקוח ילחץ על דיפדוף לדף הבא או הקודם, נקבל בשרת את הערך הזה ולפיו נדע איזה רשומות למשוך עכשיו. במקרה שלנו נקבל את המספר 3 לשרת והשאילתה תהיה:
Sql :Sel ect top 2 * from t1 where id>3 order by id
MySql: Sel ect * from t1 where id>3 order by id limit 2
וכך הלאה לשאר הדפים.
החסרונות ברורים:
לא ניתן למיין לפי עמודות אחרות שכן אנחנו חייבים עמודה עם מזהה יחודי
לא ניתן לקפוץ לעמוד מסויים, שכן אנחנו יודעים את האינדקס של העמוד הבא והקודם בלבד
כדי לנסות לפתור את 2 המגבלות האלה, ניתן לשכלל קצת את הפיתרון שלנו כפי שמופיע באתר של מיקרוסופט
בשיטה זו נוכל גם למיין לפי עמודות אחרות וגם נוכל לקפוץ לכל עמוד שנרצה:
נגדיר משתנה שישמור את מספר הדף:
int pageNum=1;
Sql : select top [rowsPerPage] * from (select top [rowsPerPage*pageNum] * from t1 order by name desc) as t2 order by name
Mysql : select * from (select * from t1 order by name desc limit [rowsPerPage*pageNum]) as t2 order by name limit [rowsPerPage]
דבר נוסף שחסכנו זה את הצורך לשמור את האינדקס של העמודה האחרונה, אנחנו ישר בוחרים לפי מספר הדף שהלקוח לחץ עליו. נניח שהוא לחץ על דף מספר 3, ואנו כאמור רוצים להציג 2 שורות בכל דף, אזי השאילתה תהיה :
MySql : sel ect * from (SEL ECT * FROM t1 order by name desc limit 6) as t2 order by name limit 2
Sql :sel ect top 2* from( sel ect top 6 * from t1 order by name desc) as t2 order by name
אולם גם זה פיתרון חלקי ( מיקרוסופט כנראה לא שמה לב...) , כי עבור עמודות עם ערכים זהים, כמו עמודת "מין", תתקבלנה תוצאות מוזרות :
MySql : sel ect * from (SEL ECT * FROM t1 order by gender desc limit 6) as t2 order by gender limit 2
Sql : sel ect top 2 * from (sel ect top 6 from t1 order by gender desc) as t2 order by gender
עדיין אנחנו לא יכולים למיין לפי עמודת "מין"...
אז מה כן עושים? טוב, אז אם יש לנו Sql Server 2005 או MySql הפיתרון יעיל וקל. לא משנה לפי איזו עמודה אני רוצה למיין:
ב sql server 2005 נכתוב
select * ,rank from (select *, row_number() over(order by gender desc) as rank from t1) as t2 where rank between [(pageNum-1)*rowsPerPage+1] and [(pageNum-1)*rowsPerPage+rowsPerPage]
וב MySql הרבה יותר פשוט, פשוט נכתוב
select * from clients limit [(pageNum-1)*rowsPerPage],[rowsPerPage]
והתוצאה כצפוי:
MySql : sel ect * from t1 order by gender limit 2,2
Sql : sel ect *,rank from
(sel ect *,row_number() over(order by gender desc) as rank from t1 ) as t2
where rank between 7 and 8
אבל מה עושים אם אני עובד עם שרת אחר?
אז הפיתרון קצת יותר איטי ויותר מסובך, אבל עדיין יותר יעיל מלמשוך את כל הרשומות אלי לדף...
כל פעם אני רוצה לבצע שאילתה, אני אצור טבלה זמנית, עם עמודה אחת של מיספור אוטומטי ועמודה נוספת שתשמור את מספר האינדקס מהטבלה הראשית.
Create table temp1 (tid int not null primary key auto_increment/identity,foreign_id int)
* אם אני לא סוגר את החיבור לדטה בייס במהלך הפונקציה אז ניתן ליצור את הטבלה בתור טבלה זמנית ואז היא תימחק אוטומטית בעת סגירת החיבור- יותר מהיר ויותר נקי....
כעת נעביר לטבלה הזאת את מספרי האינדקס מהטבלה המקורית, כשהם כבר ממויינים לפי העמודה שאני רוצה:
Insert into temp1 (foreign_id) select id from t1 order by gender
וכעת אני יכול לבחור את מספרי השורות שאני רוצה:
Select * from t1, temp1 where t1.id=temp1.foreign_id and temp1.tid between [(pageNumber-1)*rowsPerPage+1] and [(pageNumber-1)* rowsPerPage+rowsPerPage]
והתוצאה כצפוי:
sel ect t1.* from temp1,t1 where t1.id=temp1.foreign_id and temp1.tid between ((2-1)*2+1) and ((2-1)*2+2) order by gender
רק לא לשכוח בסוף למחוק את הטבלה אם לא השתמשנו בטבלה זמנית...
* יש דרך לשיפור המהירות של השיטה האחרונה באתר 4guysfromrolla* ניתן להשתמש ב stored procedures ליעול המהירות