April 30, 2002 at 1:02 pm
I work with a group of middle tier/GUI developers. One of them was trying to call a stored proc using ADO. So he built ADO objects that have @params for each of the parameter that the stored proc expects. Now when we look at the SQL Profiler that we had turned 'ON', what the SQL Server database is getting is just a string with the name of the sproc and all the passed in parameters, from the ADO connection.
What they want to know is 'Is the ADO object just really building a string for them, when it hits the Datbase or is that what the profiler shows but maybe underneath ADO is actually building some binary level code which makes things faster at the database level. Does the profiler always reflect the actual transformation that has been done by ADO or is it just that the profiler puts it in a way that we can understand what has happened.
If the ADO object has just built the string, then the developer would much rather build the string himself as he knows the params that need to be passed and he can do it faster than having tha ADO object do it.
Please tell us what you can about this.
Thanks.
April 30, 2002 at 1:23 pm
Good question. My hunch is that what you see is what you get. The advantage to declaring the parameters is you can be type safe on the client and it handles issues like single quotes for you. Setting up the params on the client does take a little more time but with a tool is pretty easily done. I have to admit to occasionally just doing connection.execute for simple procs where I'm passing in a int or something. Much the same holds true for using recordsets to make updates (plus it handles collisions, etc).
Andy
April 30, 2002 at 2:41 pm
We will have this sproc with 300 parameters and we don't really need ADO's ability to do type matching for us, so I guess sending the string should make things a little bit faster. Although on the flip side I would think that error detection could get a little more difficult if a data mismatch were to actually happen.
Looking at the profiler I had felt its 'What you see is what you get' thanks for confirming that.
April 30, 2002 at 4:57 pm
Where do you think you will gain performance? I agree that type checking isnt always a must have. Is it run time performance or time it takes to set it up the first time?
Andy
April 30, 2002 at 6:16 pm
The proc will be called all day long with different values for the 300 odd params and actually there will be several sprocs later on with many many params. The developers are hoping to see some(very little maybe) gains at run time when they hit the database.
April 30, 2002 at 6:48 pm
Dont see how you're going to gain much, any gain would be in client side prep if anything. Be interesting to see.
Andy
April 30, 2002 at 7:40 pm
Yes, the gain if any is expected on the client side. Will let you know what we find when our developers run benchmarks.
May 1, 2002 at 5:14 am
To answer your base question, yes. ADO is just building the string which your developer can do himself and I have done on serveral occasions myself. However there are some client side things that are done with SP to augment there impact to the client and it is suggested you do the ADO method liek you are doing now. Look at http://www.sql-server-performance.com as there are some statements about this. I do remember the major thing about the .Parameters.Append method is for procedures with Output parameters. You cannot just supply the string and get the return from an output parameter. The most efficient way to retrieve a scalar value from a database is via a stored procedure output parameter. Example would be a record we just added with the Stored Procedure and we want to get back the Identity value of the newly inserted item.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply