auk_ie
asked on
What is quicker Code or Procedure
Hello, Could somebody tell what (and why) is faster the code below or the stored procedure. I'm trying to figure out what code I should place in stored procedures and what to use C# for.
/// <summary>
/// Generates barcode from DB
/// </summary>
/// <param name="nSupplierID"></param >
/// <param name="nItemIndex">The 1 based index of item in the products table</param>
/// <returns></returns>
private string GenerateBarcode(int nSupplierID, int nItemIndex)
{
//First get the supplier code
string strSQL = "SELECT Supplier_Code FROM tblSuppliers WHERE Supplier_ID = " + nSupplierID.ToString();
this.m_sqlCommand.CommandT ext = strSQL;
string strSupplierCode = (string)m_sqlCommand.Execu teScalar() ;
//Build the barcode form i.e. Month+Year+SupplierCode
string strBarcodeForm = DateTime.Now.ToString("MMy y") + strSupplierCode;
//Find the MAX barcode of this format in the DB, eg. SELECT MAX(BarCode) FROM tblProducts WHERE BarCode LIKE '0804PAM%'
strSQL = "SELECT MAX(BarCode) FROM tblProducts WHERE BarCode LIKE '" + strBarcodeForm + "%'";
string strBarcode = "";
try
{
this.m_sqlCommand.CommandT ext = strSQL;
strBarcode = (string)m_sqlCommand.Execu teScalar() ;
}
catch(InvalidCastException ex)
{
//If no value for the perticular barcode format exist in DB an InvalidCastException occurs
Debug.WriteLine("Genetrate Barcode() - " + ex.ToString());
strBarcode = strBarcodeForm + "00001";
return strBarcode;
}
//If a barode of that format exists we must increment and return it
int strBarcodeIndex = Convert.ToInt32(strBarcode .Substring (strBarcod e.Length - 5, 5));
strBarcodeIndex += nItemIndex;
//2) Create Barcode
strBarcode = strBarcodeForm + strBarcodeIndex.ToString(" 00000");
return strBarcode;
}
Or the below stored procedure
ALTER PROCEDURE dbo.GenerateBarcode
(
@pSupplierID int,
@pBarcode VARCHAR(20) OUTPUT,
@pProductIndex int
)
AS
DECLARE @BarcodeDate DATETIME
SET @BarcodeDate = GETDATE()
DECLARE @BarcodeIndex NVARCHAR(10)
--Construct Date Part
SET @BarcodeIndex = STUFF('00', 2-len(MONTH(GETDATE()))+1, 2, MONTH(GETDATE())) + right(CONVERT(nvarchar, YEAR(GETDATE())), 2)
--Add Supplier Code
SELECT @BarcodeIndex = @BarcodeIndex + (SELECT Supplier_Code FROM tblSuppliers WHERE Supplier_ID = @pSupplierID)
--Get the highest value of this type of barcode
SELECT @pBarcode = MAX(BarCode) FROM tblProducts WHERE BarCode LIKE (@BarcodeIndex + '%')
--Increment Barcode
DECLARE @nNewIndex int
--Parse last 5 chars from Barcode (e.g. 0804JOM"000020")
SET @nNewIndex = CONVERT(int, SUBSTRING( @pBarcode, LEN(@pBarcode) - 4, 5)) + @pProductIndex
SET @pBarcode = @BarcodeIndex + RIGHT('00000' + CAST(@nNewIndex AS VARCHAR(5)), 5)
RETURN
/// <summary>
/// Generates barcode from DB
/// </summary>
/// <param name="nSupplierID"></param
/// <param name="nItemIndex">The 1 based index of item in the products table</param>
/// <returns></returns>
private string GenerateBarcode(int nSupplierID, int nItemIndex)
{
//First get the supplier code
string strSQL = "SELECT Supplier_Code FROM tblSuppliers WHERE Supplier_ID = " + nSupplierID.ToString();
this.m_sqlCommand.CommandT
string strSupplierCode = (string)m_sqlCommand.Execu
//Build the barcode form i.e. Month+Year+SupplierCode
string strBarcodeForm = DateTime.Now.ToString("MMy
//Find the MAX barcode of this format in the DB, eg. SELECT MAX(BarCode) FROM tblProducts WHERE BarCode LIKE '0804PAM%'
strSQL = "SELECT MAX(BarCode) FROM tblProducts WHERE BarCode LIKE '" + strBarcodeForm + "%'";
string strBarcode = "";
try
{
this.m_sqlCommand.CommandT
strBarcode = (string)m_sqlCommand.Execu
}
catch(InvalidCastException
{
//If no value for the perticular barcode format exist in DB an InvalidCastException occurs
Debug.WriteLine("Genetrate
strBarcode = strBarcodeForm + "00001";
return strBarcode;
}
//If a barode of that format exists we must increment and return it
int strBarcodeIndex = Convert.ToInt32(strBarcode
strBarcodeIndex += nItemIndex;
//2) Create Barcode
strBarcode = strBarcodeForm + strBarcodeIndex.ToString("
return strBarcode;
}
Or the below stored procedure
ALTER PROCEDURE dbo.GenerateBarcode
(
@pSupplierID int,
@pBarcode VARCHAR(20) OUTPUT,
@pProductIndex int
)
AS
DECLARE @BarcodeDate DATETIME
SET @BarcodeDate = GETDATE()
DECLARE @BarcodeIndex NVARCHAR(10)
--Construct Date Part
SET @BarcodeIndex = STUFF('00', 2-len(MONTH(GETDATE()))+1,
--Add Supplier Code
SELECT @BarcodeIndex = @BarcodeIndex + (SELECT Supplier_Code FROM tblSuppliers WHERE Supplier_ID = @pSupplierID)
--Get the highest value of this type of barcode
SELECT @pBarcode = MAX(BarCode) FROM tblProducts WHERE BarCode LIKE (@BarcodeIndex + '%')
--Increment Barcode
DECLARE @nNewIndex int
--Parse last 5 chars from Barcode (e.g. 0804JOM"000020")
SET @nNewIndex = CONVERT(int, SUBSTRING( @pBarcode, LEN(@pBarcode) - 4, 5)) + @pProductIndex
SET @pBarcode = @BarcodeIndex + RIGHT('00000' + CAST(@nNewIndex AS VARCHAR(5)), 5)
RETURN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, its clear that stored procedure live closer to the DB layer and will be faster, but what about the code in the strored procedure:
--Increment Barcode
DECLARE @nNewIndex int
--Parse last 5 chars from Barcode (e.g. 0804JOM"000020")
SET @nNewIndex = CONVERT(int, SUBSTRING( @pBarcode, LEN(@pBarcode) - 4, 5)) + @pProductIndex
SET @pBarcode = @BarcodeIndex + RIGHT('00000' + CAST(@nNewIndex AS VARCHAR(5)), 5)
Should I do basic formatting like this in CSharp and leave the dataaccess bit to a stored procedure. Is it correct for me to persume that stroed procedures are interpreted and slower because of it. In that case what interprets and executes the stored procedure code?
--Increment Barcode
DECLARE @nNewIndex int
--Parse last 5 chars from Barcode (e.g. 0804JOM"000020")
SET @nNewIndex = CONVERT(int, SUBSTRING( @pBarcode, LEN(@pBarcode) - 4, 5)) + @pProductIndex
SET @pBarcode = @BarcodeIndex + RIGHT('00000' + CAST(@nNewIndex AS VARCHAR(5)), 5)
Should I do basic formatting like this in CSharp and leave the dataaccess bit to a stored procedure. Is it correct for me to persume that stroed procedures are interpreted and slower because of it. In that case what interprets and executes the stored procedure code?
the big issue is not necesarily which is faster but which is better encapsulation ... i.e. do you have other places who need the data formatted the same way ? (lets say a report or a 3rd party who queries ?) if yes then putting it in the db is a good idea, so that you may maintain consistancy throughout the different places viewing the data.
ASKER