How Stored Procedures make databases BLAZINGLY FAST
Вставка
- Опубліковано 5 лип 2024
- Stored Procedures are functions which can run on a database. Here, we discuss how stored procedures work, when and why they should be used, and the potential drawbacks of this approach.
We illustrate these points with a real-world example: a war story!
00:00 What are stored procedures?
00:40 Where and how do they run?
01:25 Real-world example
02:30 Request-Response Flow
03:20 First Suggestion - BiDirectional BFS
04:14 Cache results
05:30 Debugging
06:02 The Problem
07:08 Solution - Stored Procedures
08:03 Benefits of Stored Procedures
09:59 Drawbacks of Stored Procedures
12:00 Thank you!
System Design Course at InterviewReady: interviewready.io/
You can follow me on:
Github: github.com/InterviewReady/sys...
Instagram: / interviewready_
LinkedIn: / interview-ready
Twitter: / gkcs_
#Databases #StoredProcedures #QueryOptimization
We were using stored procedures in our enterprise banking application and all drawbacks you mentioned fit perfectly well.
It's really difficult to understand in first go why the proc gave an error. cant even debug the proc.
I wasn't aware of such huge benefit stored procs are giving as banking application logic is indeed complex.
But when this application is moved to microservices architecture then architects have decided to do away with stored procedures and we are not using them anymore.
But its always good to use stored procedures. It will optimize your queries. You guys converted your project to microservice architecture, its strange why removed uses of SPs? These days even the engineering team-developers are suppose to know how to create and use these SPs. Its a mandate skillset one should know. Please do read more about SPs, I'm sure you will know how good it is in optimizing your queries as Gaurav highlighted.
Btw which db you guys use?
@@kavitakumawat4929 Microservice "architecture" is a huge meme, and everyone is still falling for it. How can anyone think that taking Data, the center point of any business, and splitting it into numerous eventually "consistent" databases is a good idea? Now it's impossible to run data analysis without dumping data from all the different databases and joining it together. Now you have to service many different databases separately. Now you can't debug your business logic, because it's running on different servers and in different processes, constantly talking over the network. It's pure madness.
Very interesting! Seems like stored procedures have richer applications than views because views are read-only whereas stored procedures can make changes to the database as well
Even if we want multiple friend layers, a simple recursive cte call would work just fine.
Stored procedures are amazing as the server optimizes and generates the most optimal solution to the query using index scans
I dont know if you even need a stored procedure in order to do this. At least on Oracle databases, you can write your select statement using CONNECT BY PREVIOUS and START WITH, and the select will return all friends of friends, going as deep as it needs to, starting from the passed user_id. It can even return a 'level' column.
Depending on how you've implemented the stored procedure, I think this solution might be faster.
Amazing explanation, you came in as I resolved the same problem with stored procedures with PostgreSQL in my application. A nice revision for me, thanks!
Thank you!
Great video Gaurav. SPs sound great, but It's not always a good idea to move the business logic to DB layer. Other pain points include upgrade/migration of database, modification of Stored Procedures, difficult troubleshooting of SPs etc.
Skill issue
Having worked with both SPs and code based queries for many years now, I find SPs easier to maintain and understand after the code has switched multiple hands. Even updating them is easy. Only drawback is debugging them. That needs to be taken care of by using debugging statements at strategic places. But most people prefer writing queries in code and not in SPs because they are not comfortable with it.
did you try WITH recursive query ?
The example that you used is a bit problematic. The problem appears to have been that the data processing happened in the app and not database. This is a cleaner design, but in certain cases (when handling lots of rows) leads to terrible performance, which in your case was exacerbated by a high round trip time, and not using persistent database connections. Databases are optimised for high performance data processing, and if you know how to use them efficiently, you can benefit greatly from that. It isn't necessary to use stored procedures to move data processing to the database. You could also use a join query, or a temporary table. You could also use persistent database connections, and put multiple SQL statements into one API call, if the API supports it.
Do you even need stored procedures? Simple query like following can work
WITH
a as (SELECT friend_id FROM friends WHERE user_id = 1)
b as (SELECT DISTINCT friend_id from friends WHERE user_id in (SELECT friend_id FROM a))
SELECT * FROM users WHERE id in (SELECT friend_id FROM b)
Not sure if this is also procedure
This would also work. We had some temporary variables and logic in between, so a stored procedure made sense.
I have been using Microsoft SQL (t-sql) for a long and not sure if it is the same, but there are a few things
Do you mean function table. I think that what you actually show here
Select * from functionname(parameter) where a stored procedures is executed storeProceduresname parametername=parameter
Now there a lot of ways to do this there is the CTE recursion method, close to what was commened on.
WITH friendCTE AS (
SELECT friendId FROM friends
WHERE userId = @id
UNION ALL
SELECT f.FriendId FROM friends f
JOIN friendCTE cte ON cte.friendId = f.userId
)
SELECT DISTINCT friendId FROM friendCTE
(I just free text this so double check the query first)
There are also other types of loops like while or cursors. Also a query with multiple parts to it with temp tables could be used
I just thought about it and this will get friend of friend of friend until it hit a max limit. And since a friend can be a friend that is the first friend this would make a infinity loop so you would have to keep track of the level and end it after n loops so you don't get stuck
@@cartercordingley6062 does it cause infinite loop? Even if it capture all the duplicate friends, and repeat some of its calculation, it doesn't have the chance for inf loop.
@SreekantShenoy
The query I posted had this issue if you don't account for it A>B>C>A>B...
MsSQL does have a limit of 100 layers by default to prevent a loop but will error it out. But there are way to avoid the loop but I would have to make a mock up of the tables to do some test but I don't have the time to do so right now
Hi Gaurav, I didn't understood the last drawback. Generally the projects I have experienced on does create a different sql projects which can be deployed to db directly. So even if db changes and it's sql then it should be able to deploy directly without changing anything in code. Can you please explain that drawback ?
Changing DB means moving to different database. Imagine you are using MySQL and for some reason you need to move to Postgres or MSSQL, Here you will be using SQL but it will be having DB engine specific differences in syntax, functions and datatypes. So you will need to do some compatibility changes in your Stored Procedures.
Also, You can write Stored Procedures in Java in Oracle DB, if you do so when you want to move to MySQL you need to re-write all the procedures in SQL because MySQL doesn’t support Stored Procedures in Java
@@banujan.b2479 Oh in that way, I was thinking in migration kind of thing. Thanks for clarification.
I would say to use graph db like neo4j for it
Why can't this be done using a simple self join or recursive cte?
For the example of friends of friends. If depth was just 2 levels. You could have used eager loading. Which will create 2 queries. One query to load 1st level friends and 2nd query with all the ids of all friends of friends. And then eager loader will then manage the assignment of data to respective friend.
I could do user.friends.includes(:friends) if only 2 level depth is needed.
Wouldn't a graph database like neo4j or Neptune be ideal for running graph queries?
It would, but the DB was an IBM DB2 instance. Asking the company to change their DB for our usecase wouldn't work.
Could not this have just been done with a simple SQL query that does a self join on friends table?
But then the join would have to be done for every single request whereas right now the join needs to happen only once for all requests
@@vedantgupta2601That doesn’t matter, because if you are doing self join on a subset of records, it’s way fast. And we can cache the results at backend server. This also has benefit of only getting joins for active users.
Hey it's interesting, But my solution is can't we use recursive query?
You can but it is slower
With recursive queries can written to solve this problem and I believe will it will be helpful and if we use with recursive query then friend of friend and .. so on you can get I tried with recursive query to get referral tree of user who refer whom at each level and it works fine for me.
While I do think stored procedures are good (still evaluating), I think this video exhibits the advantage of trying to use single query (employing joins or any other way) over executing multiple queries over the network.
graph database should work in this scenario
Recursive cte in the SP.
Tuf t-shirt
Striver comes everywhere
Time and number of steps are different 😅😃. Complexity vs network bs.
This also sounds like N+1 problem.
@gaurav is this similar to stored procedures?
This video was somehow missed by me and after watching it I feel how amazing and interesting it could be to solve such problems. Extremely addictive channel I would admit!🙌🏼 @gkcs