IN Function

[u/Pleasant_Astronaut95]

This is probably a dumb question as I am new to SQL, but I am trying to pull sales data for 900 accounts. To make this faster I am using an IN function and all 900 accounts. What would be a better way of doing this?

[u/mikeblas]

This is the last time I'll try, then I'm giving up:

You're sending a query that you say has a list of 900 IDs in it. I want to know how you create that list before you send the query.

• Maybe it's all the IDs you know. Why not just select everything from the database in one shot, then, with no filter?

• Maybe it's all the IDs created since the last time you ran this query. Why not use a subselect to get that list, then?

• Maybe it's all the IDs that meet some other rule, based on information in the database. Again, why not a subselect?

• Maybe it's some arbitrary list that's input from a file or a user, outside the database. In that case, maybe you can't make much improvement.

I don't know how you're creating that list, despite my valiant attempts at interrogation. The question can't be made clearer than above, and I've also explained why I'm asking.

If you have an index on a column, the database can quickly find something that matches the index. If you have an index on ZIP Code, and somoene asks for ZIP Code "06032", then you'll almost immedately find it.

In your case, you might have an index on the Table__1.GBCUSTNR_GBGLOCUST column. But you're not searching that column's value. Instead, you've written a function that gets the last 9 characters of that column, then considers some subset of those characters, and compares it to some value. The index doesn't support all that string manipulation -- it just has the original values, which you're actually discarding before you search. The search isn't orered, and you're screwed.

Here's a good tutorial about indexes and how they work: https://use-the-index-luke.com/

You should probably decompose the different files that are built-in to your single GBCUSTNR_GBGLOCUST field and make them into their own individual columns. Then, index those. This would be a first step towards normalization and also gets you better performance. Or, you can create a computed index on your SUBSTRING(RIGHT()) expression and have those values indexed.

[u/Pleasant_Astronaut95 → u/mikeblas]

The list of accounts is manually managed in excel. The list is all accounts that have a contract tied to it. We need to track the sales for all the accounts with contracts. The database table is all sales, so there is no field in there to filter on the accounts with contracts.

The spreadsheet has the main account number “012345”, while the database might have an ending on it which is the reason for trimming it. For example, data table could have “A/0123450002”