Stored Procedure takes a lot time when called from asp.net/ .ajax but executes quickly as batch

I'm tracking with sql server profile a stored proc called via Ajax in asp.page.

this procedure takes 10 parameters that are user defined table types(tables).

Let'开发者_StackOverflows call it MyProc.

when i call MyProc from .ajax ,it show in SQL Profiler :"RPC Starting" Event and My Proc >10 min to finish.

When i copy paste [MyProc script call] and paste it to SSMS(executed as batch from SSMS=Batch Event(BatchStartin,BatchCompleted)in the profiler),the execution takes 3 seonds.Is normal.

Has anyone already encountered this situations?Any ideas.

I have experienced a very similar issue. My issue was simple: a table needed an index. I don't know the details, but parameterized queries made the index issue far worse. If I ran the query without parameters, it was fast, but with them it was horrible. Once the index was added, it was fine.

I'm not as well versed in SQL as I used to be, but I would try rebuilding the stored procedures. What you describe used to be pretty common if you built your stored procedures back when there was no data in the table or the nature of the table tends to change over time.

A stored procedure works so quickly because it saves some of the preliminary (and time-consuming) tasks, such as the parsing and query plan. If your database contents looked a lot different than it does now, the query plan may be way off, and looking for data in the least efficient ways. When you execute your scripts outside of a stored procedure, the query plan is built from scratch according to its latest understanding of the data it needs to search, so it will tend to be consistently fast over time regardless what's in your tables or how the table has changed over time.

Basically any time that a stored procedure returns more slowly than it does executing in a regular query, it's a sign the stored procedure's query plan needs updating.