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

Απ: Paging στη βάση με ROW_NUMBER ?

KelMan — Thu, 12 Oct 2006 02:41:31 GMT

Για να αποδώσω και τα εύσημα, πέρυσι είχε χρειαστεί να κάνω ένα performance auditing και fine-tunning. Eίχα χρησιμοποιήσει παρόμοιο κώδικα που τον είχα βασίσει σε ένα άρθρο από το SQL-Server-Performance.com. Και τώρα ήρθε κουτί στη κουβέντα μας

Απ: Paging στη βάση με ROW_NUMBER ?

KelMan — Thu, 12 Oct 2006 02:27:33 GMT

Και όχι μόνο αυτό, στο 1000στο post έγραψα σε C#

Βασικά, ξεκίνησα να γράφω:

"Και το χώσιμο συνεχίζεται. Πρέπει να το κάνετε έτσι, να το κάνετε αλλιώς, κλπ, κλπ" αλλά μετά με έπιασαν οι καλοσύνες μου...

Απ: Paging στη βάση με ROW_NUMBER ?

anjelinio — Thu, 12 Oct 2006 02:15:39 GMT

Εξιλεώθηκες πάραυθα για το πρωινό χώσιμο Μάνο !!! :D Ωραίος !

( τελικά το χιλιοστό σου post ήταν γραφτό να είναι super :D ... επετειακοί λόγοι;;; )

Απ: Paging στη βάση με ROW_NUMBER ?

KelMan — Thu, 12 Oct 2006 01:15:24 GMT

Λοιπόν, εγώ το πήγα λίγο διαφορετικά το stress test και πάλι τα αποτελέσματα με εξέπληξαν…

Έχω αλλάξει λίγο τον κώδικα του Anjelinio. Έχω κλείσει το connection pooling μέσα από το connection string μιας και τρέχει το test από ένα PC. Επίσης, αφαίρεσα τον κώδικα που μετράει τα statistics μιας και θα τα μετρήσω στον server μέσω του Profiler κι επίσης άλλαξα το ExecuterReader και το έκανα NonQuery μιας και δεν με ενδιαφέρει να μετρήσω τον χρόνο που χρειάζονται να γυρίσουν τα data.

Μια μικρή λεπτομέρεια. Έβαλα δύο μεθόδους για να τρέχω τις δύο versions της δουλειάς που θέλω να κάνω και ορίζω το connection object μέσα σε κάθε μέθοδο προσθέτοντας το «Application Name='StressTest.StoredProcedure'» και «Application Name='StressTest.Text'» ανάλογα, σε κάθε ένα από τα δύο connection strings ώστε μετά να μπορέσω να διαβάσω με ευκολία τα στατιστικά του Profiler.

Τέλος, γύρισα τον TSQL κώδικα να παίζει στην AdventureWorks για να μπορεί οποιοσδήποτε να δοκιμάσει αυτά που γράφω και συγκεκριμένα στον πίνακα Sales.SalesOrderHeader όπου έχει περίπου 31Κ εγγραφές, ήτοι πάλι περίπου 1500 σελίδες των 20 εγγραφών (κάθε φορά ζητάω μια τυχαία σελίδα). Έβαλα επίσης ένα Thread.sleep με τυχαίες τιμές ως 200msec μεταξύ των κλήσεων για καλύτερο simulation, ας το πούμε αυτό το κένο «think time».

Έχουμε και λέμε:

Η νέα SP:

CREATE PROCEDURE dbo.ShowOrders
@PageIndex INT,
@PageSize INT
AS
BEGIN

WITH SalesOrders AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC)AS Row, OrderDate, PurchaseOrderNumber, AccountNumber
FROM Sales.SalesOrderHeader)

SELECT OrderDate, PurchaseOrderNumber, AccountNumber
FROM SalesOrders
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END

Και ο κώδικας:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Threading;

namespace SQLServerStressTest
{
    public partial class Form1 : Form
    {
        private const string SQL_CMD_TEXT = "WITH SalesOrders AS (SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC)AS Row, OrderDate, PurchaseOrderNumber, AccountNumber FROM Sales.SalesOrderHeader) SELECT OrderDate, PurchaseOrderNumber, AccountNumber FROM SalesOrders WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            runStoredProcedureTest();
            runTextTest();
            MessageBox.Show("Done!");
        }

        private void runStoredProcedureTest()
        {
            Random rnd = new Random();
            int repetitions = int.Parse(txtUsers.Text);
            for (int i = 0; i <= repetitions - 1; i++)
            {
                TestHarness oLoadConnection = new TestHarness();
                Thread oThread = new Thread(new ThreadStart(oLoadConnection.StartStoredProcedure));
                oThread.Start();
                Thread.Sleep(rnd.Next(1,200));
            }
        }

        private void runTextTest()
        {
            Random rnd = new Random();
            int repetitions = int.Parse(txtUsers.Text);
            for (int i = 0; i <= repetitions - 1; i++)
            {
                TestHarness oLoadConnection = new TestHarness();
                Thread oThread = new Thread(new ThreadStart(oLoadConnection.StartTextCommand));
                oThread.Start();
                Thread.Sleep(rnd.Next(1, 200));
            }
        }

    }

    public class TestHarness
    {
        public void StartStoredProcedure()
        {
            Random rnd = new Random();
            int pageIndex = rnd.Next(1, 31460);
            int pageSize = 20;

            SqlConnection cn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            cn.ConnectionString = "Integrated Security=true;Initial Catalog=AdventureWorks;Data Source=localhost;Connect Timeout=600;Pooling=false;Application Name='StressTest.StoredProcedure'";

            try
            {
                cmd.Connection = cn;
                cmd.CommandTimeout = 600;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ShowOrders";
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);

                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }

        public void StartTextCommand()
        {
            Random rnd = new Random();
            int pageIndex = rnd.Next(1, 1570);
            int pageSize = 20;

            SqlConnection cn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            cn.ConnectionString = "Integrated Security=true;Initial Catalog=AdventureWorks;Data Source=localhost;Connect Timeout=600;Pooling=false;Application Name='StressTest.Text'";

            try
            {
                cmd.Connection = cn;
                cmd.CommandTimeout = 600;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "WITH SalesOrders AS (SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC)AS Row, OrderDate, PurchaseOrderNumber, AccountNumber FROM Sales.SalesOrderHeader) SELECT OrderDate, PurchaseOrderNumber, AccountNumber FROM SalesOrders WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize";
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", pageSize);

                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }

    }
}

Απλά χρειάζεται μια φόρμα με ένα κουμπάκι και ένα textbox.

Τρέχουμε το προγραμματάκι και παράλληλα ξεκινάμε τον Profiler. Στο TraceProperties κάνουμε τις εξής ρυθμίσεις:

Στο tab General, βάζουμε check στο “Save to table” και ορίζουμε στο dialog box που θα εμφανιστεί την βάση και τον πίνακα. Εγώ όρισα στην AdvemtureWorks τον πίνακα «StressTest». Στον tab Events Selection αφήνουμε μόνο checks στα RPC:Completed και SQL:BatchCompleted. Τις στήλες δεν χρειάζεται να τις πειράξουμε. Κατόπιν πατάμε Run να ξεκινήσει το tracing και φέρνουμε μπροστά το προγραμματάκι όπου το τρέχουμε. Μιας και περιέχει αρκετές random τιμές όσες περισσότερες επαναλήψεις κάνουμε, τόσο το καλύτερο.
Πλέον, αφού έχει τελειώσει, μπορούμε να ανοίξουμε το SQL Server Management Studio και να τρέξουμε το παρακάτω query:

SELECT ApplicationName, avg(CPU) [Avg CPU], avg(Duration) [Avg Duration]
FROM StressTest
GROUP BY ApplicationName

Τα δικά μου αποτελέσματα (σε Virtual PC με 1G RAM) για 2000 (1000 + 1000) επαναλήψεις είναι:

ApplicationName  Avg CPU Avg Duration
StressTest.Text  80  88529
StressTest.StoredProcedure 104  112980

και ναι, είναι καλύτερο το text command από το stored procedure και από πλευράς CPU και από πλευράς ταχύτητας!

Χμμμμ και ξανά χμμμμ… Θέλει ψάξιμο το πράγμα… Όποιος μπορεί ας κάνει post και τους δικούς του χρόνους χρησιμοποιώντας την ίδια μεθοδολογία.

Απ: Paging στη βάση με ROW_NUMBER ?

Χρήστος Γεωργακόπουλος — Wed, 11 Oct 2006 21:22:47 GMT

Τα δικά μου εκτελέστηκαν σε 100 κύκλους, κάθε ένας εκ τους οποίους περιελάμβανε 40 εκτελέσεις με text command και 40 με sp. Οπότε δεν επηρεάστηκε.

Απ: Paging στη βάση με ROW_NUMBER ?

agmarios — Wed, 11 Oct 2006 21:12:28 GMT

έχω την εντύπωση (χωρίς να είμαι sql server expert) ότι τα στατιστικά του procedure χαλάνε από την πρώτη εκτέλεση, όπου πρέπει όχι μόνο να κάνει compile αλλά και να φτιάξει execution plan και όλα τα άλλα καλούδια τα οποία έχει μετά έτοιμα και τα χρησιμοποιεί δίνοντας καλύτερους χρόνους. Αντίθετα με το text έχουμε μικρότερες αποκλίσεις που εξηγούνται από αστάθμιτους παράγοντες.

Αν το τρέξεις 1000 φορές (για να επιρρεάζει λιγότερο η πρώτη φορά τα στατιστικά ) τι δίνει;

Μάριος

Απ: Paging στη βάση με ROW_NUMBER ?

anjelinio — Wed, 11 Oct 2006 21:09:13 GMT

Χμμμ ... 1,80% δεν είναι κακό, σε tradeoff με το flexibility ... σε σχέση με το χρόνο που θα χρειαστεί δε να κωδικογράφεται ένα sp ανα paged select ... μμμ ... είδωμεν !

Απ: Paging στη βάση με ROW_NUMBER ?

Χρήστος Γεωργακόπουλος — Wed, 11 Oct 2006 20:52:51 GMT

Τα δικά μου test δείχνουν 1,80% περισσότερο χρόνο για τα text commands (απλό select, εφαρμογή που μιλάει κατευθείαν με τη βάση, η βάση σε άλλο μηχάνημα). Θα πρέπει να λάβεις υπόψη σου όμως την αύξηση του μεγέθους του request στα text commands. Αν ο sql server είναι μακριά και η επικοινωνία είναι αργή, αυτό μπορεί να επιβαρύνει σημαντικά τα text commands.

Απ: Paging στη βάση με ROW_NUMBER ?

anjelinio — Wed, 11 Oct 2006 19:16:04 GMT

Μας έχωσες Μάνο πρωινιάτικα ... :D

Λοιπόν ... δεν περίμενα αυτό που είδα !!! Πήρα το post, και έφτιαξα μια αντίστοιχη stored procedure στη βάση μας, παίζοντας στο μεγαλύτερο view μας - 20883 rows !

Έγραψα λοιπόν και το παρακάτω μικρό test ...

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleTests.Paging

{

/// <summary>

/// Summary description for PagingTest.

/// </summary>

public class PagingTest

{

public delegate void FetchData(int pageIndex, int pageSize);

public PagingTest()

{

// TODO: Add constructor logic here

}

public void Test() {

double averageProc = 0.0;

double averageText = 0.0;

for(int i=0; i<10; i++){

double sProcTest = Measure(new FetchData(this.FetchStoredProcedure), 10, 20);

double textTest = Measure(new FetchData(this.FetchTextCommand), 10, 20);

averageProc += sProcTest;

averageText += textTest;

Console.WriteLine("Measured stored procedure at: {0} millis, text command at: {1} millis", sProcTest, textTest);

}

Console.WriteLine("Average Proc: {0} - Average text: {1} - Exception count: {2}", (averageProc/10), (averageText/10), m_ExceptionCount);

}

SqlConnection m_Connection = null;

private int m_ExceptionCount = 0;

private SqlConnection Connection {

get {

try {

if(null==m_Connection){

m_Connection = new SqlConnection(ConfigurationSettings.AppSettings["dbConnectionString"]);

}

if(m_Connection.State!= System.Data.ConnectionState.Open){

m_Connection.Open();

}

return m_Connection;

}catch(Exception e) {

m_ExceptionCount++;

throw;

}

private void FetchStoredProcedure(int pageIndex, int pageSize) {

try {

SqlCommand cmd = new SqlCommand("ValidActivitiesView", this.Connection);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@PageIndex", pageIndex);

cmd.Parameters.Add("@PageSize", pageSize);

cmd.ExecuteReader();

}finally {

this.Connection.Close();

}

private const string SQL_CMD_TEXT = "WITH ActivityEntries AS ( SELECT ROW_NUMBER() OVER (ORDER BY CA_DateStarted DESC)AS Row, ContractActivityID, CA_ContractID, CN_Code FROM ContractActivity_ValidList_View) SELECT Row, ContractActivityID, CA_ContractID, CN_Code FROM ActivityEntries WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize";

private void FetchTextCommand(int pageIndex, int pageSize) {

try {

SqlCommand cmd = new SqlCommand(SQL_CMD_TEXT, this.Connection);

cmd.CommandType = CommandType.Text;

cmd.Parameters.Add("@PageIndex", pageIndex);

cmd.Parameters.Add("@PageSize", pageSize);

cmd.ExecuteReader();

}finally {

this.Connection.Close();

}

private double Measure(FetchData dataCall, int pageIndex, int pageSize){

// mark start time ...

long startTicks = DateTime.Now.Ticks;

// call the method ...

if(null!=dataCall)

dataCall(pageIndex, pageSize);

// mark end time

long endTicks = DateTime.Now.Ticks;

return ((endTicks-startTicks) / TimeSpan.TicksPerMillisecond);

}

... και τί ανακαλύπτω τρέχοντάς το;;; Το text command είναι κατα μέσο όρο γρηγορότερο !!! Ιδού το output:

Measured stored procedure at: 1999 millis, text command at: 1390 millis
Measured stored procedure at: 1406 millis, text command at: 1390 millis
Measured stored procedure at: 1515 millis, text command at: 1421 millis
Measured stored procedure at: 1499 millis, text command at: 1406 millis
Measured stored procedure at: 1437 millis, text command at: 1437 millis
Measured stored procedure at: 1437 millis, text command at: 1531 millis
Measured stored procedure at: 1515 millis, text command at: 1546 millis
Measured stored procedure at: 1437 millis, text command at: 1484 millis
Measured stored procedure at: 1374 millis, text command at: 1499 millis
Measured stored procedure at: 1390 millis, text command at: 1484 millis
Average Proc: 1500,9 - Average text: 1458,8 - Exception count: 0

Έτρεξα το τεστάκι 4-5 φορές. Το text cmd μου γύριζε γρηγορότερα στο 60% των δοκιμών. Αλλά αρνούμαι να πιστέψω τα αποτελέσματα, οπότε το κάνω και post εδώ να δείτε μηπως και δεν βλέπω εγώ κάποια βλακεία που ίσως έχω κάνει :D

Άντε, καλημέρα μας !

Απ: Paging στη βάση με ROW_NUMBER ?

anjelinio — Wed, 11 Oct 2006 07:04:47 GMT

Δεν έχω SQL Server 2005 στο σπίτι :(

... Καλά δεν έχουμε κανένα SQL Server MVP εδώ μέσα να μας βοηθήσει; ;) Πού είσαι;;;

Απ: Paging στη βάση με ROW_NUMBER ?

KelMan — Wed, 11 Oct 2006 06:48:54 GMT

Άντε λοιπόν, ετοίμασε ένα test harness να μας πεις

Paging στη βάση με ROW_NUMBER ?

anjelinio — Wed, 11 Oct 2006 06:10:07 GMT

Διάβαζα αυτό το πολύ ενδιαφέρον blog post, το οποίο έκανε post ο KelMan απαντώντας σε κάποιο άλλο, για μια τεχνική paging στον SQL Server με τη χρήση Stored Procedures και της function ROW_NUMBER που είναι διαθέσιμη στον SQL 2005.

Στο αρχικό post, ο author τελικά φτιάχνει μια stored procedure, η οποία κάνει και paging στη βάση πλέον, προτού επιστρέψει δεδομένα στον client:

CREATE PROCEDURE dbo.ShowLog
    @PageIndex INT, 
    @PageSize INT 
AS

BEGIN 

WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description 
FROM LOG)

SELECT Date, Description
FROM LogEntries 
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

END

Η ερώτησή μου είναι η εξής ... τι κόστος θα είχα, εάν αντί για stored procedure εκτελούσα αυτόν τον κώδικα με ένα SqlCommand τύπου Text;