SQL Stored Procedure VB.Net

Question

I have a Stored Procedure which when run on SQLServer takes 1 second to run, yet in my VB.Net code it takes nearly 20 seconds. It takes a long time on the line:

Adapter.Fill(ds,"TimeTable")

Am I doing something wrong for it to take so long?

My code snippet is below:

    SQLConn = New SqlConnection(SQLDConnString)
    cmd = New SqlCommand("SPNAME", SQLConn)
    cmd.CommandType = CommandType.StoredProcedure
    SQLConn.Open()
    cmd.Parameters.AddWithValue("@p1", p1)
    cmd.Parameters.AddWithValue("@p2", p2)
    cmd.Parameters.AddWithValue("@p3", p3)
    cmd.Parameters.AddWithValue("@p4", p4)
    adapter.SelectCommand = cmd
    adapter.Fill(ds, "TimeTable")
    DataGridView1.DataSource = ds.Tables("TimeTable")
    SQLConn.Close()

When you do `Adapter.Fill(ds,"TimeTable")`, you are doing more than just running the sproc - you are also transferring *all the results* from the server to the client. How much data are we talking about here? — AakashM, Jan 30 '13 at 12:01
Possible duplicate of http://stackoverflow.com/questions/250713/sqldataadapter-fill-method-slow — andy, Jan 30 '13 at 12:03

score 0 · Answer 1 · answered Jan 31 '13 at 00:02

If your datagridview property for column and row height/width is set to auto, it can take a lot of time to finish the procedure. I had this happen once, where a stored procedure took an abnormally large amount of time and it ended up being that property that was causing it to stall.

score 0 · Accepted Answer · answered Feb 12 '13 at 13:00

0

I seemed to find a fix to this by declaring local variables in my SP and then assigning my Parameters to these variables.

Something to do with parameter sniffing.

answered Feb 12 '13 at 13:00

user1295053

303
1
7
17

SQL Stored Procedure VB.Net

2 Answers2