Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Execution plan diff from ASP.NET and QAI have a simple stored procedure that queries 1 table which holds 7.7 million records. When I run the sproc from QA, it runs in 30 milliseconds. When I run the sproc from within ASP.NET it runs in 20 seconds. I ran profiler and found that the Execution plan is completely different when run from ASP.NET vs. QA. I am currently waiting for a call back from Microsoft, but in the meantime, does anyone have any ideas? Thanks.
Post Follow-up to this message
Re: Execution plan diff from ASP.NET and QAOne possible reason if different SET options. But without the proc code it i s difficult to say... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www. solidqualitylearning .com/ http://www.sqlug.se/ "Rich Miller" <rooster575@hotmail.com> wrote in message news:OXPIhHUNFHA.508@TK2MSFTNGP12.p hx.gbl... >I have a simple stored procedure that queries 1 table which holds 7.7 milli on records. > > When I run the sproc from QA, it runs in 30 milliseconds. > When I run the sproc from within ASP.NET it runs in 20 seconds. > > I ran profiler and found that the Execution plan is completely different w hen run from ASP.NET vs. > QA. > > I am currently waiting for a call back from Microsoft, but in the meantime , does anyone have any > ideas? > > Thanks. >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QAcan you elaborate on SET options.. I'm not sure what that means and how to change it. If it involves the parameters, they are provided and used in the query. [not delared in the sproc and set to the input parameter values] Thanks for your response. "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote in message news:%234f1bMUNFHA.1732@TK2MSFTNGP14.phx.gbl... > One possible reason if different SET options. But without the proc code it > is difficult to say... > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www. solidqualitylearning .com/ > http://www.sqlug.se/ > > > "Rich Miller" <rooster575@hotmail.com> wrote in message > news:OXPIhHUNFHA.508@TK2MSFTNGP12.phx.gbl... > >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QASee Books Online, SET for descriptions for the SET settings. Some dev enviro nment will turn on SET settings for you, making the SET setting different between, say QA and ADO.N ET. Also, I would use Profiler to trace the TSQL execution of the proc from your .NET code and execute that same code from QA to see if there is a difference. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www. solidqualitylearning .com/ http://www.sqlug.se/ "Rich Miller" <rooster575@hotmail.com> wrote in message news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl... > can you elaborate on SET options.. I'm not sure what that means and how to change it. > > If it involves the parameters, they are provided and used in the query. > [not delared in the sproc and set to the input parameter values] > > Thanks for your response. > > > "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote i n message > news:%234f1bMUNFHA.1732@TK2MSFTNGP14.phx.gbl... > >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QAThanks Tibor.. So far today your quite ahead of Microsoft. ;) I'll let you know what I find. "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote in message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl... > See Books Online, SET for descriptions for the SET settings. Some dev > environment will turn on SET settings for you, making the SET setting > different between, say QA and ADO.NET. > > Also, I would use Profiler to trace the TSQL execution of the proc from > your .NET code and execute that same code from QA to see if there is a > difference. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www. solidqualitylearning .com/ > http://www.sqlug.se/ > > > "Rich Miller" <rooster575@hotmail.com> wrote in message > news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl... > >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QAI ran; DBCC USEROPTIONS through an ASP.NET page and the results are: textsize 2147483647 language us_english dateformat mdy datefirst 7 quoted_identifier SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_n ull SET Through Query Analyzer the results are... textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_n ull SET So I guess this is not my problem ?? "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote in message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl... > See Books Online, SET for descriptions for the SET settings. Some dev > environment will turn on SET settings for you, making the SET setting > different between, say QA and ADO.NET. > > Also, I would use Profiler to trace the TSQL execution of the proc from > your .NET code and execute that same code from QA to see if there is a > difference. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www. solidqualitylearning .com/ > http://www.sqlug.se/ > > > "Rich Miller" <rooster575@hotmail.com> wrote in message > news:eubptpUNFHA.2252@TK2MSFTNGP15.phx.gbl... > >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QAHow are you calling it from ASP? There can be an implicit type conversion that may be different between the two. Are you using the Parameters collection and executing it as a sp or as text command? -- Andrew J. Kelly SQL MVP "Rich Miller" <rooster575@hotmail.com> wrote in message news:On$kwWVNFHA.2356@TK2MSFTNGP14.phx.gbl... >I ran; > DBCC USEROPTIONS > through an ASP.NET page and the results are: > > textsize 2147483647 > language us_english > dateformat mdy > datefirst 7 > quoted_identifier SET > ansi_null_dflt_on SET > ansi_defaults SET > ansi_warnings SET > ansi_padding SET > ansi_nulls SET > concat_null_yields_n ull SET > > Through Query Analyzer the results are... > textsize 64512 > language us_english > dateformat mdy > datefirst 7 > quoted_identifier SET > arithabort SET > ansi_null_dflt_on SET > ansi_defaults SET > ansi_warnings SET > ansi_padding SET > ansi_nulls SET > concat_null_yields_n ull SET > > > So I guess this is not my problem ?? > > > > "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote > in message news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl... > >
Post Follow-up to this message Re: Execution plan diff from ASP.NET and QAI see. I suggest again that you catch the call from Profiler and execute the very same from QA to see if there is any difference. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www. solidqualitylearning .com/ http://www.sqlug.se/ "Rich Miller" <rooster575@hotmail.com> wrote in message news:On$kwWVNFHA.2356@TK2MSFTNGP14.phx.gbl... >I ran; > DBCC USEROPTIONS > through an ASP.NET page and the results are: > > textsize 2147483647 > language us_english > dateformat mdy > datefirst 7 > quoted_identifier SET > ansi_null_dflt_on SET > ansi_defaults SET > ansi_warnings SET > ansi_padding SET > ansi_nulls SET > concat_null_yields_n ull SET > > Through Query Analyzer the results are... > textsize 64512 > language us_english > dateformat mdy > datefirst 7 > quoted_identifier SET > arithabort SET > ansi_null_dflt_on SET > ansi_defaults SET > ansi_warnings SET > ansi_padding SET > ansi_nulls SET > concat_null_yields_n ull SET > > > So I guess this is not my problem ?? > > > > "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai l.nomail.com> wrote i n message > news:%23WwgprUNFHA.568@TK2MSFTNGP09.phx.gbl... > >
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
