SQL Server (και άλλες databases)

Απ: Δημιουργία indexes σε read-only βάση.

KelMan — Thu, 07 Dec 2006 22:15:31 GMT

Ναι, θα παίξει ωστόσο στην συγκεκριμένη περίπτωση δεν είναι απαραίτητο γιατί όπως είπαμε όταν έχεις πολλαπλά arguments που συνδέονται με AND, σου αρκεί έστω και ένα από αυτά να βασίζεται σε κάποιο index. Αν είχες όμως OR, τότε μια τέτοια λύση που περιγράφεις θα ήταν απαραίτητη...

Απ: Δημιουργία indexes σε read-only βάση.

dimkasta — Thu, 07 Dec 2006 18:25:20 GMT

ΠΩΠΩ πραγματικό σεμινάριο το πόστ σου kelman. THANKS.

Στο θέμα με τα non-sarg και τα indexed views, έχω sqlexpress και θα ήθελα να παίζει σωστά και σε τέτοια βάση οπότε μιας και μιλάμε για SPs σκέφτηκα το εξής. Το query μας είναι αυτό.

SELECT * FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (datePart(year,dbo.myEmails.dateReceived) = @thisYear) AND (datePart(month,dbo.myEmails.dateReceived) = @thisMonth)

Το πρόβλημα προφανώς είναι ότι χρησιμοποιείται το function μέσα στο select. Άν πιο πρίν στο SP κάνουμε κάτι τέτοιο?

declare @y numeric

declare @m numeric

declare @d numeric

declare @startDate DateTime

declare @endDate DateTime

set @y = @thisYear

set @m = 1

set @d = 1

set @startDate = select dateadd(mm,(@y-1900)* 12 + @m - 1,0) + (@d-1)

set @endDate = select dateadd(mm,(@y -1900)* 12 + @m + 1 - 1,0) + (@d-1)

και στη συνέχεια στο πρώτο query χρησιμοποιήσω το between? Δηλαδή....

SELECT * FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (dbo.myEmails.dateReceived between @startDate and @EndDate)

Λογικά έτσι θα παίξει με το index ε? Δεν ξέρω βέβαια τι overhead θα έχω με τους αρχικούς υπολογισμούς... Aν χρησιμοποιούσα string concatenation και μετά conversion σε datetime προφανώς θα ήτανε χειρότερα ε?

Απ: Δημιουργία indexes σε read-only βάση.

KelMan — Thu, 07 Dec 2006 09:02:07 GMT

Ωραία, έχουμε και λέμε:

dimkasta wrote:

SELECT DISTINCT DATEPART(year, dbo.myEmails.dateReceived) AS years
FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser)
ORDER BY years DESC

Θα ξεκίναγα βάζοντας στα PKs (τα δύο ID columns) από ένα Non-Clustered Unique Index. Αυτό θα βοηθήσει τόσο οποιαδήποτε αναζήτηση γίνει με βάση το PK όσο και το JOIN. Δεν χρειάζεται index στο UserID (FK) του myEmails καθώς το IDs του myUsers έχει υψηλό selectivity (καθότι PK). To dateReceived πεδίο του myEmails είναι ό,τι πρέπει για clustered index καθώς έχεις ταξινόμηση.

dimkasta wrote:

SELECT count(*) from myemails
FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (datePart(year,dbo.myEmails.dateReceived) = @thisYear) AND (datePart(month,dbo.myEmails.dateReceived) = @thisMonth)

και

dimkasta wrote:

SELECT * FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (datePart(year,dbo.myEmails.dateReceived) = @thisYear) AND (datePart(month,dbo.myEmails.dateReceived) = @thisMonth)

Αυτά τα δύο queries έχουν το μειονέκτημα ότι χρησιμοποιούν τα πεδία μέσα σε συναρτήσεις, πράγμα που σημαίνει ότι κατά πάσα πιθανότητα αγνοούνται τα indexes (λέμε δηλαδή ότι το (datePart(year,dbo.myEmails.dateReceived) = @thisYear) είναι non-SARG, όπου SARG=Searchable Argument, δηλαδή argument που χρησιμοποιεί indexes). To καλό είναι ότι το WHERE αποτελείται από τρία arguments που συνδέονται με AND λογικό τελεστή οπότε αρκεί έστω και ένα από αυτά να έχει καλό selectivity, πράγμα που συμβαίνει με το argument του username. Υποθέτω ότι είναι πεδίο με unique τιμές, οπότε ένα non-clustered unique index πάνω του είναι μια χαρά και βολεύει ολόκληρο το WHERE.

Υπάρχει και άλλη μια λύση. Φτιάχνεις ένα view που κάνει SELECT τα non-SARG πεδία του where και βάζεις πάνω του indexes, φτιάχνεις δηλαδή ένα indexed view. Ο query optimizer είναι αρκετά έξυπνος ώστε να χρησιμοποιήσει αυτά τα indexes σε queries που τα χρειάζονται έστω και αν δεν αναφέρονται στο συγκεκριμένο view! To μόνο κακό είναι ότι για τα indexed views απαιτείται SQL Server Enterprise edition.

dimkasta wrote:

SELECT * FROM dbo.myEmails
WHERE IDs = @thisEmail

Αυτό έτσι κι αλλιώς θα εκμεταλλευτεί το unique non-clustered index που έχει ήδη καθοριστεί.

Όλο αυτό το indexing είναι μια καλή αρχή. Πριν αρχίζεις να παίζεις, χρειάζεται να ορίσεις ένα performance baseline ώστε όταν κάνεις τις δοκιμές σου να έχεις ένα μέτρο σύγκρισης με κάποια προηγούμενη κατάσταση. Κατόπιν, μπορείς να δοκιμάσεις εναλλακτικές λύσεις ή να χρησιμοποιήσεις τον Database Tunning Advisor.

Απ: Δημιουργία indexes σε read-only βάση.

dimkasta — Thu, 07 Dec 2006 05:55:40 GMT

OK ανεβάζω ένα παράδειγμα του πως είναι φτιαγμένη η εφαρμογή. Έχουμε χρήστες και έχουμε και emails.

Tα select είναι αυτής της λογικής

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

SELECT DISTINCT DATEPART(year, dbo.myEmails.dateReceived) AS years
FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser)
ORDER BY years DESC

Για να μετρήσω τα μηνιαία μηνύματα

SELECT count(*) from myemails
FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (datePart(year,dbo.myEmails.dateReceived) = @thisYear) AND (datePart(month,dbo.myEmails.dateReceived) = @thisMonth)

Για να δείξω τα μηνιαία μηνύματα

SELECT * FROM         dbo.myEmails INNER JOIN
                      dbo.myUsers ON dbo.myEmails.UserID = dbo.myUsers.IDs
WHERE     (dbo.myUsers.username = @thisUser) AND (datePart(year,dbo.myEmails.dateReceived) = @thisYear) AND (datePart(month,dbo.myEmails.dateReceived) = @thisMonth)

Και τέλος για να δείξω το επιλεγμένο email,

SELECT * FROM         dbo.myEmails

WHERE     IDs = @thisEmail

H λογική είναι αυτή σχεδόν σε όλους τους πίνακες. Περιμένω τα φώτα σας με ανυπομονησία...

Απ: Δημιουργία indexes σε read-only βάση.

KelMan — Thu, 07 Dec 2006 02:11:42 GMT

Για να μπορέσουμε να σου δώσουμε σωστές πληροφορίες σχετικά με το πως να κάνεις optimize τα indexes χρειάζεται να ξέρουμε το schema και τα SELECT statements που χρησιμοποιείς. Διαφορετικά, μόνο γενικά πράγματα μπορούμε να πούμε, ωστόσο ενδέχεται αυτά τα γενικά να μην είναι καθόλου κατάλληλα για την περίπτωσή σου.

Επιπρόσθετα, ψάξε για συζητήσεις που έχουμε κάνει στο παρελθόν για τα indexes. Προσοχή: Αυτό που παίζει μεγαλύτερο ρόλο ειναι τα SELECT που χρησιμοποιείς. Όσο για το clustered index πάνω στο PK, αυτό συνήθως δεν είναι σωστό. Clustered index βάζουμε σε πεδία βάσει των οποίων επιλέγουμε ranges εγγραφών (πχ στο orderdate αν χρησιμοποιείς "WHERE orderdate BETWEEN @date1 AND @date2") ή σε πεδία που θέλεις να βασίσεις ταξινόμηση (πάνε στο ORDER BY). Υπάρχει μια μεγάλη παρανόηση ότι το PK πάει με το clustered index και αυτό οφείλεται στο γεγονός ότι είναι το default πράγμα που συμβαίνει κατά το CREATE TABLE αν δεν ορίσεις explicitly κάτι διαδορετικό καθώς και στο ότι οι βάσεις-παραδείγματα (pubs, Northwind) ακολουθούν αυτό το pattern (υποθέτω γιατί δεν υπάρχουν τυποποιημένα queries για να γίνουν optimized).

Απ: Δημιουργία indexes σε read-only βάση.

Dimitris Papadimitriou — Thu, 07 Dec 2006 02:01:15 GMT

Καταρχήν διάβασε αυτό: http://www.dotnetzone.gr/cs/forums/21537/ShowPost.aspx

Είναι συζήτηση που έγινε πριν μερικές μέρες και είναι ακόμα ζεστή!

Κατά τα άλλα περιμένω τις απαντήσεις των υπολοίπων με ενδιαφέρον!

Δημιουργία indexes σε read-only βάση.

dimkasta — Thu, 07 Dec 2006 01:29:06 GMT

Έχω μια βάση η οποία κρατάει read-only πληροφορία από συγχρονισμό με άλλα συστήματα. Η βάση περιέχει μόνο δεδομένα και καθόλου metadata, πχ ΡΚ, FK, indexes κλπ. Τα υπόλοιπα συστήματα κανονίζουνε μόνα τους το integrity των δεδομένων, και εγώ απλά βεβαιώνομαι με transactions κατά το synchronisation ότι όλα τα δεδομένα έχουνε αντιγραφεί σωστά. Θέλω το σχόλιό σας πάνω σε αυτό. Στη βάση γίνονται ΜΟΝΟ select.

Από κει και πέρα, επειδή μιλάμε για αρκετές γραμμές, η δημιουργία indexes είναι μάλλον απαραίτητη. Ο προβληματισμός μου είναι ο εξής. Έχω 2 ειδών πίνακες χοντρικά.

Έναν κεντρικό, ο οποίος έχει τα στοιχεία του προσώπου, στον οποίο η αναζήτηση γίνεται σύμφωνα με το username, ενώ το PK είναι FK στους υπόλοιπους πίνακες. Πού θα βάζατε το clustered index? στο ΡΚ προφανώς ε? και το username ένα unclustered?

Mερικούς πίνακες που περιέχουνε πληροφία όπως μηνύματα κλπ. Εδώ το select γίνεται με join στο FK επιστρέφοντας μόνο του χρήστη, και με where στην κολώνα με την ημερομηνία, μέσα σε datepart(year,x) και datepart(month,χ), ώστε να γίνεται drill-down σύμφωνα με έτος και μήνα. Πού θα βάζατε το clustered? Στο FK ή στο date? Το ΡΚ αυτών των πινάκων δεν χρησιμοποιείται, παρά μόνο για reference άν θέλω το τελικό drill-down detailed view.

Eπαναλαμβάνω δεν γίνεται insert/update στους πίνακες και τα indexes θα γίνονται drop πρίν και create ξανά μετά το συγχρονισμό.

Καμιά συμβουλή?