Transcript

00:08

Rapid SQL

Welcome to this webcast today on how you can architect SQL queries for multiple database platforms using a single GUI. My name is Anil Mahadev and I’m the principal solutions architect for the database data tools here at Idero. What we’ll be covering today is to first and foremost of housekeeping. If you have any questions, please pose your questions in the GoToWebinar Q and A box. If I can get a show of hands that you can actually see my screen and hear me clearly. Awesome. The goal of today’s presentation is going to be on how you can build queries on these solutions. Today we’ll be taking a look at Rapid SQL, which is our flagship component in RDB Par Studio product Fambo. Rapid SQL primarily helps you to boost database developer productivity by writing queries really fast, and it also helps you to build and look at any kind of optimization that you would want to do with your databases.

01:35

SQL Query Tool

Today we’ll be primarily focusing on Microsoft SQL Server, and I’ll also show you how a query can be built using Oracle as well. With that, one of the key benefits that we have here is what’s new in the new Par Studio families that we’ve added support for this product line, which is support for Amazon Redshift and for the DB Optimizer product we’ve added for Postgres. Here you can see we have Amazon Redshift here, and here we have support for Postgres. Now what I’ll do is I’ll go ahead and switch screens and I’ll go ahead and log into my Rapid SQL environment. Can everybody see my Rapid SQL environment here please? Okay, so on the left hand side, one of the fundamental aspects of any database professional is to come into the database and connect and start writing the SQL queries here what we have is the ability for you to actually achieve that in multiple ways.

02:50

SQL Query Builder

One of the primary ways is to actually use our IntelliSense that we have. You can actually go into here and look at from the Schema level. Here I can actually just do a select start from Sales Lt and it should go ahead and populate the list of tables. I can do it in this way, or I could actually go ahead and start building the query using the query builder. That’s what we’re going to be taking a look at here. Here I’ll launch the query builder, and here I can actually go ahead and select a group of tables and I just say add, then Rapid SQL will automatically go ahead and add those tables, including they’re related, they’ll actually have those relationships. What you can do is you can actually go ahead and start visually building them. Here if I put in like address ID to this, so I can actually drag a relationship like this to that.

03:59

Here if I go and drag the relationship with your primary key table to the customer ID here, and then there you get the relationship. The same thing you can do here as well. Here you can go ahead and add it to one of them. Finally here, from this customer table to the customer address, you can add the primary key foreign key relationship. As I’m building this, you will see that at the bottom screen that it’s actually giving me the query. What I can do is I can just copy this and then I can go back to my ISQL window and paste it. I can also go ahead and format this. Let me just go ahead and format the SQL looks pretty good. I can also go ahead and add this to my script library. The script library enables you to add user based scripts. Here for example, I’m looking at the Adventure Works SQL query, so I can actually go ahead and do that.

05:10

Oracle GUI

Once I’m done with this, I’m brought with this dialog that I can actually instantiate at any moment’s notice. Here you can see I have a hotkey for the IQL window. Here if I do control seven, and I have launched Rapid SQL for the first time, and then I just get control Seven, I get this query right away and then I can actually go ahead and run this. If the condition matches, it will give me the result set. If it does not give me a result set. Now, the same thing I can do with Oracle, what I can do is I can go into my tables here, and once I’m connected to it, you will see that I have a huge list here. What I can do now is I can actually go into the query builder, it’s actually going to go ahead and pick from whichever schemas I need from here.

06:17

Write SQL Query

I have a lot of schemas, so I can pick from which or I have. Here, if I look at one of the schemas here, I don’t have any tables I can pick from, let’s say MD data here, let me go to one of the other ones. Okay, good. Here we have some audit trails and some of these here, so I can actually go ahead and build this here as well. The purpose of us showcasing this is to enable you to appreciate how fast we can go ahead and build these queries within Rapid SQL, and the same holds true for any of the solutions. Let’s say that you’re a developer and then you wanted to go ahead and your manager comes back and says, anil, I need this to be done with this specification. You can go into Rapid SQL and go ahead and start building the query as per the specification and execute those queries.

07:26

So here it’s running in a VM. Apologies for the slowness but it’s almost done here. It again depends on the number of columns that each table has so that’s based on how it brings in all the information. While it’s doing this, let’s go ahead and launch one more instance of rapid SQL and I will show you some other tips and tricks with regards to this. Once I’m done with it, we should be able to look at the square here. Now it’s populating this here as well. Let me just go ahead and open this up. Here we have SQL server and I go ahead and connect to this as well. You will notice that you can also go ahead and kickstart things by instantiating this with a new project and if you’re having a version control system that you want to go ahead and put all of your queries, you can do so.

08:27

You can actually go ahead and say a new project and you can just call it as AdventureWorks Lt. From here I can actually create this from a database. Here I’m choosing the server and here I’ll just pick the Adventure Work Celt database and from the schemas I’ll pick the sales Lt schema because that’s the one that has all the tables and you can choose whatever you want. Here I’ll just choose I just want the tables and the related options here I can actually just do a select all if I choose next here’s where you can actually go ahead and choose what options you need. You can either choose ANC, you can choose Utf or any of the file encoding and then you can also generate drop scripts. If it does not exist, you can do that as well and you can also retain the owner or you can change the owner to whatever is going to be this.

09:32

If you already have let’s say a DVO so I can actually go ahead and do this or you can just say retain order and uncheck that. Now what I can do is I can hit finish, it’s going to go ahead and generate the actual script for me here and push those changes to an IQL window. Some of the tables, if it does not find it’s fine, but you can go ahead and generate a report. Here you can see one of the tables it got generated. You can see all of this here in action. Once that is done, you should be able to go ahead and hit continue and it pops up this list. You can go ahead and add this and double click on it and start working with this here as well. One of the other key benefits about building these multi architecting these SQL queries in multiple databases is that right now within the same project I can actually go ahead and add database objects like directly from here and I can also go ahead and choose some database objects that I would like to have it connected.

10:53

From here I can just choose which or schemas I want. Here, if I just choose the SIS schema, and here I can go ahead and choose a couple of tables. From there, I can just pick one of these. Again, just go ahead and hit finish. It’s actually reverse engineering the code back and then once it’s done, it should give you the option to open this up as part of the same project. Can I just get a show of hands to make sure that we are all on the same page that you’re following what I’m demonstrating? Perfect, thank you. The workflow is very important here because for any organization, especially since we’re living in a very DevOps centric area, this can really help you architect those queries and put that as part of your DevOps pipeline and connecting to your version control into your CI/CD pipelines.

12:19

Rapid SQL does a fantastic job of generating these queries. Again, like I said, depending upon the size of the columns and what it’s going through, you can actually see what it has there as well. Not all of them will be processed. In most of the time, depending upon how you have and what permissions you have here, I wanted to simulate that there are certain permission challenges that we do not have, so that’s why it’s giving you these error messages. Not to worry, if you’re having the appropriate permissions, you should be able to get this working. I’m going to go ahead and pause this out of here and once I’m done with it, I should be able to go ahead and extract it. The next thing that we’re going to be covering is the ability for you to report on some of these. For example, here, it brought in this year as well.

13:17

RDBMS

Again, I can go ahead and link this from here. Link these two here. Now here you can see if I wanted to do select couple of these, go ahead and select here as well. There you go. Now that I’ve built this, I can do a control A and I can do a control B here as well. I can paste this and this will be all my Oracle related code. I can probably just insert a comment here somewhere, just say Oracle code. Now what’s really cool about this is as you’re developing this, you can put this into multiple workspaces. Now in the new workspace, I can instantiate this and start working with my SQL Server stuff. I can just switch between workspaces and just go back to my Oracle stuff. If I want to work with SQL, I can go to my SQL workspace. That’s where you can enhance this and design this queries for multiple databases.

14:44

The other option that a lot of people would like to take a look at is our explain plan. Here you can use our explain plan on how you want to go ahead and do it. Now that I’ve done this, I can actually just execute this along with an explained plan. It’s going to go ahead and generate here. Let me go ahead and do this here and we’ll execute that. Now that I’m doing this here, I can actually go ahead and leverage this here as part of it. Once that is done, you can see that we have the explain plan. You can also go ahead and view this as a tree as well as a graph as well. Now what I can do is I can actually leverage this here as well into a lot of other I can save this plan as well as part of my query optimization.

15:56

I can save it as a SQL script. I can just save it like that. Once that is done, I can actually go ahead and leverage any of these statement statistics that will help me to build much better queries as we move forward. Today’s webinar is going to be a little shorter than our usual webinar. Right now I would like to go ahead and pause and ask if we have any questions, please. Simona, do we have any questions? No, I don’t see any questions. Okay, so just a quick show of hands. Did you folks find this useful and did you have any other feedback for us? Please type it in the Q and a box or in the chat window and we will incorporate that for our future webinars. For those of us joining us here in the United States, we wish you and your loved one so very happy Thanksgiving from Madeira.

17:07

If there are no other questions, I would like to end this webinar here and would like to thank you all for attending and we’ll see you in the next one. Thank you and have a fantastic weekend.