hi everyone in this video I'm going to introduce the topic of database indexes now there are a couple of important reasons that you as a servicenow developer or administrator need to have at least a basic understanding of how indexes work number one custom tables when you create custom tables in servicenow you'll need to ensure that the queries to the data in those tables is performant because the tables that you create will not come automatically with their own indexes number two performance of all queries in general so as an administrator of service now you'll need to ensure that your instances are running smoothly and that page loads and background processes are not taking too long because remember your database the tables the data are being queried every minute every hour every day now although servicenow might not be considered purely a full stack environment because we don't have direct access to the database we need to have at least a basic understanding of data modeling and performance of our queries now one of the main ways you can improve query times is through indexes purpose of an index in a database is fundamentally the same as an index in the book a book's index contains a list of subjects which reference page numbers where those subjects are found indexes and tell us exactly what line in that page or the position in that line but we're able to use the index to locate information more quickly similarly a database index will allow a database to locate records in a table more quickly if a specific field has been indexed for example the make of a car the index will store the location of records for those different makes the database can then use that index to return results more quickly where the make field has been used in the search query my intention with this video is not to tell you everything there is to know about indexes there are plenty of resources out there and I provided some links in the description below to the ones that I found useful so I think the first thing that we can do to get deeper into this topic is just to go ahead and look at a simple example in our servicenow instance for our demonstration we're going to use this table here vehicles as you can see it stores over 600 000 records at the moment so you can imagine that queries to this table could be potentially quite long now I know this is the case already because this table is relatively new and I have not created any indexes for it yet if I go to servicenow Studio to the table record and scroll down there is a related list here called database indexes Now by default the system will create this one always with every custom table for the primary key which is the sysid in every table in servicenow um but if we go ahead in our table and just do a simple query let me just do a query here where the make is Volvo okay and run that as you can see it's taking a while and there we go okay so that query probably at least two three seconds long and then you add the time for the application server to do its thing for the browser to actually render the page and load the records uh yeah it all adds up you can come down here to the bottom right hand corner to see the total response time and as you can see here was over 10 seconds so over 10 000 milliseconds in other words over 10 seconds all right so but as we can see with this response time here it's actually made up of different components we've got one here called server which is actually where the query will be contained within so we can actually see here exactly how long the query itself took but there is a way you can actually time that and I just want to show you that because we want to make our tests that we do as accurate as possible so what I'm going to do is use a script to time the query itself so I've got one here ready to go the first thing I'm going to do in this script is to flush the cache okay so every query that I do I just want to make sure that it's not using the case that nothing else is kind of going to get into way in determining how fast or how slow the query is going to perform so it's going to flush the case first of all using this Glide system method cage flush and then I'm going to use this undocumented method here Glide stopwatch okay you will find references to it in the servicenow community and on various servicenow support pages and this will basically start a stopwatch essentially and then we perform our query of that table using the make Volvo and then at the end I'm going to Output how long that query took okay so uh I will copy that and I'll just come into another browser tab here and go to my background Scripts I'll pop it in there now what I'll have to pay attention to here is the scope it will need to be Global because a couple of the methods that I'm using here can only be run in the global scope so the cache flush can only be run from Global and the Glide stopwatch likewise so let's run that okay so now that that's finished uh we've got some messages here about the result of the cache flush what I'm really interested in is this line here the output of the script the query duration here okay so it's over two seconds two and a half seconds approximately okay I could actually go back we could just run it one more time just to make sure that that duration was accurate that it wasn't an anomaly and we can see here that the time here was 2.3 seconds okay so we're looking at well over two seconds for that query now if we were to leave this as it is without doing anything else this is going to create all kinds of headaches and problems for a simple query based on the make of the car you know we've got users who want to load lists based on a certain query if we have flows scheduled jobs other background processes going on that are querying that table you can just imagine how long all these processes are going to take and how lackluster the performance of your instance will be specific to that table but it can also impact the performance of your entire instance in some cases so the easiest way to resolve this issue that we've got here is to create an index so let's go ahead and do that so I'll go to servicenow Studio come down to the bottom here for my table record again under database indexes click on new and we'll create an index for the make field so I will select that now I could leave that as it is okay but I'm just going to go one step ahead here because I'm going to come back to this a little bit later in the video I'm actually going to add the model and the city as well okay in that order okay so let's go ahead and create the index I don't want to be notified so click ok I'm not going to create a unique index that's if you want to ensure that if you have multiple values that together should form a unique record then you can do that here all right so I will close that and I'll just refresh this list okay and now we can see we've got the index created it takes just a few moments to do that all right so now that we have an index for the make column and these other two as well we'll go ahead and test our query again just on the make field so let's come back to our background script let's come back here and we'll just execute exactly the same query once more okay so if we have a look at the output here of the script it is 53 milliseconds which is yeah a whole lot faster than two and a half or 2.3 seconds okay a significant Improvement all right let's come back let's execute it once more just to make sure again that that wasn't an exception all right and again uh 108 or 183 milliseconds I'm sorry a little bit slower than last time but still significantly faster than our initial two tests that we performed okay so if I were to come back here now and just refresh this list by clicking on the filter you can see that was significantly faster if I come back to the response times here okay that was just over two seconds in total for for everything uh including the the query and the network and browser rendering Etc but you know that's uh a whole lot faster than 10 seconds okay so a real easy quick win just by creating an index on the make field okay everyone so thank you for watching since I recorded this video I decided to break it up into two parts in this first part we introduced the concept of indexes as well as provided a simple demonstration of how indexes can significantly improve query times however if you would like to delve a little bit deeper into indexes I also invite you to watch part two where we will one take a look at the slow queries log to determine for which queries we need in index for two examine the explain plan which will tell us a little bit more about the query itself including if an index was used if so which one and even what part of the index was used and third and lastly we'll also take a look at composite indexes so again thanks for watching and see you next time [Music]