Get AI-Ready With Erik: A Little About Finding Related Content
Video Summary
In this video, I delve into user search handling in AI systems, specifically focusing on how database administrators can manage and utilize user queries effectively. By leveraging embeddings generated by application layers, we explore a workflow where user searches are converted into vectors and stored in a searchable table. This allows for efficient similarity searches using vector distance functions to provide relevant results based on the user’s intent rather than relying on traditional keyword matching. I also discuss practical considerations such as ensuring unique search phrases through hashing, handling embedding data from application layers, and optimizing query performance with minimal impact on cardinality estimation.
Full Transcript
Erik Darling here with Darling Data, and we are going to continue our AI preparedness and talk a little bit about dealing with user searches. This is, of course, all brief snippets of the content that I have available in my Get AI Ready with Erik course. You can get it from training.erikdarling.com. Right now, it’s a hundred bucks off with the coupon code AIREADY. The link that you see is a good way to get it. And the link that you see above you floating in SQL Server Management Studio is down in the video description for your tender loving clicking. So we’ve got that going for us. Now, so far, what we’ve done is used existing embeddings as query vectors. Right? We said, hey, is any of this other stuff similar to this thing? Which, you know, can be useful. Right? That can be a useful thing to measure. It can be useful to pre-generate embeddings and say, is this thing similar to this other thing. But, you might have users in your database. Right? Shocking. Crazy, I know. Where we, the general EBA public may need to deal with these users. And these users may have their own specific questions to ask. So what we’re going to look at here is a workflow where the application does the embedding for us, right? So a user will type in a search query into some form in the front end. And then we’re going to pretend that like a service in the application layer generated an embedding based on that user search query and stuck it in the database. And now it’s our job to take that user search query and, you know, see if there’s anything similar to it.
Right? You know, developers love their app layers. So we’re going to app players. Whatever. But this video will focus mostly on like, you know, how you as a DBA might take those search phrases, stick them in a table and then do a search using them with the vector distance function. So you likely want a table that looks something like this, right? And you may, I don’t know, like, like, if I had to like really design this thing and not just have like a simple demo table, I’d probably want like a user ID or something else where I could like uniquely identify the user and their search queries. So when I go and get whatever someone sent in, I could, you know, find the right thing and all that. But for us, this is good enough right now where we’ll have the, you know, the general phrase that they sent in their search text. We’re going to hash that phrase so that we can make sure that we only have unique stuff in there. We don’t want, if someone has already asked the same question, then we would want to say, hey, we already have something. We already have that. Like, we won’t even have cache search results somewhere in our app player. And we might be able to use that instead.
We will have the embedding that was generated again in the app player. I’ve prefilled this table with the embeddings. We’re pretending that the app player is doing some magic and inserting into this table. And all that other good stuff. So if we look at what is currently in our search phrases table, these are all searches that users have submitted, right? If we zoom in on this, how do I make SQL queries faster? JavaScript array message. What is a foreign key? Python file handling. There’s a lot of stuff in here that we could probably use to, I don’t know.
These are all questions that people cared about. Loop without cursor SQL. How dare you? Cursors are great. So we’re going to start with this. Hey, you did it. I’m going to start with something here. Just to make life a little bit simpler, we will declare a query vector local variable and we’ll assign an embedding to that local variable. Normally, the local variable thing would be a bit icky. All right. If you’re a skilled SQL, skilled T-SQL practitioner, perhaps you’ve watched my other videos.
Perhaps we have, you have bought my prior trainings. Perhaps you have attended some of my prior trainings in person. You will know that local variables can often have deleterious effects on cardinality estimation. But in this case, it doesn’t matter because it is not an aware clause. It is just being fed into this vector distance function.
And it has no effect on cardinality estimation or anything else. So we don’t have to worry too much about that there. You could throw a recompile hint on this if you wanted. It would change absolutely nothing for you. But what we’re going to do is we’re going to ask, we’re going to look up the phrase, how do I make my SQL queries faster? We’re going to assign the embedding that was generated in the app layer for that search query. And then we’re going to find the top 10 queries, top 10 posts that might be similar to that. All right. So again, we are using the vector distance function. We are using the cosine equationatory things.
And here we are comparing the embedding in the post embeddings table to the query vector. And then we are ordering by the distance alias in ascending order so that we get back the most relevant results first. And we could think about other ways of doing this, right? Like we might like make maybe score would be a valid thing to also bring up, like maybe it’ll be like a little bit more high quality content or something. But for now, we’re just going to stick to just finding purely what is the most similar.
And if we look over here, we get some rather low scoring things back, right? So these might be the most similar, like, like I was saying, these might be the most similar things, but they might not be the most high quality things. We don’t even have a guarantee that like this thing has an answer, right? Because we’re just comparing titles.
We’re just saying, is the title of this thing similar to the title of any other things? What other titles is this title similar to? And these titles are quite similar, aren’t they? Right? Like how optimize SQL query, make it faster. How can I speed up the SQL query twice? Two different IDs with the same damn post title.
Speed up the SQL query to my SQL. Well, stop using my SQL. Don’t be stupid. SQL query is taking too long, right? So like all this stuff, pretty similar. And this is like the kind of, I mean, what I think is the kind of neat thing about vector search is that you can, you can search based on like what a user meant, what a user’s intent was.
And for DBAs, I mean, like think of all the, think of all the times you’ve yelled at developers for throwing in like, like wild card string searches, like, like where title, like percent something percent or title and title, like percent something percent. Like think of all the times you’ve screamed at them for this and like how you can now just go to be like, well, now we don’t have to do that anymore.
Now we can just take this magical embedding and compare it to this other magical embedding. And we can figure out how similar those embeddings are without having to do a bunch of wild cards, wild card string searches. It’s kind of a nice thing for DBAs because you no longer have this like, like, well, how are the developers going to figure out if this thing is like this or this or this or this or this or this or this or this or this without like, like just ruining your life, right?
And giving you, making steam come out your ears. It’s not a good feeling. Trust me. I’ve had steam go in. I’ve had steam come out. It’s not.
It’s cleansing, but it’s painful. Let’s, let’s try another one using something similar, right? We’re going to take this query vector for what is a foreign key.
What is a foreign key? Why would I stick it in a lock if I know it’s not going to work? So it’s from a foreign country and foreign countries all have far different locking mechanisms than American locks do.
But if we run this query, right? Well, now we’re going to do the same thing where we’re going to, you know, get our search phrase. We are going to plug that into the vector distance function.
We are going to compare the, the, the embedding for that, for our, what is a foreign key search phrase to other things in the post table. Right? And we’re going to get back some pretty similar stuff here.
Right? Right. What exactly is a foreign key? Foreign keys, foreign keys. What do they do for me? How can foreign keys benefit me?
Right. And then, but like all this stuff is generally about foreign keys. And this is without having to do like, again, like the like percent foreign key percent, you know, inevitably having to worry about case sensitivity or something like that. But we, we, we get a bunch of stuff back about foreign keys.
Now, again, we have not established like a, like a high quality benchmark for this. Right? Because the scores for this are all over the place.
11, 1, 7, 15, minus 1, 0, 2. Right? There’s like, there’s some stuff in there where, you know, you might want some of the higher scoring stuff to maybe show up a little bit higher in the search results. We’ll talk about that later and how to sort of like weight things differently.
Because, you know, distance, well, it is a good, you know, indicator of similarity between two, two, two, two strings. Right? The, the, the, the vectors and the embeddings, they’re a good indicator of like similarity there, but they’re, they’re not like, they’re not a terrible, good, terribly good measure of like, is the content after that quality?
Like, like it could be completely wrong. It could be an AI generated response, which is full of malarkey. Right?
If you’ve ever asked the AI a question and gotten a bunch of stuff back where you’re like, none, none, none of that is right. None of, none of that is a key insight. Thank you.
You, it doesn’t do anything to tell us like, like how high quality, like the information after that is. Right? It can tell us how similar these things are. And these things are very similar.
They all say foreign keys in them. Right? Granted, there’s a little bit of a spread there between like MySQL and like, you know, general databases and stuff. But what you get back is at least, you know, foreign key related without having to search like percent, foreign percent key percent over and over again. Like doing traditional keyword searches that often looked like this.
Right? Like SQL performance and foreign in key and stuff like that. And we all know how we feel about this. And while you can get, you know, stuff that says, you know, insert, we can get stuff back that is SQL and performance.
You know, it, it, it, it does not give us like, we’d have to give it way more things, way more tokens, if you will, in order to narrow it to think something that we care about, like SQL Server. And then like maybe a SQL Server version and then like, I don’t know, is this, we just like, like how many likes are you going to throw in there before you’re like, ah, I finally found what I was looking for. It’s a lot of likes, a lot of likes, unlike my posts.
It’s a lot of likes. All right. Thank you for watching. Hope you enjoyed yourselves.
I hope you learned something. And again, if you are interested in the full course content, it is all available for sale and purchase at that link, which is down in the video description. The coupon code embedded will get you 100 smackaroos, dollaroos, buckaroos.
I’m going to stop there. Off the, off the price. So, thank you and goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.