Θα ηθελα να ρωτησω πως μπορω να εχω σορταρισμενα τα δεδομενα ενος πινακα ανα πασα ωρα και στιγμη, χωρις να χρειαζεται εγω να περιλαμβανω το order by στα queries που εχω.

Μπορει να γινει με καποια store procedure ή υπάρχει επιλογή από τις ιδιότητες του πίνακα?

ευχαριστώ εκ των προταίρων

Αλέξανδρος

υ.γ.

MS SQL SERVER 2000 & MS SQL SERVER 2005

Θα πρέπει να δημιουργήσεις ένα index που θα περιλαμβάνει τα πεδία με τη σειρά του order που θέλεις και θα τον δηλώσεις σαν clustered προσοχή μόνο ένας clustered index μπορεί να υπάρχει.

---

Ιωάννης Μανουσάκης

Μπορείς να ορίσεις μια View η οποία θα έχει τα δεδομένα ταξινομημένα κατά τη σειρά που θέλεις, και μετά να κάνεις τα SELECT σου από αυτή τη View.

Π.χ. για τη Northwind και τον πίνακα Customers θα μπορούσαμε να έχουμε κάτι τέτοιο:

CREATE VIEW dbo.TestView
AS
SELECT    
 TOP 100 PERCENT
   CustomerID
 , CompanyName
 , ContactName
 , ContactTitle
 , Address
 , City, Region
 , PostalCode
 , Country
 , Phone
 , Fax
FROM 
 dbo.Customers
ORDER BY
 City DESC

Το οποίο ταξινομει τις εγγραφές κατά το πεδίο CITY κατά φθίνουσα σειρά. Αν μετά κάνεις ένα SELECT * FROM dbo.TestView τα δεδομένα σου θα έρθουν σορταρισμένα

Εύλογες απορίες που μπορεί να έχεις:

1. Τι είναι αυτό το TOP 100 PERCENT; Δεν αυτοκαταργείται μια τέτοια δήλωση;

Αν επιχειρήσεις να βάλεις ORDER BY clause κατά την κατασκευή μιας View χωρίς να χρησιμοποιήσεις TOP clause θα χτυπήσει σφάλμα. Συγκεκριμένα θα λάβεις κάτι σαν το παρακάτω:

Server: Msg 1033, Level 15, State 1, Procedure TestView, Line 19
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

2. Μπορώ να χρησιμοποιήσω αυτή τη View αντί του πίνακα για τις υπόλοιπες εργασίες μου (π.χ. UPDATE, DELETE κλπ);

Οχι. Ο λόγος είναι ακριβώς αυτό το αναθεματισμένο TOP. Π.χ αν επιχειρήσω να κάνω το εξής στην TestView:

UPDATE dbo.TestView
SET ContactTitle = 'Owner1'
WHERE CustomerID = 'WOLZA'

Θα πάρω ένα σφάλμα όπως το παρακάτω:

Server: Msg 4427, Level 16, State 1, Line 1
The view or function 'testview' is not updatable because the definition contains the TOP clause.

Πιστεύω με αυτά τα..ολίγα να βοήθησα κάπως. Αλλιώς, εδώ είμαστε πάλι!

 

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Μπράβο cap πολύ ωραία εξήγησες στον φίλο πως θα μπορούσε να το κάνει με view αλλά εξαιτίας της δεύτερης περίπτωσης νομίζω ότι το καλύτερο που έχει να κάνει είναι να φτιάξει έναν clustered index.

Βέβαια μπορεί στον client να κάνει sort το dataset οπότε δέν χρειάζεται να κάνει τίποτα από τα παραπάνω και να έχει την λειτουργικότητα που θέλει.

---

Ιωάννης Μανουσάκης

Δεν θα συμφωνήσω απόλυτα με την λογική του clustered index, γιατί περιορίζει. Να ξεκαθαρίσω όμως πρώτα το εξής: Αν είναι το μοναδικό index στον πίνακα δεν χρειάζεται να είναι clustered. (Οχι οτι μας πειράζει να είναι, απλά δεν χρειάζεται)

Τωρα, αν χρησιμοποιήσεις το clustered index απλά για να αποκτήσεις το default sort order τότε σημαίνει οτι το primary key σου δεν θα έχει clustered index, πράγμα που σημαίνει οτι θα έχεις χάσει τη χρήση για την οποία ενδείκνυνται τα clustered indexes, δηλαδή την εφαρμογή τους πάνω στο πεδίο που αποτελεί το primary key (πράγμα που κάνει και by default ο Enterprise Manager όταν δημιουργείς ένα primary key χωρίς να έχεις ορίσει άλλο clustered index - δημιουργεί ένα clustered index επάνω στο πεδίο που ορίζεις ως primary key).

Βεβαια, αυτό είναι προσωπική άποψη, και η τελική προτίμηση εξαρτάται πάντοτε από το είδος της χρήσης (π.χ. πίνακες με εγγραφές που δεν αλλάζουν συχνά και που δεν προσπελάζονται με χρήση του primary key θα μπορούσαν, απο μια οπτικη γωνία, να χρησιμοποιήσουν αυτό το τέχνασμα για να έχουν ένα default sort order).

 

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Cap σου παραθέτω ένα κομματι από το BOL του SQL SERVER

Using Clustered Indexes

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. Also, if there is a column(s) that is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.

Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

 

Note  PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried in this way rather than by employee ID.

Οπότε βλέπουμε ότι απλά by default ο SQL δημιουργεί έναν clustered index στο primary key όταν δέν υπάρχει άλλος clustered index δηλαδή ανάλογα μέ το τι δεδομένα θέλουμε περισσότερο να κάνουμε αναζητήσεις στον πίνακά μας πάνω σ'αυτά πρέπει να δηλώσουμε τον clustered index το οποίο μπορεί να μην είναι απαραίτητα το PRIMARY KEY

---

Ιωάννης Μανουσάκης

Νομίζω πως η απάντηση δεν είναι τόσο απλή στο αρχικό ερώτημα και εξαρτάται από πολλούς παράγοντες. Ίσως να ειναι ένα μίγμα των παραπάνων προτάσεων-λύσεων που γίνονται.

Και εξηγούμε. Ο πίνακας αυτός τι ρυθμό εισαγωγής, μεταβολής, διαγραφής εγγραφών θα έχει; Είναι αρχικός πίνακας στην βάση στον οποίο δείχνουν Foreign Keys οπότε η επιλογή εγγραφών σε αυτόν τον πίνακα συνήθως δεν θα γίνεται βάση Primary Key κτλ.

'Ολα τα παραπάνω που γράφω βέβαια αρχίζουν να έχουν σημασία όταν ο πίνακας αυτός θα έχει πολλές εγγραφές αλλοιώς εντάξει δεν το αναλύουμε τόσο.

Οπότε ανάλογα με τα παραπάνω ερωτήματα και τις απαντήσεις να οδηγηθείς στη σωστή λύση.

Η ιδέα του View είναι αρκετά καλή και θα μπορούσες τα updates,deletions να τα κάνεις πάνω στον αρχικό πίνακα. Την ιδέα του clustered index την απορρίπτω αν ο πίνακάς σου έχει πολλά inserts, deletions, updates αλλά από την άλλη αν τα δεδομένα του δεν αλλάζουν συχνά τότε το προτιμώ σε πίνακα με πολλές εγγραφές. Από την άλλη λέγοντας προτιμώ μην αρχίσουμε να βάζουμε όλα τα πεδία του πίνακα σε clustered index. Με μέτρο, με πολύ μέτρο θα έλεγα...

Βέβαια όλα αυτά που λέμε εδώ είναι γενικές προτάσεις με στόχο τη βέλτιστη λύση. Αυτά που προτάθηκαν σαν μία μέση οδό είναι αρκετά ικανοποιητικά, εγώ το προχώρησα λίγο παρακάτω.

Όπως είπε σωστά ο Σωτήρης, το primary key δεν πρέπει να είναι υποχρεωτικά clustered index. Μπορεί να είναι και σε μη clustered. Από default, όταν ορίζεις το primary key μέσα από τον Enterprise Manager, μπαίνει ως clusterd index. Προσθήκη: Επειδή το κάνει από default ο EM, δεν συνεπάγεται ότι είναι και η βέλτιστη λύση.

 Άρα το ερώτημα είναι για ποιά πεδία προορίζονται τo clustered index ενός πίνακα. Η απόφαση για τι θα θέσουμε ως clustered key καθορίζεται σε πρώτη φάση αποκλειστικά και μόνο από τα queries που προβλέπουμε ότι θα τρέξουμε πάνω στον πίνακα. Τα queries που ευεργετούνται από το clustered index είναι (φυσικά) όσα έχουν το ORDER BY clause (ASCending ή DESCending, το ίδιο κάνει, δεν παίζει ρόλο) όπως επίσης και όλα όσα έχουν ranges τιμών πάνω στο πεδίο που έχουμε βάλει το clustered index. Προσθήκη: Πεδία που αποτελούν foreign keys (και κατ' επέκταση λαμβάνουν μέρος σε joins) δεν αποτελούν καλούς υποψήφιους για clustered indexes.

Για παράδειγμα, αν έχω έναν πίνακα με κλειδί το πεδίο ProductID και έχω queries με WHERE clause του τύπου "WHERE ProductID=X" καθώς και του τύπου "WHERE ProductDate>FromDate AND ProductDate<ToDate" είναι σαφώς προτιμότερο να βάλω το clustered index στο πεδίο ProductDate και όχι στο ProductID.

Η ρύθμιση σωστών indexes παίζει πολύ μεγάλο ρόλο για το performance της εφαρμογής και δεν πρέπει να τη σπαταλήσουμε επιπόλαια απλά και μόνο πάνω σε κάποιο πεδίο βάσει του οποίου θα θέλαμε να βλέπουμε default ταξινόμηση, εκτός αν συμβαδίζει αυτή η επιθυμία με την προηγούμενη λογική. Οι επιθυμίες αλλάζουν, μπορεί αύριο να θέλουμε να τα βλέπουμε διαφορετικά ταξινομημένα, τι θα κάνουμε τότε; DROP και recreate τα indexes; Η default ταξινόμηση των clustered indexes θα πρέπει να εκλαμβάνεται ως side-effect και όχι ως feature. Ας μην ξεχνάμε ότι η σχεσιακή θεωρία λέει ότι δεν πρέπει να υπολογίζουμε ότι θα πάρουμε τα δεδομένα με καμία συγκεκριμένη σειρά, αν θέλουμε ταξινόμηση, τότε τη ζητάμε (με το ORDER BY) ή την κάνει ο client όπως είπε ο Γιάννης.

 

---

Vir prudens non contra ventum mingit

Μανώλη νομίζω ότι κάλυψες πάρα πολύ ωραία το θέμα .

Ελπίζω να μπορέσαμε μέσα απο αυτή την συζήτηση να βοηθήσαμε αρκετά τον φίλο μας πάνω στο συγκεκριμμένο θέμα.

---

Ιωάννης Μανουσάκης

imanos wrote:

Οπότε βλέπουμε ότι απλά by default ο SQL δημιουργεί έναν clustered index στο primary key όταν δέν υπάρχει άλλος clustered index δηλαδή ανάλογα μέ το τι δεδομένα θέλουμε περισσότερο να κάνουμε αναζητήσεις στον πίνακά μας πάνω σ'αυτά πρέπει να δηλώσουμε τον clustered index το οποίο μπορεί να μην είναι απαραίτητα το PRIMARY KEY

Δεν διαφωνώ, όπως ανέφερα και πιό πρίν και όπως είπαν ήδη και άλλοι συνάδελφοι, πάντοτε η απόφαση για το που θα χρησιμοποιηθεί το clustered index εξαρτάται από το είδος της χρήσης που κανείς επιθυμεί να κάνει στον πίνακα. Πιστεύω όμως οτι συμφωνούμε οτι σε ένα heavily-updated πίνακα, το να χρησιμοποιήσει κανείς το clustered index για να επιτύχει default sort order δεν αποτελεί βέλτιστη επιλογή μια και στερεί την απόδοση από τις αναζητήσεις που θα γίνονται με βάση το primary key του.

Σε περιπτώσεις heavily-read πινακα που δεν έχει ιδιαίτερα (ή καθόλου) updates, ενδέχεται η χρήση ενός clustered index για το default sort order να ωφελούσε την κατάσταση και στο θέμα της απόδοσης, εκτός της ταξινόμησης φυσικά.

 

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Επίσης πρέπει να αναφέρουμε ότι clustered index υποστηρίζονται και σε views .

Μόνο στις SQL ENTERPRISE EDITION και DEVELOPER για το SQL2000 για τον 2005 επειδή δέν το έχω κοιτάξει μάλλον πρέπει ο Μανώλης να μας δώσει τα φώτα του.

Σημείωση.

Τα views πρέπει να κάνουν access μόνο σε DataTable και όχι άλλα views.

---

Ιωάννης Μανουσάκης

Σωστά, μπορώ να έχω ένα view με clustered index από κάτω, ονομάζονται indexed views. Στον SQL Server 2000 παίζουν σε Enterprise και Developer editions, στον 2005 σε όλες τις εκδόσεις.

Σωτήρη, τα clustered indexes δεν προσφέρουν performance gain όταν γίνεται αναζήτηση για unique values καθώς δεν έχουν να κάνουν με τη μοναδικότητα των εγγραφών.

Επίσης, σε ένα πίνακα με αρκετά OLTP operations, δεν αλλάζει δραματικά το performance αν μπει clustered index ή όχι. Έτσι κι αλλιώς η ύπαρξη του non-clustered index που θα βρίσκεται στο κλειδί ή στα κλειδιά, σημαίνει ότι κάθε Insert/Update/Delete θα πρέπει να παίξει με τα keys για να τοποθετήσει κάθε εγγραφή που αλλάζει στην κατάλληλη θέση. Αφήστε που τυπικά, οι πίνακες έχουν πολλαπλά indexes. Εξάλλου ο SQL Server έχει μηχανισμούς που μειώνουν το overehead που προκαλούν τα IUD στην αναδιοργάνωση των indexes. Πάντως, αντίθετα από αυτό που λένε και οι κινέζοι, καλύτερα πλούσιος και υγιής παρά φτωχός και άρρωστος, δεν μπορούμε να τα έχουμε όλα. Όντως τα πολλά indexes βελτιώνουν τα read operations και βλάπτουν τα write operations και γι αυτό αποτελεί τέχνη η επιλογή των σωστών indexes.

Παράκληση: Γιάννη, φώναζέ με Μάνο και όχι Μανώλη, δε μου αρέσει το δεύτερο...

---

Vir prudens non contra ventum mingit

KelMan wrote:

Σωτήρη, τα clustered indexes δεν προσφέρουν performance gain όταν γίνεται αναζήτηση για unique values καθώς δεν έχουν να κάνουν με τη μοναδικότητα των εγγραφών.

Εχμ, εχεις απόλυτο δίκιο. Ακυρο αυτό που είπα περι απόδοσης και clustered indexes.

---

Σωτήρης Φιλιππίδης

DotSee Web Services

Μάνο χίλια συγνώμη

---

Ιωάννης Μανουσάκης

Κατ'αρχήν σας ευχαριστω παρα πολύ όλους!

Κάθησα το βράδυ και έπαιξα με την λύση Views και την λύση clustered indexes.

Ο πίνακας έχει πολλά inserts και για κάθε insert 2 υποχρεωτικά updates (υπάρχει πιθανότητα να γίνουν και περισσοτερα σε κάθε εγγραφή).

Ετσι κατέληξα στην λύση του view (και ευχαριστω παρα πολύ για το παράδειγμα cap).

Σας ευχαριστώ και πάλι όλους.



Αλέξανδρος