 | |
Procedure execution problem - Clients performances
microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Procedure execution problem - Clients performances -
12-14-2007
, 03:30 AM
|
One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#12
| |||
| |||
|
|
One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#13
| |||
| |||
|
|
I always use ISO format YYYY/MM/DD. Then no chance for mistakes The result is always correct when it works.. Best Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:e5a3pigPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#14
| |||
| |||
|
|
Andrew the timeout is got on FILL command.. I test under debug step by step. Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> escreveu na mensagem news:e5a3pigPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#15
| |||
| |||
|
|
I always use ISO format YYYY/MM/DD. Then no chance for mistakes |
SET LANGUAGE us_english
SELECT CAST('2005/05/23' AS datetime)
GO
SET LANGUAGE german
SELECT CAST('2005/05/23' AS datetime)
GO
Also, ISO format uses dash, not slash. And, even with dash, the format isn't safe since you use
separators without the T between date and time portion. See Andrew's replies for elaboration.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote
Quote:
|
I always use ISO format YYYY/MM/DD. Then no chance for mistakes The result is always correct when it works.. Best Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:e5a3pigPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#16
| |||
| |||
|
|
I always use ISO format YYYY/MM/DD. Then no chance for mistakes No, that is not correct: SET LANGUAGE us_english SELECT CAST('2005/05/23' AS datetime) GO SET LANGUAGE german SELECT CAST('2005/05/23' AS datetime) GO Also, ISO format uses dash, not slash. And, even with dash, the format isn't safe since you use separators without the T between date and time portion. See Andrew's replies for elaboration. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23LQfNQjPIHA.2208 (AT) TK2MSFTNGP06 (DOT) phx.gbl... I always use ISO format YYYY/MM/DD. Then no chance for mistakes The result is always correct when it works.. Best Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:e5a3pigPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
#17
| |||
| |||
|
|
Ok . But the causes are not the dates. The issue in this question is about query plans. Robson "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com escreveu na mensagem news:236F0959-68A3-4DB1-85EE-AD8517CC8675 (AT) microsoft (DOT) com... I always use ISO format YYYY/MM/DD. Then no chance for mistakes No, that is not correct: SET LANGUAGE us_english SELECT CAST('2005/05/23' AS datetime) GO SET LANGUAGE german SELECT CAST('2005/05/23' AS datetime) GO Also, ISO format uses dash, not slash. And, even with dash, the format isn't safe since you use separators without the T between date and time portion. See Andrew's replies for elaboration. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23LQfNQjPIHA.2208 (AT) TK2MSFTNGP06 (DOT) phx.gbl... I always use ISO format YYYY/MM/DD. Then no chance for mistakes The result is always correct when it works.. Best Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:e5a3pigPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... One comment. You should use the unseperated format for datetimes so there is no mistaking the days for months etc. For instance the value you have of '2007/10/11' can be interpreted in at least two ways depending on some client settings. Is it Oct 11th or Nov 10th? .net may interpret it differently than ODBC in SSMS. http://www.karaszi.com/SQLServer/info_datetime.asp In .net you should specify the datatypes for each parameter as well. And the example you gave the dates are not the same for both. Did you run a trace to compare the execution plan and statistics from each to see what is different? In the .net example you are filling a Dataset and then binding to a Grid, can you eliminate those as a source of the issue? Maybe just fill a DS and don't bind it or do any other processing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:ursNXmdPIHA.4808 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Procedure call in SSMS exec dbo.mapa_separacao_pesquisar_ext @empresa_id=1,@unidade_negocio_id='2.001.02',@data _inicial='2007/10/11',@data_final='2007/12/11' Executes in 1 second in .net app. Dim conn As New SqlConnection conn.ConnectionString = "Data Source=server1;Initial Catalog=varuna70;Integrated Security=True" Dim cmd As New SqlCommand Dim par1 As SqlParameter Dim par2 As SqlParameter Dim par3 As SqlParameter Dim par4 As SqlParameter Dim sqla As New SqlDataAdapter Dim ds As New DataSet cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "mapa_separacao_pesquisar_ext" par1 = New SqlParameter("@empresa_id", 1) cmd.Parameters.Add(par1) par2 = New SqlParameter("@unidade_negocio_id", "2.001.02") cmd.Parameters.Add(par2) par3 = New SqlParameter("@data_inicial", "2007/11/11") cmd.Parameters.Add(par3) par4 = New SqlParameter("@data_final", "2007/12/11") cmd.Parameters.Add(par4) cmd.Connection = conn sqla.SelectCommand = cmd sqla.Fill(ds) -- timeout Me.DataGridView1.DataSource = ds.Tables(0) Me.DataGridView1.Refresh() "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:uCKTk9cPIHA.4752 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Exactly how are you calling it in SSMS? Can you show the exact code? In .net you are defining the datatypes and in SSMS it may be guessing. Plus it will be an RPC call in .net vs. a Batch in SSMS and will get two plans which may or may not be the same. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:e2Ohe0cPIHA.4272 (AT) TK2MSFTNGP06 (DOT) phx.gbl... But it still strange.. Same procedure with same parameters executes in 1 sec in sql management studio and get me timeout when executed in a .net application using sqlclient. but if i recompile the SP in SSMS both cases work well. But after a time the problem repeats.. I see SSMS is not sensitive to query plan as sqlclient is. Robson Robson "Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message news:OR2OQMbPIHA.5164 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Sounds like parameter sniffing issue. Have a look at these: http://www.microsoft.com/technet/pro...05/recomp.mspx http://blogs.msdn.com/queryoptteam/a...31/565991.aspx http://omnibuzz-sql.blogspot.com/200...rocedures.html -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Robson Luiz" <robsonbr_2004 (AT) yahoo (DOT) com.br> wrote in message news:%23mBc09ZPIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl... Hello people. I have a strange behaviour with a stored procedure. Using management studio procedure executes in 1 second. Using a .net application (VS 2005) with sqlclient same procedure get me a timeout execution.. Any help??? Best Robson |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.