Re: Procedure execution problem - Clients performances

It may not be the dates but it is still a possibility if .net is interpreting them one way and ODBC another. That would be a different range and the optimizer would likely choose a different plan if the range difference was great enough.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Robson Luiz" <robsonbr_2004@xxxxxxxxxxxx> wrote in message news:uJGeFUoPIHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx> escreveu na mensagem news:236F0959-68A3-4DB1-85EE-AD8517CC8675@xxxxxxxxxxxxxxxx
>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@xxxxxxxxxxxx> wrote in message news:%23LQfNQjPIHA.2208@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxx> wrote in message news:e5a3pigPIHA.4752@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxx> wrote in message news:ursNXmdPIHA.4808@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxx> wrote in message news:uCKTk9cPIHA.4752@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxx> wrote in message news:e2Ohe0cPIHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxx> wrote in message news:OR2OQMbPIHA.5164@xxxxxxxxxxxxxxxxxxxxxxx
Sounds like parameter sniffing issue. Have a look at these:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Robson Luiz" <robsonbr_2004@xxxxxxxxxxxx> wrote in message news:%23mBc09ZPIHA.280@xxxxxxxxxxxxxxxxxxxxxxx
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














.

Relevant Pages

  • Re: Procedure execution problem - Clients performances
    ... Again check the parameters and plan with a trace. ... Andrew J. Kelly SQL MVP ... Dim conn As New SqlConnection ...
    (microsoft.public.sqlserver.clients)
  • Re: Procedure execution problem - Clients performances
    ... Andrew J. Kelly SQL MVP ... Dim conn As New SqlConnection ... 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. ...
    (microsoft.public.sqlserver.clients)
  • Re: Procedure execution problem - Clients performances
    ... Andrew the timeout is got on FILL command.. ... Dim conn As New SqlConnection ... Same procedure with same parameters executes in 1 sec in sql management ...
    (microsoft.public.sqlserver.clients)
  • Re: Procedure execution problem - Clients performances
    ... Andrew J. Kelly SQL MVP ... Dim conn As New SqlConnection ... 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. ...
    (microsoft.public.sqlserver.clients)
  • Re: Procedure execution problem - Clients performances
    ... .net may interpret it differently than ODBC in SSMS. ... Andrew J. Kelly SQL MVP ... Dim conn As New SqlConnection ... 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. ...
    (microsoft.public.sqlserver.clients)
Loading