lost of performance

  • Thread starter Thread starter petr.formak
  • Start date Start date
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
 
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.
__________________________________
 
No index was dropped, all indexes are the same. Execution plan is the
same on both sql servers.
Any other idea?
 
No index was dropped, all indexes are the same. Execution plan is the
same on both sql servers.
Any other idea?
 
Back
Top