Query Analizer vs ADONET

  • Thread starter Thread starter Patriot
  • Start date Start date
P

Patriot

I have a stored procedure. If I run it using Query Analizer, it takes
no time to return the result (max 2 records, 7 columns and approx. 15
bytes/record)

However, If I execute it from ASPNET Code (see below), it takes approx.
1 - 2 seconds.

If I have to loop and list of Part_IDs (500-1000 records), this adds up
to significantly of wait time for a webbrowser. In fact, in Production,
it usually throws an error stating "Object Reference not set to an
instance of an object".

If I debug it in VS.2003, it will go through without issue though it
takes hella long time.

Has anyone experienced a similar problem with ADONET? or Can you help
me point out what is wrong with the code below?

Thank you in advance.



sqlAdt = New SqlDataAdapter("DB_LIST_PRICE_HISTORY_B2SP",
iDatabankConn)
sqlAdt.SelectCommand.CommandType = CommandType.StoredProcedure
sqlAdt.SelectCommand.Parameters.Add("@PART_ID_in", SqlDbType.VarChar,
25).Value = drDtl("Part_ID")
sqlAdt.SelectCommand.Parameters.Add("@PRICE_REGION_ID_in",
SqlDbType.Int).Value = iPriceRegionID.ToString
sqlAdt.SelectCommand.Parameters.Add("@FROM_DATE_in", SqlDbType.VarChar,
10).Value = ""
sqlAdt.SelectCommand.Parameters.Add("@TO_DATE_in", SqlDbType.VarChar,
10).Value = Convert.ToDateTime(Tdate).AddDays(1).ToShortDateString
lDs = New DataSet
sqlAdt.Fill(lDs, "PriceHist")
 
Not sure how you are looping, that seems the most likely bottleneck. Try
running the app with a Profiler trace to see what is happening (it would
also help if we could see the sproc). Also ensure you have an index on the
appropriate columns in the db.
 
Patriot said:
I have a stored procedure. If I run it using Query Analizer, it takes
no time to return the result (max 2 records, 7 columns and approx. 15
bytes/record)

However, If I execute it from ASPNET Code (see below), it takes approx.
1 - 2 seconds.
Click to expand...

You are, of course, testing this twice before drawing this conclusion,
right? The reason I ask is first hit on debug requires a JIT compile. The
second hit is the way it will work after the first hit in production, when
the jitted code is in memory.
If I have to loop and list of Part_IDs (500-1000 records), this adds up
to significantly of wait time for a webbrowser. In fact, in Production,
it usually throws an error stating "Object Reference not set to an
instance of an object".
Click to expand...

The object reference means you have a field that has no value that you are
setting to an object that requires a value. Most likely a null on one of the
fields. You can test this.

The object reference could also come because of a time out. Without seeing
the loop, I am clueless, however.

In an exceptional condition, times do extend.
If I debug it in VS.2003, it will go through without issue though it
takes hella long time.

Has anyone experienced a similar problem with ADONET? or Can you help
me point out what is wrong with the code below?

Thank you in advance.



sqlAdt = New SqlDataAdapter("DB_LIST_PRICE_HISTORY_B2SP",
iDatabankConn)
sqlAdt.SelectCommand.CommandType = CommandType.StoredProcedure
sqlAdt.SelectCommand.Parameters.Add("@PART_ID_in", SqlDbType.VarChar,
25).Value = drDtl("Part_ID")
sqlAdt.SelectCommand.Parameters.Add("@PRICE_REGION_ID_in",
SqlDbType.Int).Value = iPriceRegionID.ToString
sqlAdt.SelectCommand.Parameters.Add("@FROM_DATE_in", SqlDbType.VarChar,
10).Value = ""
sqlAdt.SelectCommand.Parameters.Add("@TO_DATE_in", SqlDbType.VarChar,
10).Value = Convert.ToDateTime(Tdate).AddDays(1).ToShortDateString
lDs = New DataSet
sqlAdt.Fill(lDs, "PriceHist")
Click to expand...

I see nothing inherently wrong with what you are doing, although it is
better to explicitly set the the command object and attach to the adapter.
if you are going to loop to, for example, pick and choose, a DataReader
might be a better option. I would also get which row you are having issue
with.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************
 
Hi Earl, Greg,

Thanks for your feedback. Below is the sproc. I run this with Query
Analyzer, it takes no time to give me 2-record result. That's why I
suspect something is wrong with the way this code use ADONET.

When I debug it, I can see the 1-2 seconds delay at this line
sqlAdt.Fill(lDs, "PriceHist") .

The other codes don't yeild significant time so I dont post it here.
Besides, the codes get wrapped around and hard to read as I post it.

I will try your suggestion explicitly using Command object, Greg.


CREATE PROCEDURE DB_LIST_PRICE_HISTORY_B2SP
(
@PART_ID_in VARCHAR (25),
@PRICE_REGION_ID_in int,
@FROM_DATE_in varchar(10) = '',
@TO_DATE_in varchar(10)
)

AS

SET NOCOUNT ON

-- This gets the current & previous price
Select Top 500 Max(Td.BatchNo) as BatchNo,
cast(convert(varchar(40),Td.Effective_Date,101) as datetime) as Efx
into #Top1
From V_DBT02D_PARTS_PRICE_CHANGE_BATCHNO Td
Inner Join V_DBT02e_PARTS_PRICE_CHANGE_BY_BATCHNO Te On
Td.BatchNo = Te.BatchNo
Where Effective_Date BETWEEN convert(datetime, @FROM_DATE_in + '
00:00:00', 120)
AND convert(datetime, @TO_DATE_in + '
23:59:59', 120)
And Price_Region_ID = @PRICE_REGION_ID_in
And Te.Part_Id = @PART_ID_in
Group By cast(convert(varchar(40),Effective_Date,101) as
datetime)
Order By cast(convert(varchar(40),Effective_Date,101) as
datetime) desc

Select Top 2 T1.efx as Effective_Date, cast(Te.Changed_Price as
varchar(12)) as Changed_Price,
Tc.Price_Region, Tc.Currency_Code, Td.Status, Te.CreatedBy,
Te.BatchNo
From V_DBT02E_PARTS_PRICE_CHANGE_BY_BATCHNO Te,
DBT02C_PARTS_PRICE_REGION Tc,
V_DBT02D_PARTS_PRICE_CHANGE_BATCHNO Td, #Top1 T1
Where T1.Batchno = Td.BatchNo
and Te.BatchNo = Td.BatchNo
and Te.Part_Id = @PART_ID_in
and Tc.Price_Region_Id = Td.Price_Region_Id
and Tc.Price_Region_Id = @PRICE_REGION_ID_in
Order By T1.Efx desc, T1.BatchNo desc
 
I would figure out how to get rid of the row-by-row CONVERT expressions. If
you use BETWEEN, TSQL can figure out how to deal with the date formatting
for you. And yes, I expect there is some caching involved that might be
affecting your performance numbers.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------
 
Hi Bill,

Thanks for your feedback. I also suspect some caching involved, but I
haven't found where that would be as I just inherited these codes. I
also tested using DataReader and explicitly used SqlCommand object as
suggested above by Greg and Earl. And the 1-2 seconds delay still
happens at the call to the stored procedure. Again, if I use Query
Analyzer to execute the stored procedure, I can get the result
instantly (zero execution time). Here is the code:

Dim sqlCmd As SqlCommand = New SqlCommand("DB_LIST_PRICE_HISTORY_B2SP",
iDatabankConn)
With sqlCmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@PART_ID_in", SqlDbType.VarChar, 25).Value =
drDtl("Part_ID")
.Parameters.Add("@PRICE_REGION_ID_in", SqlDbType.Int).Value =
iPriceRegionID.ToString
.Parameters.Add("@FROM_DATE_in", SqlDbType.VarChar, 10).Value = ""
'Convert.ToDateTime(Fdate).ToShortDateString
.Parameters.Add("@TO_DATE_in", SqlDbType.VarChar, 10).Value =
Convert.ToDateTime(Tdate).AddDays(1).ToShortDateString
myDataReader = .ExecuteReader(CommandBehavior.Default) ' this
takes 1-2 seconds to return
End With
 
Patriot,

Why not test your sample with some exact values instead of variables. I
assume you did that in the server as well.

Just as idea,

Cor
 
Thanks for your feedback, Cor.

I think Bill is right about the caching. I worked with my DBA who just
got back from his vacation and we ran some tests. It seems to be the
result of the sproc is cached and that is why I got no execution time
as I tested only for one value. When feeding the data which is
different at every call to the sproc to the debugger, the database
server (not a high-end 5 year-old server) can not cache all the result.
This produces the 1-2 seconds delay.

I think I will shift my focus to the stored procedure and work with the
DBA about upgrading our server.

Thank you all for your feedback.
 
Consider that when you call a SP for the first time, the query plan is
cached and used from that point forward--until it's forcibly removed or
overlaid. The subsequent requests for the SP might not match the cached plan
(because of the changes in parameters). I would inspect the query plan with
your DBA and see where all of the work is being done. It can also suggest
better indexes to improve performance. No, I don't expect asking the
question or fetching the results faster will help--it's the intelligence of
the question--not how fast you ask it that gates performance. ;) Consider
that the Fill is also running a DataReader (with far less developer-written
code).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
 
Back
Top