.NET SqlClient Dataprovider - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

.NET SqlClient Dataprovider

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-02-10 : 11:08:56

So we put our first .NET application into production (ok this is an issue due to the fact we have no QA at all, not by choice) ... and when I run some queries, it ends up calling the stored procedure like 100+ times when there is only one ExecuteReader statement ... any clues to KB articles or anything, I have yet to find any ... there are no loops around the ExecuteReader method, the only loop that follows is the loop for reading the SqlDataReader ...

One other thing I noticed while watching the profiler do its thing, that OUTPUT parameters are done so that you might as well use a SELECT statement...

declare @P1 varchar(8000)
set @P1=''
declare @P2 varchar(8000)
set @P2='1,2,3,6,7'
declare @P3 varchar(8000)
set @P3='111,112,113'
declare @P4 varchar(8000)
set @P4=''
declare @P5 varchar(8000)
set @P5=''
exec Report_ProjectSummary 
@Classifications = @P1 output, 
@ProjectStates = @P2 output, 
@Departments = @P3 output, 
@Teams = @P4 output, 
@ProjectOwners = @P5 output, 
@IsOperatingPlan = default, 
@ActualStartDateCompare = '=', 
@ActualStartDate = default, 
@ActualEndDateCompare = '=', 
@ActualEndDate = default, 
@TargetStartDateCompare = '=', 
@TargetStartDate = default, 
@TargetEndDateCompare = '=', 
@TargetEndDate = default, 
@SortColumn = 1, 
@SortDirection = 1, 
@Notification = ''
SELECT @P1, @P2, @P3, @P4, @P5

this chunk of code is called NUMEROUSly in profiler ... which slows the actual page because of profiler ... but if you run it without profiler attached its quite speedy ...

hmmm

Edited by - onamuji on 02/10/2003 16:17:21

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-10 : 11:52:52

Have you tried to step through your ASP.net page and see what's getting called where? I had a similar thing happening to me, and it was the way I was using soem event that caused "Page_Load" to fire twice every time.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-02-10 : 16:18:08

That's what I'm assuming but I attach the event manually (AutoEventWireup = false) ... plus I see it happening for a lot of SQL calls, but not all of them ... :-\ and its even happening to ones that don't return data ...

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-10 : 16:28:45

Could you post one of your code behind pages (take out SQL login info etc)?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-02-11 : 10:52:38

public CategorySet(int catalogCategoryID, int projectID) : base()
{
SqlDataReader dr = null;

using (SqlConnection connection = Application.Connection)
using (SqlCommand command = new SqlCommand("[CatalogItems.CategorySet]", connection))
{
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@CatalogCategoryID", SqlDbType.Int).Value = catalogCategoryID;
command.Parameters.Add("@CatalogCategory", SqlDbType.VarChar, 8000).Direction = ParameterDirection.Output;

if (projectID > 0)
command.Parameters.Add("@ProjectID", SqlDbType.Int).Value = projectID;

try
{
connection.Open();
dr = command.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
List.Add(new Item(dr.GetInt32(0), dr.GetString(1), dr.GetString(2), dr.GetDecimal(3)));

CatalogCategory = Application.GetString(command.Parameters["@CatalogCategory"]);
}
finally
{
command.Parameters.Clear();

if (dr != null && !dr.IsClosed)
dr.Close();

dr = null;

if (connection != null && connection.State != ConnectionState.Closed)
connection.Close();
}
}
}

this is the procedure that is called ... and its only called once per Load event. However I see 3 entries in the same instant for requesting the access from the same connection.

SP:Completed declare @P1 varchar(8000)
set @P1=NULL
exec [CatalogItems.CategorySet] @CatalogCategoryID = 5, @CatalogCategory = @P1 output, @ProjectID = 44070
select @P1 8 1490104349 CRP610 68 2003-02-11 10:50:36.323
SP:Completed declare @P1 varchar(8000)
set @P1=NULL
exec [CatalogItems.CategorySet] @CatalogCategoryID = 5, @CatalogCategory = @P1 output, @ProjectID = 44070
select @P1 8 1490104349 CRP610 68 2003-02-11 10:50:36.323
SP:Completed declare @P1 varchar(8000)
set @P1=NULL
exec [CatalogItems.CategorySet] @CatalogCategoryID = 5, @CatalogCategory = @P1 output, @ProjectID = 44070
select @P1 8 1826105546 CRP610 68 2003-02-11 10:50:36.323

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-11 : 10:57:37

That code looks perfect to me.

One thing you might want to try is to have the code return a dataset instead of the data reader. I know it's slower, but it might tell you if it's something with the DB code, or how you are calling the DB code.

I'll see if I can run profiler on some of my stuff today and see what it shows me.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources