Μας έχωσες Μάνο πρωινιάτικα ... :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
Άντε, καλημέρα μας !
Angel
O:]