Advantages of stored procedures Part 21 — Transcript

Full transcript

1. 0:00Welcome to PRIM Technologies. I am
2. 0:01Wenut. This is part 21 of SQL Server. In
3. 0:05this session, we'll understand the
4. 0:07advantages of using store procedures
5. 0:09over ad hoc or inline SQL. Now, before
6. 0:12continuing with this session, I strongly
7. 0:14recommend to watch part 18 of this video
8. 0:16series where we have covered the basics
9. 0:18of STO
10. 0:20procedures. The greatest benefit of
11. 0:22using stow procedures is that they are
12. 0:24capable of reusing an execution plan.
13. 0:28Okay, let's understand what we mean by
14. 0:30an execution plan. Okay, first of all,
15. 0:33when we issue a query to a SQL server,
16. 0:36three things happen. First, it checks
17. 0:38the syntax of that query. Second, it
18. 0:41compiles that query. Third, it generates
19. 0:44an execution plan. Okay, what do we mean
20. 0:46by an execution plan? An execution plan
21. 0:49is nothing but for the query to retrieve
22. 0:51the data, what is the best possible way
23. 0:54available? Okay, now this depends on the
24. 0:58indexes that are available in SQL server
25. 1:00to help that query. So based on all
26. 1:02these factors, it's going to generate an
27. 1:04execution plan and then it executes the
28. 1:08query. So next time when you issue the
29. 1:10query, since that execution plan is
30. 1:12already generated, it will be cached by
31. 1:14SQL server. So next time when you run
32. 1:17the same query, what's going to happen?
33. 1:19It's going to reuse that execution plan.
34. 1:22Okay. So basically if it is stored
35. 1:24procedures you know stored procedures
36. 1:26have been doing this from a long time.
37. 1:29Okay. They are capable of retaining the
38. 1:32execution plan caching that execution
39. 1:34plan and reusing that. Okay. Now even
40. 1:37with ad hoc queries these days you know
41. 1:40they are able to reuse the execution
42. 1:42plan. Okay. With the new versions of SQL
43. 1:45server that we have. But the problem is
44. 1:47even a slight change in the query if
45. 1:51it's an ad hoc query. So even a slight
46. 1:53change in the query can prevent the
47. 1:55execution plan from being reused. Let's
48. 1:58understand what we mean by that. I have
49. 2:01this table called tbl employee which is
50. 2:03very simple. It has got three columns
51. 2:04name ID name and gender. And if you look
52. 2:07at the query here it select name from
53. 2:11tbl employee where ID is equal to one.
54. 2:13Now when I execute this query what's
55. 2:15going to happen first time when we issue
56. 2:17this query the syntax is checked it is
57. 2:19compiled and execution plan is generated
58. 2:22and then it executes that query returns
59. 2:24the data back which is very good now
60. 2:27when I execute this again the query is
61. 2:30the same thing even ad hoc queries these
62. 2:32days they can you know reuse the
63. 2:35execution plan so it reuses that
64. 2:37execution plan and it will be quick but
65. 2:40on the other hand if I change the
66. 2:41parameter okay I'm saying in the initial
67. 2:44query id is equal to 1. Now if I pass ID
68. 2:47is equal to two, it is not going to
69. 2:50reuse the execution plan. It's going to
70. 2:52generate a new plan for this. Okay. But
71. 2:55whereas when it comes to store
72. 2:57procedures, look at this. I have the
73. 2:58store procedure. Now to the store
74. 3:01procedure, uh I mean what the store
75. 3:03procedure bas basically does is it
76. 3:05retrieves the name of an employee, you
77. 3:07know, for a given ID. So this procedure
78. 3:10has a parameter called at id. So when
79. 3:12you pass that parameter, it gives the
80. 3:15name of that employee. Okay. Now if
81. 3:17you're new to stored procedures, I
82. 3:18strongly recommend to watch part 18 of
83. 3:20this video series where we have covered
84. 3:22the basics of stored procedures. Okay.
85. 3:25So now when I execute this one, look at
86. 3:28this. The parameter that I'm passing is
87. 3:29one. So basically the same thing
88. 3:31happens. Okay. Um the syntax you know
89. 3:35basically the the procedure is compiled
90. 3:37and then execution plan is generated etc
91. 3:40etc. And then when I execute this again
92. 3:43the execution plan is reused. Okay. Now
93. 3:46even if I execute this even if I change
94. 3:48the parameter to two in case of store
95. 3:51procedures the same plan will be reused.
96. 3:54But that's not the case with ad hoc SQL
97. 3:57queries. Okay. So in your interview just
98. 4:00in case if you're attending an interview
99. 4:02and if you if somebody asks you you know
100. 4:04will stored procedures can't adop
101. 4:07queries reuse execution plan they can
102. 4:11but even a slightest change in the query
103. 4:13maybe change in parameter values if it
104. 4:15is not a parameterized query or even a
105. 4:18space in the query you know if if for
106. 4:21example if you look at this query you
107. 4:24know initially when I execute this you
108. 4:26know the plan gets created
109. 4:28I get I execute again the plan will be
110. 4:31reused. But even if I have a simple
111. 4:33space here like this and if I execute
112. 4:35the same query again
113. 4:37now it doesn't reuse the execution plan.
114. 4:40It's going to generate a new execution
115. 4:41plan. So a simple chain you know space
116. 4:45or change in parameter values you know
117. 4:48can result in a new execution plan being
118. 4:52created. Okay. But that's not the case
119. 4:54with stored procedures. stored
120. 4:56procedures can reuse the execution plan.
121. 5:01All right. Another benefit from a
122. 5:04performance standpoint that stored
123. 5:05procedures has is they can reduce
124. 5:07network traffic. Now how can they reduce
125. 5:10network traffic? Let's understand that a
126. 5:12stored procedure usually is something
127. 5:14like this. We create the procedure.
128. 5:16There is the body for the stored
129. 5:17procedure. And within the body of the
130. 5:19stored procedure in reality, stored
131. 5:21procedures are or you know they they
132. 5:24have got enough number of lines maybe
133. 5:26there are I have seen stored procedures
134. 5:27even with 3,000 lines of code within
135. 5:30them lots and lots of logic happening
136. 5:32within them. Okay. Now when we have such
137. 5:35kind of a stored procedure and we want
138. 5:37to execute that store procedure all you
139. 5:40have to do is from the client
140. 5:41application it could be a net
141. 5:42application java application or simply a
142. 5:45SQL server management studio. So from
143. 5:47the SQL server management studio you
144. 5:49just need to specify the name of the
145. 5:52procedure I mean this execute keyword
146. 5:54the name of the procedure and the
147. 5:56parameter values and then you execute
148. 5:58that. Okay. So over the network what
149. 6:02gets transmitted just this three words.
150. 6:05Okay. But if this store procedure I mean
151. 6:07if you don't have a store procedure if
152. 6:10it is just ad hoc SQL that you want to
153. 6:12send from the client tools then you have
154. 6:15to send all those 3,000 lines of code
155. 6:18which is definitely going to have a you
156. 6:21know impact on network traffic. Okay. So
157. 6:24stored procedure since the store
158. 6:25procedure resides on SQL server itself.
159. 6:28All you have to tell the store procedure
160. 6:29to do is give the name of the stored
161. 6:31procedure and the parameters and tell it
162. 6:34to execute it. That's it. But if it is
163. 6:36not a stored procedure, then you will
164. 6:38have to pass all those ad hoc SQL
165. 6:40statements from the client tool to the
166. 6:43SQL server which will definitely
167. 6:45increase the network
168. 6:47traffic. So that's another benefit. So
169. 6:49from a performance standpoints, you
170. 6:51know, these are the two advantages,
171. 6:53execution plan reuse and network traffic
172. 6:57redaction. Okay. And another advantage
173. 7:00of using stored procedures is code
174. 7:02reusability and better maintainability.
175. 7:04Now if it's a stored procedure, you know
176. 7:06that resides on the server. Now several
177. 7:09applications can reuse that stored
178. 7:11procedure. If there are multiple
179. 7:12applications which want a similar kind
180. 7:15of data, they can just call that stored
181. 7:17procedure. Okay. Now what is the
182. 7:19advantage of doing so? The advantage of
183. 7:21doing so is that tomorrow for some
184. 7:24reason if there is a bug in that store
185. 7:26procedure or the logic of the store
186. 7:28procedure has to be changed you only
187. 7:30have one place to change it irrespective
188. 7:33of that being used by multiple
189. 7:35applications. So maintainability also
190. 7:38becomes much easier. Reusability
191. 7:41reusability definitely promotes
192. 7:42maintainability. So stored procedures
193. 7:45does that. Whereas if it is an inline
194. 7:47SQL, what's the problem? If it's an
195. 7:49inline SQL, then every application has
196. 7:52to issue those SQL statements. They
197. 7:54prepare that SQL and then issue those
198. 7:56SQL statements from the application. So
199. 7:58you have this copies of SQL statements
200. 8:01spread across all the applications where
201. 8:03you want to reuse that and tomorrow if
202. 8:05there has to be a change in the logic,
203. 8:07you will have to change it across all
204. 8:09those applications which makes
205. 8:12maintenance a
206. 8:13nightmare. Okay. So definitely stored
207. 8:16procedures promote reusability and
208. 8:18better
209. 8:19maintainability and from a security
210. 8:21standpoint okay they provide better
211. 8:23security as well. Okay. And they provide
212. 8:26security by two means. Let's understand
213. 8:28what you know both of them in terms of
214. 8:30finer grain
215. 8:32control. Okay. So what do we mean by
216. 8:34that? Let's say a user needs data from a
217. 8:37specific table. Okay. Now if a user
218. 8:40needs let's say I have IT department, HR
219. 8:43department, payroll department and one
220. 8:45of the managers or employee needs IT
221. 8:47department data. Okay.
222. 8:49Now if that table has got all these
223. 8:53departments data it payroll HR and if I
224. 8:56grant him access to the entire table
225. 8:57what's going to happen is going to he's
226. 8:59going to see all everybody's records and
227. 9:02we don't don't want that okay so in
228. 9:04those cases what you can do you can
229. 9:06actually create a store procedure and
230. 9:08grant him access to execute that store
231. 9:10procedure without actually granting
232. 9:13access to the underlying table itself.
233. 9:16So this give allows us to have finer
234. 9:18grain control over what data he can see.
235. 9:22Okay. So stored procedures can provide
236. 9:24security in that angle. Not only stored
237. 9:27procedures, we can also use views for
238. 9:30that purpose. Okay. But stored
239. 9:31procedures is another
240. 9:33way. And another way you know how stored
241. 9:37procedures provide security is they
242. 9:39avoid SQL injection attack. Now most of
243. 9:43the people who are new to application
244. 9:45development they dynamically build SQL
245. 9:48statements in the application and then
246. 9:50issue those to the to the SQL server
247. 9:53database. Now what's going to happen is
248. 9:55in the user interface elements people
249. 9:58can type in you know SQL and inject that
250. 10:01to the dynamically built up SQL code.
251. 10:04Now if you if you if you don't know how
252. 10:06to do that you know I have already
253. 10:08recorded a video on what is SQL
254. 10:10injection attack how key how how can I
255. 10:13how we can actually inject SQL and how
256. 10:16to prevent it using stored procedures. I
257. 10:18have a video recorded already on this.
258. 10:20Please watch this video. Okay. So these
259. 10:23are the these are the greatest benefits
260. 10:25of using stored procedures. Okay. From a
261. 10:28performance standpoint they can reuse
262. 10:30execution plan and reduce network
263. 10:32traffic. and code reusability and better
264. 10:35maintainability. And from a security po
265. 10:38standpoint, you can have finer grain
266. 10:40control by granting access only to the
267. 10:43store procedure and not to the
268. 10:44underlying tables. And they can also
269. 10:47simply avoid SQL injection attack. I
270. 10:49mean not only stood procedures, even if
271. 10:51it's an ad hoc query, if they are
272. 10:53parameterized queries, they still can
273. 10:55avoid SQL injection
274. 10:59attack. On this slide, you can find
275. 11:01resources for ASP.NET net and uh C#
276. 11:04interview questions. That's it for
277. 11:06today. Thank you for listening. Have a
278. 11:08great day.