חלוקה לעמודים מתוך ה-SQL Server

WebMaster Team ‏ • 16 בספטמבר, 2002

הרבה פעמים אנו נתקלים בצורך לשלוף רשומות מבסיס הנתונים, תוצאות חיפוש לדוגמא, ולהציגן ללקוח מחולקות לעמודים.
כדי לעשות זאת, רובינו משתמשים בחלוקה לעמודים ברמת ה-ASP , כלומר – שליפת הרשומות, מעבר לעמוד הרצוי והצגת הנתונים.
הבעייה בשיטה זו היא שאנו שולפים בעצם כל פעם מחדש את כל תוצאות השאילתה, אך מציגים רק חלק מהן.
הפתרון האידיאלי יהיה לבצע את החלוקה לעמודים ברמת ה-SQL Server, כבר בתוך אותה Stored Procedure המבצעת את החיפוש, וממנה להחזיר רק את הרשומות המתאימות לדף אותו ביקש הלקוח לראות.

SET ROWCOUNT

פקודה זו מגדירה למנוע ה-SQL להחזיר רק מספר רושמות ספציפי.

הפקודה מבצעת בדיוק אותה פעולה שמבצע SELECT TOP, בהבדל אחד:
עם
SET ROWCOUNT
, מנוע ה-SQL מפסיק לפעול ברגע שהגיע למספר הרשומות הרצוי ומחזיר את הרשומות ללקוח, בעוד ש-TOP הינו חלק מהשאילתה עצמה.

אופן השימוש בפקודה:

1
2
3
4

SET ROWCOUNT 10
SELECT * FROM tbl

בלוק זה יחזיר לנו את 10 הרשומות הראשונות בטבלה. כמובן, ניתן להוסיף פסוקית ORDER BY כדי לשלוט בשרומות המוחזרות.

יצירת ה-Stored Procedure

SP זו תרוץ על בסיס הנתונים pubs, ותחפש את כל המחברים ששם משפחתם מכיל את המחרוזת שהוזנה ע"ע המשתמש. כמובן שניתן להתאים אותה לכל בסיס נתונים שהוא.

ראשית נגדיר אותה ואת הפרמטרים שתקבל:

1
2
3
4
5
6
7

CREATE PROCEDURE sp_SearchResults
@PAGESIZE INT,
@CURRENT_PAGE INT,
@searchTerm varchar(50)

@PAGESIZE – מספר הרשומות שאנו רוצים להציג בדף
@CURRENT_PAGE – מספר הדף אותו ביקש הלקוח לראות
@searchTerm – השם לחיפוש

נשתמש ב- SET ROWCOUNT כדי להגדיר את מספר הרשומות שיוחזרו מה-SP.

1
2

SET ROWCOUNT @PAGESIZE

השאילתה

תחילת השאילתה היא פשוטה ומוכרת – בחירת נתוני כל המחברים ששם מפחתם מכיל את המחרוזת לחיפוש:

1
2
3
4

SELECT au_id, au_lname, au_fname, phone
FROM authors A
WHERE (au_fname LIKE '%' + @searchTerm + '%') AND

עכשיו נוסיף לפסוקית ה-WHERE תנאי נוסף.

1
2
3
4
5
6
7
8

AND
      ((SELECT COUNT(*)
       FROM authors A2
       WHERE A2.au_lname <= A.au_lname AND au_fname LIKE '%' + @searchTerm + '%')
     > (@PAGESIZE * @CURRENT_PAGE) – @PAGESIZE)

השאילתה הזו:

1
2
3
4
5

(SELECT COUNT(*)
FROM authors A2
WHERE A2.au_lname <= A.au_lname AND au_fname LIKE '%' + @searchTerm + '%')

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

את תוצאת השאילתה הזו, נשווה לנוסחה:

1
2

(@PAGESIZE * @CURRENT_PAGE) – @PAGESIZE)

כדי לקבל את הרשומות הרצויות.
לשם הדוגמא, נניח ש:

1
2
3
4
5
6

@PAGESIZE = 5
@CURRENT_PAGE = 2

(5*2)-5 = 5

כלומר, מה שאנחנו מבקשים זה "תן לי את כל הרשומות שמיקומן גדול מ-5". ואלו הן בעצם הרשומות של הדף השני.
ה-ROWCOUNT שהגדרנו קודם, ידאג לכך שיוחזרו לנו רק @PAGESIZE רשומות, כלומר 5, בדוגמא שלנו

ה-SP במלואה

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE PROCEDURE sp_SearchResults
@PAGESIZE INT,
@CURRENT_PAGE INT,
@searchTerm varchar(50)

SET ROWCOUNT @PAGESIZE

SELECT au_id, au_lname, au_fname, phone
FROM authors A
WHERE (au_fname LIKE '%' + @searchTerm + '%') AND
             ((SELECT COUNT(*)
               FROM authors A2
               WHERE A2.au_lname <= A.au_lname AND au_fname LIKE '%' + @searchTerm + '%')
        > (@PAGESIZE * @CURRENT_PAGE) – @PAGESIZE)
ORDER BY au_lname

SET ROWCOUNT 0

רעיון המאמר נלקח מהאתר http://www.sql-server-performance.com

בהצלחה!

תגיות: paging, SQL, SQL Server, TSQL, חלוקה, עמודים

חלוקה לעמודים מתוך ה-SQL Server

SET ROWCOUNT

יצירת ה-Stored Procedure

השאילתה

ה-SP במלואה

עוד בנושא זה

תגובות בפייסבוק

תגיות