|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query Analizer vs ADONETno 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. Show quote "Patriot" <tim.***@gmail.com> wrote in message news:1162325828.694623.295290@m73g2000cwd.googlegroups.com... >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") > "Patriot" <tim.***@gmail.com> wrote in message You are, of course, testing this twice before drawing this conclusion, news:1162325828.694623.295290@m73g2000cwd.googlegroups.com... >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. 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 The object reference means you have a field that has no value that you are > 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". 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. Show quote > If I debug it in VS.2003, it will go through without issue though it I see nothing inherently wrong with what you are doing, although it is > 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") > 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. -- Show quote____________________________________ 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... ----------------------------------------------------------------------------------------------------------------------- "Patriot" <tim.***@gmail.com> wrote in message news:1162333986.642422.105990@b28g2000cwb.googlegroups.com... > 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 > 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 Show quote "Patriot" <tim.***@gmail.com> schreef in bericht news:1162405192.341396.75050@e3g2000cwe.googlegroups.com... > 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 > 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). -- Show quote____________________________________ 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... ----------------------------------------------------------------------------------------------------------------------- "Patriot" <tim.***@gmail.com> wrote in message news:1162405192.341396.75050@e3g2000cwe.googlegroups.com... > 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 > You may be a victim.
http://www.eggheadcafe.com/tutorials/aspnet/353cb16c-3cde-44dd-a264-750c1ce4e423/sql-server-query-analyzer.aspx -- Show quoteRobbe Morris - 2004-2006 Microsoft MVP C# I've mapped the database to .NET class properties and methods to implement an multi-layered object oriented environment for your data access layer. Thus, you should rarely ever have to type the words SqlCommand, SqlDataAdapter, or SqlConnection again. http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp "Patriot" <tim.***@gmail.com> wrote in message news:1162325828.694623.295290@m73g2000cwd.googlegroups.com... >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") >  |
|||||||||||||||||||||||
Other interesting topics