The biggest advantage of stores procedures from my perspective is that sometimes I have to create extremely long and complicated historical queries from multiple tables. If I just used queries from directly within the application, retrieving certain data can easily take 8 to 10 seconds, but using a stored procedure can easily cut that time down to less than a second. The above is an actual example that I came across a few years ago, and yes I was using Index as well. I don't always use stored procedures as in certain scenarios I prefer direct app access. But for large complicated queries (usually historical) I always stored procedures.
Con 3: You usually always have, as you pointed out, specific non standard sql in complexe apps anyway. Triggers are also on top of that. So I guess rewriting the stored procedures only add up a very tiny bit on it. We migrated from oracle to snowflake and didn’t had any stored procedures and it took us a full year. I guess the stored procedures if they would have been present would have added just one week by one person. In total 5 people were involved on and off in the year.
About version control, there are libraries that help automating the process of applying migrations, so you can have a solution that keeps the sql files and you can track the commits and automate the migrations across different environments with a proper CI/CD pipeline. I was very against stored procedures but I'm coming to seeing their value and advantages the more experience I gain
Hey there,keep up the great work in your channel. I have only a general enquiry as a newbie sql learner. Could i channel shopify live data into an sql application?(This should be an application developed for a company of 50 employees mainly for the sake of generating reports and analytics)) Is this an easy process to undertake as a sql beginner? What should i keep in mind and what are the tools or softwares that i should work with right from the beginning. For notice, I made my mind to work with microsoft sql server, also our company has a dedicated server. Thanks for taking care of my enquiry.
Good question! I'm not sure how you would get the live Shopify data into an SQL database. You may need to research how to get that data. If you can download it somehow, you can import it into a database and run analysis on that.
based on my personal experience... SP remains the best way to get the most out of your database. And I still have tolerance for those who put their SQL on their middle tier apps as long as they are not using ORM.
The biggest advantage of stores procedures from my perspective is that sometimes I have to create extremely long and complicated historical queries from multiple tables. If I just used queries from directly within the application, retrieving certain data can easily take 8 to 10 seconds, but using a stored procedure can easily cut that time down to less than a second. The above is an actual example that I came across a few years ago, and yes I was using Index as well.
I don't always use stored procedures as in certain scenarios I prefer direct app access. But for large complicated queries (usually historical) I always stored procedures.
Great example! Thanks for sharing. Good to hear it’s not something you necessarily use all the time but that they have their purpose.
Con 3:
You usually always have, as you pointed out, specific non standard sql in complexe apps anyway. Triggers are also on top of that. So I guess rewriting the stored procedures only add up a very tiny bit on it. We migrated from oracle to snowflake and didn’t had any stored procedures and it took us a full year. I guess the stored procedures if they would have been present would have added just one week by one person. In total 5 people were involved on and off in the year.
That's a good point and thanks for sharing your experience!
@@DatabaseStar thanks for taking time making high quality content!
About version control, there are libraries that help automating the process of applying migrations, so you can have a solution that keeps the sql files and you can track the commits and automate the migrations across different environments with a proper CI/CD pipeline. I was very against stored procedures but I'm coming to seeing their value and advantages the more experience I gain
Great point about version control! Good to hear about your experience as well
A really good primer of Stored procedures. Much appreciated 👍
Thanks!
Hey there,keep up the great work in your channel.
I have only a general enquiry as a newbie sql learner.
Could i channel shopify live data into an sql application?(This should be an application developed for a company of 50 employees mainly for the sake of generating reports and analytics))
Is this an easy process to undertake as a sql beginner? What should i keep in mind
and what are the tools or softwares that i should work with right from the beginning.
For notice, I made my mind to work with microsoft sql server, also our company has a dedicated server.
Thanks for taking care of my enquiry.
Good question! I'm not sure how you would get the live Shopify data into an SQL database. You may need to research how to get that data. If you can download it somehow, you can import it into a database and run analysis on that.
based on my personal experience... SP remains the best way to get the most out of your database. And I still have tolerance for those who put their SQL on their middle tier apps as long as they are not using ORM.
Thanks for sharing! Yes, stored procedures can be pretty useful!
@DatabaseStar please do a video about stored procedure vs ORM. Which one do you think is more superior?
very useful! thanks!
Glad it was helpful!
Very Nice Tip
Thanks!