IS this true?

We've been experiencing some performance degradation recently. We are a
small company partnered with a MUCH larger one. Someone from the other
company said they had experienced some of what we are seeing and recommended
that stored procedures which only return one row of data should use output
parameters instead of "selecting" the data as a resultset.
Is this really that much faster?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> We've been experiencing some performance degradation recently. We are a
> small company partnered with a MUCH larger one. Someone from the other
> company said they had experienced some of what we are seeing and
> recommended that stored procedures which only return one row of data
> should use output parameters instead of "selecting" the data as a
> resultset.
> Is this really that much faster?
It is a lot faster, however. Unless you're running several hundred of these
a second, you probably won't notice any difference. So like all performance
analysys, you need to find what resource is limited in your system and
identify the biggest consumers of that resource.
David|||Thanks for the reply...yes during our busiest hours we are definitely
running hundreds of these a second.....if not thousands. I know this is
not the main performance bottleneck in our system at this point, but if
comparatively speaking it is a big difference then it would probably be
worth it in our case.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> It is a lot faster, however. Unless you're running several hundred of
> these a second, you probably won't notice any difference. So like all
> performance analysys, you need to find what resource is limited in your
> system and identify the biggest consumers of that resource.
> David
>|||I agree with David in that it is unlikely making that change will
dramatically speed up your app as there are usually other things that can be
done first. Make sure you owner qualify all objects, especially the sp
calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
they are called as an RPC and not a batch.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply...yes during our busiest hours we are definitely
> running hundreds of these a second.....if not thousands. I know this is
> not the main performance bottleneck in our system at this point, but if
> comparatively speaking it is a big difference then it would probably be
> worth it in our case.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Not sure what you mean as "an RPC and not a batch"? Yes we do
exhaustively "FULLY" qualify all objects from db.owner.object. I'm also
going thru all old webservices and making sure EVERYTHING is using
parameterized queries and not just stringing together exec statements and
parameters.
I think there are a few of the latter remaining but I'm stomping them
out....
I just ensured the NOCOUNT is indeed in every sproc in the system. That
made a difference in tonights benchmarks. Also setting isolation level
appropriately where needed. I think we can get further though.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucWcHE9aGHA.4612@.TK2MSFTNGP03.phx.gbl...
>I agree with David in that it is unlikely making that change will
>dramatically speed up your app as there are usually other things that can
>be done first. Make sure you owner qualify all objects, especially the sp
>calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
>they are called as an RPC and not a batch.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
>|||Also wondered...if there are stored procs being called from a webservice
where it is just strung together i.e.
string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
parm2.ToString()
versus using a parameterized version, is it true that qualifying the object
above really doesn't help? I'm told that examples like the one above won't
be cached...that it won't cache unless it is parameterized...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucWcHE9aGHA.4612@.TK2MSFTNGP03.phx.gbl...
>I agree with David in that it is unlikely making that change will
>dramatically speed up your app as there are usually other things that can
>be done first. Make sure you owner qualify all objects, especially the sp
>calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
>they are called as an RPC and not a batch.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
> Also wondered...if there are stored procs being called from a webservice
> where it is just strung together i.e.
> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
> parm2.ToString()
> versus using a parameterized version, is it true that qualifying the
> object above really doesn't help? I'm told that examples like the one
> above won't be cached...that it won't cache unless it is parameterized...
>
In that case you would still reuse the plan for the proc, it's just the
"exec ..." batch that needs to be parsed, and it has a trivial plan.
But none of this is likely to help. This is just general best practice
stuff. Running around your application fixing up little stuff like this is
absolutely the wrong way to go about analyzing and improving the performance
of an application.
You must discover what your acutal problems are and address those.
David|||I have to agree and disagree with David on this<g>. First I agree that you
should determine where the bottlenecks really are and address them vs.
guessing or making random changes that may help. You can start by getting
perfmon and trace data and seeing what is out of whack. I also agree that
sending a batch (your string from the webservice) will result in two plans.
One for the batch and one for the execution of the sp. The plan for the sp
will get reused. But in my experience the batch itself does not always
result in a trivial plan that does not get cached. If it does get cached
but not reused you can have a lot of performance issues associated with it
when called a lot. You can end up with 10's or hundreds of thousands of very
similar plans that have the same hash bucket in the cache. This can result
in increased durations of each new call to search for the correct plan. You
can see this easily if you select from syscacheobjects and take a look at
the SQL and usecounts columns. Lack of owner qualifying can also be a big
hit on performance when you call a sp hundreds or thousands of times per
second due to the serialization that will occur in the cache searching
process. If yours are all owner qualified you should be all set in that
department. But one thing to note. You do not need to DB qualify the objects
if they are in the same db as where you are executing them from. As a matter
of fact it actually will be a tiny hit (most of the time not enough to
detect or worry about) in performance. The only time you need to specify
the db is when it is actually in another db.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
> In that case you would still reuse the plan for the proc, it's just the
> "exec ..." batch that needs to be parsed, and it has a trivial plan.
> But none of this is likely to help. This is just general best practice
> stuff. Running around your application fixing up little stuff like this
> is absolutely the wrong way to go about analyzing and improving the
> performance of an application.
> You must discover what your acutal problems are and address those.
> David
>|||We were closely following BOL when we did the FULL qualification ... I quote
from SQL2005 BOL
SQL Server 2005 has an efficient algorithm to find any existing execution
plans for any specific SQL statement. In most systems, the minimal resources
that are used by this scan are less than the resources that are saved by
being able to reuse existing plans instead of compiling every SQL statement.
The algorithms to match new SQL statements to existing, unused execution
plans in the cache require that all object references be fully qualified.
For example, the first of these SELECT statements is not matched with an
existing plan, and the second is matched:
SELECT * FROM Person.Contact
SELECT * FROM AdventureWorks.Person.Contact
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>I have to agree and disagree with David on this<g>. First I agree that you
>should determine where the bottlenecks really are and address them vs.
>guessing or making random changes that may help. You can start by getting
>perfmon and trace data and seeing what is out of whack. I also agree that
>sending a batch (your string from the webservice) will result in two plans.
>One for the batch and one for the execution of the sp. The plan for the sp
>will get reused. But in my experience the batch itself does not always
>result in a trivial plan that does not get cached. If it does get cached
>but not reused you can have a lot of performance issues associated with it
>when called a lot. You can end up with 10's or hundreds of thousands of
>very similar plans that have the same hash bucket in the cache. This can
>result in increased durations of each new call to search for the correct
>plan. You can see this easily if you select from syscacheobjects and take a
>look at the SQL and usecounts columns. Lack of owner qualifying can also be
>a big hit on performance when you call a sp hundreds or thousands of times
>per second due to the serialization that will occur in the cache searching
>process. If yours are all owner qualified you should be all set in that
>department. But one thing to note. You do not need to DB qualify the
>objects if they are in the same db as where you are executing them from. As
>a matter of fact it actually will be a tiny hit (most of the time not
>enough to detect or worry about) in performance. The only time you need to
>specify the db is when it is actually in another db.
> --
> Andrew J. Kelly SQL MVP
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
>|||Also, there is alot of cross db queries going on here. This makes it much
simpler as all webservices/apps etc...can use the same connection string as
the initial catalog makes no difference.
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:Oa1q8vebGHA.1208@.TK2MSFTNGP02.phx.gbl...
> We were closely following BOL when we did the FULL qualification ... I
> quote from SQL2005 BOL
> SQL Server 2005 has an efficient algorithm to find any existing execution
> plans for any specific SQL statement. In most systems, the minimal
> resources that are used by this scan are less than the resources that are
> saved by being able to reuse existing plans instead of compiling every SQL
> statement.
> The algorithms to match new SQL statements to existing, unused execution
> plans in the cache require that all object references be fully qualified.
> For example, the first of these SELECT statements is not matched with an
> existing plan, and the second is matched:
>
> SELECT * FROM Person.Contact
> SELECT * FROM AdventureWorks.Person.Contact
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>