Re: Procedure execution problem - Clients performances

In fact i noted that behaviour after upgrade a database from 2000 to 2005.
Performance is some cases is poor.

"Warren Brunk" <wbrunk@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:7052533E-0A8B-4227-87E7-0AE167157558@xxxxxxxxxxxxxxxx
Have you tried updating the statistic on the table(s) that the procedure
is
using? I have seen this before with out of data statistics. What I feel is
happening is that it is using two seperate execution plans...

http://msdn2.microsoft.com/en-us/library/ms187348.aspx

--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/


"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
    ... Dim conn As New SqlConnection ... Dim par1 As SqlParameter ... .net you are defining the datatypes and in SSMS it may be guessing. ... 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
    ... Dim conn As New SqlConnection ... Dim par1 As SqlParameter ... In .net you are defining the datatypes and in SSMS it may be guessing. ... Same procedure with same parameters executes in 1 sec in sql management ...
    (microsoft.public.sqlserver.clients)
  • Re: Procedure execution problem - Clients performances
    ... Dim conn As New SqlConnection ... Dim par1 As SqlParameter ... 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. ... 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
    ... Procedure call in SSMS ... Dim conn As New SqlConnection ... Dim par1 As SqlParameter ... Same procedure with same parameters executes in 1 sec in sql management ...
    (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