Most Efficiency with Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Most Efficiency with Parameters

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-16 : 19:44:21

Hey all. If anyone has read any of my last posts on other sections, you'll know that i have been working on converting an ACCESS db to SQL. Everything is going great so far except for a few bumps, and I'd like to get anyone and everyone's opinion on this.

I currently code in ASP classic. The current code design will not work if I convert this site to ASP.NET, but I will later, so please keep this in mind when answering these questions.

I have been looking around for way of appending parameters to a sproc from an MSSQL Server. Here are some examples:

1- (this is what I used with access):


	Set cmd = Server.CreateObject("ADODB.Command")
	cmd.ActiveConnection = dbc
	cmd.CommandText = "rolyrolls.proc_AddMakeType"
	cmd.CommandType = adCmdStoredProc
	
	Set p = cmd.CreateParameter("usrMakeType", adChar, adParamInput, Len(Form_MakeType), Form_MakeType)
	cmd.Parameters.Append(p)
	
	Set p = cmd.CreateParameter("usrLastModifiedBy", adInteger, adParamInput, Len(Session("ID")), Session("ID"))
	cmd.Parameters.Append(p)
	
	Set rs = Server.CreateObject("ADODB.Recordset")
	Set rs = cmd.Execute

I changed the parameter name by changing the 'usr' to '@' since I heard that ASP.NET does use/read this, which would be something like:


	Set p = cmd.CreateParameter("@MakeType", adChar, adParamInput, Len(Form_MakeType), Form_MakeType)
	cmd.Parameters.Append(p)

2- (found these on the internet/forums and the like)

a: Here I noticed they have averything on one line and do not use SET (which I assume will not create an extra object, right?) put the size to 255, being the size of the data-type specified on the server. (more on this later)


	cmd.Parameters.Append .CreateParameter("@MakeType", adChar, adParamInput, 255, Form_MakeType)

b: I got this from Microsoft. I have never used any output parameters, since I'm not sure it can be done with Access, but will love to use them on SQL. What i don't understand tho, is the 'cmd.CommandText' section with the question marks.
[url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q164/4/85.asp&NoWebContent=1[/url]


   Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "data source name", "userid", "password"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   ' Define the stored procedure's inputs and outputs
   ' Question marks act as placeholders for each parameter for the
   ' stored procedure
   cmd.CommandText = "{?=call sp_test(?)}"
   ' specify parameter info 1 by 1 in the order of the question marks
   ' specified when we defined the stored procedure
   cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
   adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
     adParamInput)
   cmd.Parameters("Param1") = 33
   cmd.Execute

c: Here's one i found where I assume you give the value after creating the parameter.


 	set DBCON = SetupSQL(Application("DBCON"))
 	Set cmd = Server.CreateObject("ADODB.command")
 	Set cmd.ActiveConnection = DBCON
 	cmd.CommandText = "procIOnlineAuthPwd"
 	cmd.CommandType = 4 'adCmdStoredProc
 	cmd.Parameters.Refresh 
 	' Instead of "cmd.Parameters.Refresh" we can use the followings:
 	''cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)    
 	'cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 50, "e@ti3.com") 
 	'cmd.Parameters.Append cmd.CreateParameter("backoffice", adInteger, adParamInput)
 	'cmd.Parameters.Append cmd.CreateParameter("tablename", adVarChar, adParamInput, 20, "tbOnlineAuth_2090")  
 	'cmd.Parameters.Append cmd.CreateParameter("password", adInteger, adParamOutput)   
 	cmd.Parameters("@email") = Email
 	cmd.Parameters("@backoffice")=1
 	cmd.Parameters("@branchID")=branchID
 	cmd.Parameters("@tablename")="tbOnlineAuth"
 	cmd.Parameters("@AuthStartDate")=startdate
 	cmd.Parameters("@AuthStopDate")=enddate
 	cmd.Parameters("@passwordIn")=0
 	cmd.Parameters("@passwordOut")=0
 	'Response.Write vbnewline + "Inserting via stored proc."
 	cmd.Execute
	CheckErr appid, Err, CStr(cmd.CommandText)
 	pwd=cmd.Parameters("@passwordOut")
	Set cmd = Nothing

d: (last one) I found this one to have two outputs, a return value 9maybe a recordset) along with a value for another parameter.


    set cmd = server.createobject("ADODB.Command")
    cmd.ActiveConnection = objConn
    cmd.CommandType = adCmdStoredProc

    cmd.CommandText = "sp_po_Add"
    cmd.Parameters.Append cmd.CreateParameter("@Return", adInteger, _
        adParamReturnValue)
    cmd.Parameters.Append cmd.CreateParameter("@v_ID", adInteger, _
        adParamInput, , intVendorID)
    <... other input params ...>
    cmd.Parameters.Append cmd.CreateParameter("@OutputRetVal", adInteger, _
        adParamOutput, , 0)

    cmd.Execute()

    intReturnValue = cmdPurchase("@Return")
    intOutput = cmdPurchase("@OutputRetVal")

    set cmd = nothing

So...does anyone out there have anything different? Any preferences? Why do you favor it (for better performance, etc)? What can/will work with both ASP classic and ASP.Net?

I thank everyone in advance for their participation.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-16 : 19:46:38

Deleted...was editing above.

- RoLY roLLs

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-16 : 20:00:09

Deleted because I didn't read the question properly.

Michael

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-16 : 20:05:30

I don't see why the second one wounldn't work besides the questionable Command text.

If you are looking for something a bit simplier, try this:



cmd.Parameters.Append(cmd.CreateParameter("@MakeType", adChar, adParamInput, Len(Form_MakeType), Form_MakeType))

Also, don't forget to include declarations for your ADO Constants (adChar, adParamInput, etc).

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-16 : 20:40:14

Sorry Michael, I was editting, since I accidentally hit TAb then ENTER and it submitted the post before I finished. I'm more interested not in what's simpler, but rather what's more efficient, and for what reasons is it efficient? I know everything has a good and bad side, so as why I'm here to ask. Thanks.

- RoLY roLLs

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-16 : 20:49:49

Are you looking for an ASP example or ASP.net?
They are not 100% compatibile for this type of stuff. (ADO vs ADO.net).

As far as performance goes, what I look for is this:
1. Use the least amount of objects
2. Make the code as maintainable as possible.

Personally, I like 2a or 2d. I don't like to use return values, but OUTPUT params are really great for perfomance for both ADO and ADO.net. If you only need one (or just a few) values back, OUTPUT params are highly efficient for this. If you are looking for real performance, then move your database code to a COM object (or COM+ object) for ASP, or move to ASP.Net and ADO.net. ADO.Net is quite a bit faster than ADO 2.X. I've read some things that say ADO.net is as much as 20% faster. All I know is my pages load super fast :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-16 : 21:00:05

Ok sounds good. I'll look around for diferences between ADO and ADO.NET so I know what questions to ask if I have any.

I agree that the less objects created the better. Was just a habit from how I learned when I started.

Also, what do you mean by making it as maintainable as possible. I mean, I know the meaning of it, but give me some examples of what you'd do.

- RoLY roLLs

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-17 : 01:31:00

Maintainability....

Well I like to follow the Programmer's Motto:
K.I.S.S
Keep it simple stupid!

Look at example c and d above. They both basically do the same thing.
Which one is easier to read, understand, and change? I'd say D and I'd say that's what makes it maintainable in my book. I'd probably want to see some comments in there too, but I'm guessing those got left out for simplicity's sake.

Just remember, K.I.S.S and you'll be fine.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-17 : 11:39:02

Sounds good. Thanks. Anyone else got any input on this?

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources