Why is stored procedure slower than query

A possible point can be the query optimizer of the database. If you use a normal sql statement the database parse your code, optimize it depending the current state of database - memory situation, size of tables, Creating stored procedures summarizes the steps to compile, before the actual execution.

Thus the creation of the procedure and the retrieval of data are separated in time, the optimizer may not be as effective. Especially with complex queries on large tables could lead to reduced performance. Sign up to join this community. The best answers are voted up and rise to the top.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Why is a stored procedures much slower than the single query? Ask Question. Asked 8 years, 11 months ago. Active 8 years, 11 months ago. Viewed 1k times. Code below Improve this question. Styp Styp 1, 11 11 silver badges 26 26 bronze badges. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Learn more. Why does a query run slower in a Stored Procedure than in the Query window? Ask Question. Asked 9 years, 7 months ago. Active 4 years, 2 months ago.

Viewed 40k times. Here's what my query looks like. LetterCode -- Letter Id , M. RecordId, PH. DatePaid, PH. It should probably be noted that all the tables used in the query have millions of records.

Improve this question. Community Bot 1. Rachel Rachel 8, 20 20 gold badges 45 45 silver badges 73 73 bronze badges. MartinSmith Thanks. Sign in. United States English.

Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. This is the place for advice and discussions 0 1. Sign in to vote. I have a stored procedure that contains 2 complex queries involving joins and cases, and two parameters. When executing the stored procedure itself, it takes more than 4 minutes.

However, when I copied the queries to query window in sql server management studio, it took 0 second. I looked it up on the internet.