Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mgozick
Starting Member
2 Posts |
 Posted - 2011-11-18 : 11:33:04
|
| From a VB.net exe, I am calling a SP that has a user defined table type as a parameter. The SP merely inserts all records in the user defined table to a permanent table. The problem is that the SP does not start executing for 20 seconds or so. Once it starts, all records are inserted very quickly. Depending on how many rows are in the udt, the longer the SP takes before it starts executing. What can be the cause/solution that will enable to SP to start executing immediately? Is the SP recompiling, or somehow pre-processing the data that is being passed into it? The SP itself is very small.edit: moved to proper forum |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-18 : 11:43:43
|
| How much data do you have?A table structure (probably something similar to a table variable but I don't know) has to be created and populated from your application. If there is a lot of data or the connection from your app is slow it could take a long time.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mgozick
Starting Member
2 Posts |
Posted - 2011-11-18 : 12:05:45
|
| The user define table is only 10 columns, and contains 50,000 rows.When we run the same code on different target servers, we get different results. (ie. some servers, there is no latency at all, and the SP returns quickly, other servers, there is a 20 second lag.I have Profiled it, and when the latency happens, it is within thestart/stop time of the SP itself. |
|
|
|
|
|
|
|
|
SP latency when sending user defined table type