Trivial peformance question

[Earl]

I want to retrieve a single row with only 2 columns, i.e, 2 cells. What is
the most efficient method?

 

[Sahil Malik]

DataReader

Don't forget to close the connection after you're done.

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/

 

[Cor Ligthert]

I want to retrieve a single row with only 2 columns, i.e, 2 cells. What is

the most efficient method?

Choose for a database that is able to do such short transactions fast.

Cor

 

[Miha Markic [MVP C#]]

Hi,

Either adapter.Fill or using DataReader would do.

 

[Roy Fine]

I want to retrieve a single row with only 2 columns, i.e, 2 cells. What is
the most efficient method?

Earl,

If performance is the key factor, consider a stored function that has the
two values as output parameters - and the requisite input parameters to do
the select.

Creating the DataReader and Data Adapter objects is substantial overkill,
especially if the fetched values are read only.

regards
roy fine

 

[Cor Ligthert]

Hi Roy,

Earl,

If performance is the key factor, consider a stored function that has the
two values as output parameters - and the requisite input parameters to do
the select.

Creating the DataReader and Data Adapter objects is substantial overkill,
especially if the fetched values are read only.

regards
roy fine

Interesting, can you show us how to do it with some code.

Thanks,

Cor

 

[Miha Markic [MVP C#]]

Oops, I stand corrected as I didn't take into account "single row" words
- that rules out datasets as performace leader
In this case DataReader is the easiest way.
Theoretically speaking, using sp and output parameters (as Roy suggested)
should be the fastest.
However, a guy did some timing and found out that reader is faster - don't
know if this is the rule though.
If performance is that important to you, I suggest you to implement and test
both approaches.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,

Either adapter.Fill or using DataReader would do.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I want to retrieve a single row with only 2 columns, i.e, 2 cells. What is
the most efficient method?

 

[Cor Ligthert]

Hi Miha,

Same qeustion from me to you as I did to Roy, how can I get 2 values in a
tablerow using a stored procedure and output parameters (with one execute of
course)?

I am even more curious about that

Cor

 

[coollzh]

i think OutputParameters will performance better because Datareader will
return some Database table schema

 

[Earl]

My question is also: How to get TWO output parameters?

Here is how this question came about: I needed a single value (SUM) from two
other tables, and I needed a date and amount value from the third table (the
one I referenced in my question). Because I needed set values for the first
two, and a single row for the third, a JOIN looked out of the question
(okay, maybe possible, but I didn't like what I was returning and didn't
want to fool around with expressions since I would rather do the math on the
server).

So for the two single summed values, I called ExecuteScalar on the command
object. Without using ADO classic, I'd like to do the same thing for the
single row with 2 values from one table (SQL Server). I'm now doing this
with a dataadapter and a datatable. Effective, but seems like overkill.

 

[Miha Markic [MVP C#]]

Hi Cor,

Not sure what you are asking. Here is the ExecuteNonQuery that returns two
parameters:
this.sqlCommand1.CommandText = "dbo.[doubleOutput]";

this.sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;

this.sqlCommand1.Connection = this.sqlConnection1;

this.sqlCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
false, ((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));

this.sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@a",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, false,
((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));

this.sqlCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@b",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, false,
((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));

sqlCommand1.ExecuteNonQuery();

Console.WriteLine(sqlCommand1.Parameters[1].Value + ", " +
sqlCommand1.Parameters[2].Value);

Stored proc:

CREATE PROCEDURE doubleOutput (@a int out, @b int out) AS
set @a = 1
set @b = 2

Is this the answer?

 

[Roy Fine]

Earl

Here is a quick sample app that calls a stored procedure with one input
parameter and three output parameters - and there is no cursor involved on
the client side - i.e. not DataReader object will be constructed.

This sample is for Oracle (that's my domain these days) - and I see that
Miha Markic has provided a good example of same process using SqlServer.
here is the code - but first the disclaimer - there are no comments, there
is no exception handling, and the formatting of the code is a modification
from early Stroustrup C for the sake of screen/editor real estate
conservation. There is a lot to chew on here, but look especially at the
FetchScalars method of the DataManager class.


Here goes:

<rlfine code hack>
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

/*
****************************************************************************
****
CREATE OR REPLACE PROCEDURE GET_SCALAR_VALUES(p_state IN VarChar2,
p_uniqZips OUT Number,
p_minZip OUT VarChar2,
p_maxZip OUT VarChar2) IS
CURSOR ABCD(c_state in VarChar2) IS
SELECT COUNT(*),min(zip_code),max(zip_code)FROM(
SELECT count(*),zip_code
FROM SMASTER.IMPORT_HOLDING
WHERE STATE=c_state
GROUP BY zip_code
);

BEGIN
OPEN ABCD(p_state);
FETCH ABCD INTO p_uniqZips,p_minZip,p_maxZip;
CLOSE ABCD;

END;
****************************************************************************
**** */

namespace ScalarCursorFetch{

//
// ---------------------------------------------
class Class1{
static void Main(string[] args){

using(DataManager dm = new DataManager("smaster","smaster")){
int uniqueCount;
string scanState = "NY";
string minZipcode;
string maxZipcode;
dm.FetchScalars(scanState,out uniqueCount, out minZipcode, out
maxZipcode);
Console.WriteLine("Scanning for state: {0}",scanState);
Console.WriteLine("Found {0} Unique zipcode",uniqueCount);
Console.WriteLine("Minimum zipcode in scan: {0}",minZipcode);
Console.WriteLine("Minimum zipcode in scan: {0}\n",maxZipcode);
}
}
}

//
// ---------------------------------------------
class DataManager:IDisposable{
private OracleConnection ocon;
private DataManager(){}
public void Dispose(){
Dispose(true);
}
void Dispose(bool explicitDispose){
ocon.Close();
if(explicitDispose) GC.SuppressFinalize(this);
}
~DataManager(){
Dispose(false);
}
public DataManager(string userid,string password){
ocon = new OracleConnection("user ID=SMASTER;Password=SMASTER;");
ocon.Open();
}
public bool FetchScalars(string p_state,out int p_uniqueCount,out string
p_minZipcode,out string p_maxZipcode){
string sqlCmd = "BEGIN GET_SCALAR_VALUES1,:2,:3,:4); END;";
p_uniqueCount = 0;
p_minZipcode = "";
p_maxZipcode = "";
using(OracleCommand ocmd = new OracleCommand(sqlCmd,ocon)){
OracleParameter oprm_state =
ocmd.Parameters.Add("state",OracleDbType.Varchar2,2,p_state,ParameterDirecti
on.Input);
OracleParameter oprm_ucnt =
ocmd.Parameters.Add("ucnt",OracleDbType.Int32,0,p_state,ParameterDirection.O
utput);
OracleParameter oprm_minz =
ocmd.Parameters.Add("minzip",OracleDbType.Varchar2,5,null,ParameterDirection
..Output);
OracleParameter oprm_maxz =
ocmd.Parameters.Add("maxzip",OracleDbType.Varchar2,5,null,ParameterDirection
..Output);
ocmd.ExecuteNonQuery();
p_uniqueCount = (int)oprm_ucnt.Value;
p_minZipcode = (string)(OracleString)oprm_minz.Value;
p_maxZipcode = (string)(OracleString)oprm_maxz.Value;
ocmd.Parameters.Clear();
}
return true;
}

}

}

</rlfine code hack>

 

[Cor Ligthert]

Hi Miha,

I looked to it, connected it to the Northwind database and than it did
nothing.
And than it did give 1 and 2, and than I missed the table and datanames.
However forget it, it is not that important.

I would just use the SqlDatareader for that much less complicated and can as
well with a stored procedure.

Thanks anyway,

Cor

 

[Earl]

Thanks for your help. I'm out of time to deal with changing that at the
moment (but will certainly do so asap). Thanks to both you and Miha ... the
2 output values is what I was looking for.

Earl

Here is a quick sample app that calls a stored procedure with one input
parameter and three output parameters - and there is no cursor involved on
the client side - i.e. not DataReader object will be constructed.

This sample is for Oracle (that's my domain these days) - and I see that
Miha Markic has provided a good example of same process using SqlServer.
here is the code - but first the disclaimer - there are no comments, there
is no exception handling, and the formatting of the code is a modification
from early Stroustrup C for the sake of screen/editor real estate
conservation. There is a lot to chew on here, but look especially at the
FetchScalars method of the DataManager class.


Here goes:

<rlfine code hack>
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

/*
****************************************************************************
****
CREATE OR REPLACE PROCEDURE GET_SCALAR_VALUES(p_state IN VarChar2,
p_uniqZips OUT Number,
p_minZip OUT VarChar2,
p_maxZip OUT VarChar2) IS
CURSOR ABCD(c_state in VarChar2) IS
SELECT COUNT(*),min(zip_code),max(zip_code)FROM(
SELECT count(*),zip_code
FROM SMASTER.IMPORT_HOLDING
WHERE STATE=c_state
GROUP BY zip_code
);

BEGIN
OPEN ABCD(p_state);
FETCH ABCD INTO p_uniqZips,p_minZip,p_maxZip;
CLOSE ABCD;

END;
****************************************************************************
**** */

namespace ScalarCursorFetch{

//
// ---------------------------------------------
class Class1{
static void Main(string[] args){

using(DataManager dm = new DataManager("smaster","smaster")){
int uniqueCount;
string scanState = "NY";
string minZipcode;
string maxZipcode;
dm.FetchScalars(scanState,out uniqueCount, out minZipcode, out
maxZipcode);
Console.WriteLine("Scanning for state: {0}",scanState);
Console.WriteLine("Found {0} Unique zipcode",uniqueCount);
Console.WriteLine("Minimum zipcode in scan: {0}",minZipcode);
Console.WriteLine("Minimum zipcode in scan: {0}\n",maxZipcode);
}
}
}

//
// ---------------------------------------------
class DataManager:IDisposable{
private OracleConnection ocon;
private DataManager(){}
public void Dispose(){
Dispose(true);
}
void Dispose(bool explicitDispose){
ocon.Close();
if(explicitDispose) GC.SuppressFinalize(this);
}
~DataManager(){
Dispose(false);
}
public DataManager(string userid,string password){
ocon = new OracleConnection("user ID=SMASTER;Password=SMASTER;");
ocon.Open();
}
public bool FetchScalars(string p_state,out int p_uniqueCount,out string
p_minZipcode,out string p_maxZipcode){
string sqlCmd = "BEGIN GET_SCALAR_VALUES1,:2,:3,:4); END;";
p_uniqueCount = 0;
p_minZipcode = "";
p_maxZipcode = "";
using(OracleCommand ocmd = new OracleCommand(sqlCmd,ocon)){
OracleParameter oprm_state =
ocmd.Parameters.Add("state",OracleDbType.Varchar2,2,p_state,ParameterDirecti
on.Input);
OracleParameter oprm_ucnt =
ocmd.Parameters.Add("ucnt",OracleDbType.Int32,0,p_state,ParameterDirection.O
utput);
OracleParameter oprm_minz =
ocmd.Parameters.Add("minzip",OracleDbType.Varchar2,5,null,ParameterDirection
.Output);
OracleParameter oprm_maxz =
ocmd.Parameters.Add("maxzip",OracleDbType.Varchar2,5,null,ParameterDirection
.Output);
ocmd.ExecuteNonQuery();
p_uniqueCount = (int)oprm_ucnt.Value;
p_minZipcode = (string)(OracleString)oprm_minz.Value;
p_maxZipcode = (string)(OracleString)oprm_maxz.Value;
ocmd.Parameters.Clear();
}
return true;
}

}

}

</rlfine code hack>

My question is also: How to get TWO output parameters?

Here is how this question came about: I needed a single value (SUM) from two
other tables, and I needed a date and amount value from the third table (the
one I referenced in my question). Because I needed set values for the first
two, and a single row for the third, a JOIN looked out of the question
(okay, maybe possible, but I didn't like what I was returning and didn't
want to fool around with expressions since I would rather do the math on the
server).

So for the two single summed values, I called ExecuteScalar on the command
object. Without using ADO classic, I'd like to do the same thing for the
single row with 2 values from one table (SQL Server). I'm now doing this
with a dataadapter and a datatable. Effective, but seems like overkill.




What

to

do