lost of performance

P

petr.formak

I'm using .NET framework 1.1 SP1 and SQL Server 2000. After installing
service pack 4 on sql server my data conversion application lost a lot
of performance.
Now it running 8 times slower than with SP3a on SQL server.

problematic part of code for executing simple SQL SELECT query:

public long FindWorkPlaceKey(long lClientID, long lObjectID, long
lDeptID)
{

..
..
..
sqlCommand.Parameters.Add(new SqlParameter("@CLIENT_ID", lClientID));
sqlCommand.Parameters.Add(new SqlParameter("@OBJECT_ID", lObjectID));
Object y = sqlCommand.ExecuteScalar();
..
..
}

This method is executed 5000 times.
On sql server with SP3a it last 8 seconds.
On sql server with SP4 it last 65 seconds. 8 times more!!!


With code below

sqlCommand.Parameters.Add("@CLIENT_ID", SqlDbType.Int).Value =
lClientID;
sqlCommand.Parameters.Add("@OBJECT_ID", SqlDbType.Int).Value =
lObjectID;
Object y = sqlCommand.ExecuteScalar();

code is executed 5000 times.
On sql server with SP3a it last 8 seconds.
On sql server with SP4 it last 11 seconds. 40% more!!!

Does anybody have the same problem?
What causes this problem?
Solution?

thanks a lot
Petr
 
W

William \(Bill\) Vaughn

Start the profiler and see what's getting executed. Take that code and use
the Query Analyzer to see how the query is being executed. QA can also make
suggestions that would improve performance. I suspect that somehow an index
was dropped.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
M

medabeda

No index was dropped, all indexes are the same. Execution plan is the
same on both sql servers.
Any other idea?
 
P

petr.formak

No index was dropped, all indexes are the same. Execution plan is the
same on both sql servers.
Any other idea?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sproc performance over vpon 12
SQL Server Error 8179: Could not find prepared statement with handle x 2
SqlClient Data Adapter Fill Method 5
LINQ + .Contains(s) + sp_executesql = big performance problem 1
some advice needed - rebuild of scsi server 5
ASP.NET Application Gets Frequent SQL Timeout Exceptions 14
Implication of manually chaning time on domain controller and Exch 2
Passing output parameters to SQL Server stored procedures through an SqlCommand object's Parameters 8

Top