Re: multiple times execution of procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Daniel P. (danutzp1_at_hotmail.comU)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 10:48:42 -0500

How do you know for sure that the first one takes 50 seconds? If Query
Analyzer told you that then it might not be accurate because it can't give
you less than one second.

You need to build your own timer to measure the time taken to execute one or
N sprocs.

Anyway just measuring how long it takes to execute one sproc is not the best
way to come up with conclusions about performance if it is a matter of
seconds. You can't tell who else did something on the same time on SQL
server or what happened with the network link between SQL server and the
client.

Try to use SQL Profiler to see the details.

I have built my own tool in C++ that spawns a required number of threads (N)
concurrently and it executes that in an iteration M times. I measure with
that performance by executing the same test several times and calculating
the average.

Also the tool can help me detect deadlocks and other bottlenecks.

"mahesh" <cmahesh23@yahoo.com> wrote in message
news:4ed401c473e3$643dd200$a301280a@phx.gbl...
> hi,
> When i execute a procedure p1(with some parameters....)
> i.e exec p1 .....
> it inserts 50 records on a table and takes 1 second (50
> rps)
>
> but if i repeat this procedure 80 times to insert 4000
> records (in a batch)
> exec exec p1 .....
> exec p1 .....
> exec p1 .....
> exec p1 .....
> exec p1 .....
> exec p1 .....
> ....
> ....
>
> it takes 283 sec i.e 14.13 rps
> why there is differnce in the performance ?
>
> Thanks and regards
> Mahesh

Relevant Pages

  • Re: T-SQL is not quite there yet
    ... "RECOMPILE ... Instructs the SQL Server Database Engine to discard the plan generated ... for the query after it executes, forcing the query optimizer to recompile ...
    (microsoft.public.sqlserver.programming)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Tuning query?
    ... the memory of the SQL Server, so you are seeing the delay caused by the disk ... Once it's in memory, it executes that much fast as it doesn't need to refer ... In the query Steve has done, the function calculation is carried out once, ...
    (microsoft.public.sqlserver.programming)
  • Immer noch Probleme mit dem Debugger
    ... Client (Query Analyzer) und SQL Server nicht auf der gleichen Maschine ... Gezielt gesetzte Haltepunkte waren dem Debugger ...
    (microsoft.public.de.sqlserver)
  • Re: long SQL lines for osql
    ... It works great in Query ... Analyzer, but when run through osql it takes errors on lines that are ... One thing to keep in mind, is by default OSQL runs with QUOTED_IDENTIFIER ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)