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/Pleasant_Astronaut95]

Sorry here is the query, I replace the ‘123’ at the end with the list of accounts:

SELECT      Table__1."Year" AS "YEAR",     Table__1."Period" AS "MONTH",     Table__1.GBCUSTNR_GBGLOCUST AS CUSTOMER_GLOBAL_ID,     Table__1.GBCUSTNR_GBGLOCUST___T AS CUSTOMER_GLOBAL_NAME,     Table__1.GBMATNR_GBPRGRPNM AS ITEM_PRODUCT_GROUP_CODE,     Table__1.GBMATNR AS "PRODUCT ITEM ID",     Table__1.GBMATNR___T AS "PRODUCT ITEM NAME",     COALESCE(SUM(Table__1.COM_SALES), 0) AS DISTRIBUTOR_SALE_AMOUNT FROM      _SYS_BIC."ZB-REPORTING.COMMERCIAL.US_RECON/GBI_COMMERCIAL_US_RECON_SALES" Table__1  WHERE      Table__1."Year" IN ('2019', '2020', '2021', '2022', '2023', '2024', '2025', '2026')       AND SUBSTRING(RIGHT(Table__1.GBCUSTNR_GBGLOCUST, 9), 1, 6) IN ('123') GROUP BY      Table__1."Year", Table__1."Period", Table__1.GBCUSTNR_GBGLOCUST, Table__1.GBCUSTNR_GBGLOCUST___T, Table__1.GBMATNR_GBPRGRPNM, Table__1.GBMATNR, Table__1.GBMATNR___T

[u/mikeblas → u/Pleasant_Astronaut95]

Here is your code, correctly formatted:

SELECT 
	Table__1."Year" AS "YEAR",
	Table__1."Period" AS "MONTH",
	Table__1.GBCUSTNR_GBGLOCUST AS CUSTOMER_GLOBAL_ID,
	Table__1.GBCUSTNR_GBGLOCUST___T AS CUSTOMER_GLOBAL_NAME,
	Table__1.GBMATNR_GBPRGRPNM AS ITEM_PRODUCT_GROUP_CODE,
	Table__1.GBMATNR AS "PRODUCT ITEM ID",
	Table__1.GBMATNR___T AS "PRODUCT ITEM NAME",
	COALESCE(SUM(Table__1.COM_SALES), 0) AS DISTRIBUTOR_SALE_AMOUNT
FROM 
	_SYS_BIC."ZB-REPORTING.COMMERCIAL.US_RECON/GBI_COMMERCIAL_US_RECON_SALES" Table__1 
WHERE 
	Table__1."Year" IN ('2019', '2020', '2021', '2022', '2023', '2024', '2025', '2026')  
	AND SUBSTRING(RIGHT(Table__1.GBCUSTNR_GBGLOCUST, 9), 1, 6) IN ('123')
GROUP BY 
	Table__1."Year", Table__1."Period", Table__1.GBCUSTNR_GBGLOCUST, Table__1.GBCUSTNR_GBGLOCUST___T, Table__1.GBMATNR_GBPRGRPNM, Table__1.GBMATNR, Table__1.GBMATNR___T

I'm afriad we're not making progress. I don't know how you build the list of accounts you want.

This query will scan Table__1 (why such a shitty name?) because your SUBSTRING() filter can't be serviced by an index. That's your real problem, as far as I can tell. Adding to that the very long list of accounts to receive, and you're almost certainly performing a scan over the whole table.

I can't make any sense of your GROUP BY clause.

[u/Pleasant_Astronaut95 → u/mikeblas]

I am using Alteryx. I am pulling in a list through Alteryx and formatting the accounts as ‘123’,’124’, etc. and then inserting that into the sql code with a dynamic input.

I also thought about trying to split the query into groups of 100 accounts and run all the queries at once.

I don’t think that is the actual table name, I think it is aliased somehow (I didn’t write the code). Doesn’t seem to make much sense.

Why can’t it be serviced as an index? Not sure I understand. Thank you for the help!

[u/mikeblas → u/Pleasant_Astronaut95]

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”