Solved

What is quicker Code or Procedure

Posted on 2004-09-06
6
210 Views
Last Modified: 2012-06-27
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.CommandText = strSQL;
string strSupplierCode = (string)m_sqlCommand.ExecuteScalar();
                                    
//Build the barcode form i.e. Month+Year+SupplierCode
string strBarcodeForm = DateTime.Now.ToString("MMyy") +  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.CommandText = strSQL;
strBarcode = (string)m_sqlCommand.ExecuteScalar();
}
catch(InvalidCastException ex)
{
//If no value for the perticular barcode format exist in DB an InvalidCastException occurs
Debug.WriteLine("GenetrateBarcode() - " + 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(strBarcode.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
0
Comment
Question by:auk_ie
6 Comments
 

Author Comment

by:auk_ie
ID: 11993862
PS. both the above do pretty mush the same except stored procedure is missing an if block
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 100 total points
ID: 11994046
they will be pretty much the same speed ... within a few ms of each other the sproc will be a little bit faster because your C# code is making 2 round trips to the database but you could do it in a single trip and they would be nearly identical.
0
 
LVL 7

Assisted Solution

by:jj819430
jj819430 earned 100 total points
ID: 11994223
This is a matter of situation. If they are both on the different machines then you could get an advantage with the web server, or app server being a big workhorse. Basically it depends on how the systems are setup. If you are expecting a lot of app trafic and only a couple hits to the database then put the stored proc, if your app server is rarely used but this database is heavily used then use the c# function.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Assisted Solution

by:Mohammed Nasman
Mohammed Nasman earned 50 total points
ID: 11995130
Hello auk_ie

  Using Stored Procedure is faster than using sent query, also using Query with parameters is faster than passing variable with sql statmenent for example in the above query you used
.. Supplier_ID = " + nSupplierID.ToString();
if you change the sql statment as follow:
Supplier_ID = @SupID" ;

then pass the paramemter to the command

SqlParameter p = new SqlParameter("@EmpId",SqlType.VarChar,10);
p.Direction = ParameterDirection.Input;
p.Value = nSupplierID.ToString();
cmd.Parameters.Add(p);

This way sql server will excute it faster than the one you wrote

HTH

Regards,
Mohammed
0
 

Author Comment

by:auk_ie
ID: 11998285
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?
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 12000462
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
install app on other machine 13 32
C# TextBox 11 30
Error on link 14 39
Code works but it's slow 24 44
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now