Performance Issues w/INSERT, UPDATE, DELETE -- sql server (microsoft)

hkvats_1999 NO[at]SPAM yahoo.com
8/2/2004 5:32:58 AM

Hi Ben,

Following Text has been referred from SQL Books Online....

1. Transact-SQL statements compiled into stored procedures can save a
significant amount of processing at execution time.

2. Another advantage of stored procedures is that client execution
requests use the network more efficiently than equivalent Transact-SQL
statements sent to the server. For example, suppose an application
needs to insert a large binary value into an image data column. To
send the data in an INSERT statement, the application must convert the
binary value to a character string (doubling its size), and then send
it to the server. The server then converts the value back into a
binary format for storage in the image column. In contrast, the
application can create a stored procedure of the form:

CREATE PROCEDURE P(@p1 image) AS INSERT T VALUES (@p1)

When the client application requests an execution of procedure P, the
image parameter value will stay in binary format all the way to the
server, thereby saving processing time and network traffic.

Regards
Hari Sharma

[quoted text, click to view]

mkerrigan NO[at]SPAM ktoys.com
8/2/2004 7:34:48 AM

I understand that. But in the case of a simple statement like "DELETE
FROM FOO" does it make a real performance difference? Likewise, with
a simple INSERT statement?

[quoted text, click to view]

Ben Lucas
8/2/2004 10:50:24 AM

In the case of simple statements, those statements still have to be
interpreted and compiled, whereas Stored Procedures do not. The performance
advantage may be very small, but the Stored Procedure should still perform
better than the statement.

To do a test, I wrote a program that called the "Ten Most Expensive
Products" Stored Procedure in the Northwind database and iterated 100 times
on that. It also called the equivalent in T-SQL and iterated 100 times on
that. The results:

T-SQL: 1552.1855 ms
SP: 150.2115 ms.

There is definitely a performance advantage to using SPs.

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

[quoted text, click to view]

mkerrigan NO[at]SPAM ktoys.com
8/3/2004 12:22:17 PM

I did the same thing (in ASP) but got these results:

T-SQL: 301 ms
SP: 390 ms

Here is the code I used:

<script language="javascript" runat="server">

function GetTimeInMillisec()
{
var Now = new Date()
var TimeStamp = Now.getTime()

return TimeStamp
}

</script>

<%
myDSN="DSN=Northwind;uid=sa;pwd="
set conn=server.createobject("adodb.connection")
conn.open myDSN

'statement for stored proc
mySQL1 = "EXEC ""Ten Most Expensive Products"""

'statement for inline SQL
mySQL2 = "SET ROWCOUNT 10 " _
& "SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice " _
& "FROM Products " _
& "ORDER BY Products.UnitPrice DESC "

sp_starttime = GetTimeInMillisec()
for i = 1 to 100
Response.Write i
set rs=conn.execute(mySQL1)
next
sp_endtime = GetTimeInMillisec()

il_starttime = GetTimeInMillisec()
for i = 1 to 100
Response.Write i
set rs=conn.execute(mySQL2)
next
il_endtime = GetTimeInMillisec()

sp_time = sp_endtime - sp_starttime
il_time = il_endtime - il_starttime

Response.Write "sp_time: " & sp_time & CHR(10) & CHR(13)
Response.Write "il_time: " & il_time & CHR(10) & CHR(13)

conn.Close
set conn=nothing
set rs=nothing
%>

[quoted text, click to view]

Ben Lucas
8/5/2004 8:29:12 AM

I did notice that I had made a mistake in my code. I had not performed the
"SET ROWCOUNT 10" command which meant my T-SQL was sending back more records
than the SP. I have modified the code so that the T-SQL does perform the
"SET ROWCOUNT 10" and received the following results:

T-SQL: 490.686 ms
SP: 140.196 ms

I would like to point out that your code is really only comparing T-SQL with
T-SQL that calls a stored procedure. Your SQL query "EXEC ""Ten Most
Expensive Products""" is still T-SQL and must be interpreted by the engine
before it can be run. Once interpreted, it then executes the SP which is
already compiled.

Using ADO, a more accurate test would be to use the Command object and set
its CommandType to adCmdStoreProc. The code would look like this:

set cmd = server.createobject("adodb.Command")
set cmd.ActiveConnection = conn
cmd.CommandText = "Ten Most Expensive Products"
cmd.CommandType = adCmdStoreProc
set rs = cmd.Execute

I ran my test in a console app in C#, and I'm including the code below:

using System;
using System.Data;
using System.Data.SqlClient;

namespace testSpeed2
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
SqlConnection conn = new
SqlConnection("server=dev1;database=Northwind;integrated security=sspi");
Console.WriteLine("Starting T-SQL Profile....");
DateTime dt1 = DateTime.Now;
for(int i=0; i < 100; i++)
{
conn = new SqlConnection("server=dev1;database=Northwind;integrated
security=sspi");
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand("SET ROWCOUNT 10\nSELECT
Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM
Products ORDER BY Products.UnitPrice DESC", conn);
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
}
}
}
DateTime dt2 = DateTime.Now;
TimeSpan ts = dt2.Subtract(dt1);
Console.WriteLine( ts.TotalMilliseconds.ToString() + " ms");

Console.WriteLine("Starting SP Profile....");
dt1 = DateTime.Now;
for(int i=0; i < 100; i++)
{
conn = new SqlConnection("server=dev1;database=Northwind;integrated
security=sspi");
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
}
}
}
dt2 = DateTime.Now;
ts = dt2.Subtract(dt1);
Console.WriteLine( ts.TotalMilliseconds.ToString() + " ms");
Console.Read();
}
}
}

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

[quoted text, click to view]

leonidbogdanov NO[at]SPAM decadesoftware.com
8/6/2004 9:46:51 AM

It was true for 6.5, but not for SQL Server 7.0-2000, see Books Online
for details (SQL Server Architecture\Database Architecture\Logical
Datbase Components\SQL Stored Procedures):

"...A stored procedure is compiled at execution time, like any other
Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain
execution plans for all SQL statements in the procedure cache, not
just stored procedure execution plans..."

Leonid.

[quoted text, click to view]

Ben Lucas
8/6/2004 3:48:23 PM

Thank you for the clarification regarding stored procedures and compilation
time. I was wondering what you make of the Books Online section "Optimizing
Database Performance\Application Design\Effects of Stored Procedures on
Application Performance"

"All well-designed Microsoft® SQL ServerT 2000 applications should use
stored procedures. This is true whether or not the business logic of the
application is written into stored procedures. Even standard Transact-SQL
statements with no business logic component gain a performance advantage
when packaged as stored procedures with parameters. Transact-SQL statements
compiled into stored procedures can save a significant amount of processing
at execution time. For more information, see Stored Procedures."

Likewise, the Stored Procedures section that it refers to suggests that
stored procedures are optimized once while T-SQL has to be optimized
repeatedly.

Again, thanks for your response. It seems like there are apparently
contradictory statements in Books Online (unless I'm missing something) and
so I would like to get a better understanding.

--
Ben Lucas
Lead Developer
Solien Technology, Inc.
www.solien.com

[quoted text, click to view]

mkerrigan NO[at]SPAM ktoys.com
8/9/2004 6:29:08 AM

[quoted text, click to view]

Thanks, Ben. When I changed my code to use adCmdStoredProc I got
better results - the SP was faster in this case. I had no idea this

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (microsoft)

sql server (microsoft) : Performance Issues w/INSERT, UPDATE, DELETE