Adding 4k values in where clause of query

[mhmmd_srf]

Hi team,

I am consuming data from HANA and my query is like below:

select * from table

where

cust_no in (1,2,3...)

I have around 4000 cust_no in excel . How dow I place that in my where clause?

Note: I tried with adding all data from table into power bi and loaded the excel on cust_no into PBI and have them join in desktop. But, the moment u will use Select * from table, this will throw memory allocation error at HANA end. Beacuse, the data volume of that table is huge.

Please suggest me an option to do that.

Thanks,

MS

[Shahid12523]

Don’t use IN with 4,000 values — it’s inefficient.

Better:

Upload the 4k cust_no values to a temporary HANA table.

Join in SQL:

SELECT t.*
FROM table t
INNER JOIN tmp_cust c ON t.cust_no = c.cust_no

Only select needed columns to reduce memory usage.

This pushes filtering to HANA and avoids memory errors.

[v-priyankata]

Hi @mhmmd_srf 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@DataNinja777 Thanks for the inputs.

I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @mhmmd_srf 

I wanted to check if you had the opportunity to review the information provided by user. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi  @mhmmd_srf 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[mhmmd_srf]

Yes.. I did... thanks a lot for your help..

[DataNinja777 · Accepted solution]

Hi @mhmmd_srf ,

You've run into a common and important database performance issue. Pasting 4,000 values directly into a WHERE CUST_NO IN (...) clause is not a good practice, as it can lead to very long query text, slow performance, and errors like the one you've seen. The most robust and efficient solution is to load your list of customer numbers into a temporary table on the HANA server itself and then perform a JOIN.

This approach works by first giving the database the small list of values you are interested in. You start by creating a temporary table in your HANA session specifically to hold your customer numbers. The SQL for this would look like the following, making sure the data type matches the cust_no column in your main table.

CREATE LOCAL TEMPORARY TABLE #MyCustNos (
    CUST_NO NVARCHAR(255)
);

Next, you can easily generate the required INSERT statements directly from your Excel file. If your customer numbers are in column A, you can place this formula in column B and drag it down: ="INSERT INTO #MyCustNos VALUES ('" & A1 & "');". This will produce the thousands of INSERT statements you need. After running all of these INSERT statements in your SQL client, you can then execute a simple, fast query that joins your main table against this temporary table, ensuring the filtering happens efficiently on the server.

-- First, run all INSERTs generated from Excel
INSERT INTO #MyCustNos VALUES ('123');
INSERT INTO #MyCustNos VALUES ('456');
-- ... and so on for all 4000 values

-- Then, run the final, efficient query
SELECT
    T1.*
FROM
    YourHugeTable AS T1
INNER JOIN
    #MyCustNos AS T2 ON T1.cust_no = T2.CUST_NO;

Since you are using Power BI, there is an excellent alternative that keeps you within that tool. You can leverage the Query Folding capability of Power Query. This is where Power BI intelligently translates the steps you take in its editor, such as merging two tables, into a single, optimized SQL query that it sends to the HANA server. This avoids pulling the entire massive table into Power BI and prevents the memory allocation error.

To do this, first load your Excel list of customer numbers into Power BI. Then, connect to your HANA table, but instead of loading it directly, select the table and click "Transform Data". In the Power Query editor, select your HANA table query and use the "Merge Queries" feature from the Home tab. In the merge dialog, select your Excel list as the second table and join them on the customer number columns using an "Inner" join. This tells Power BI you only want records from the huge table that have a matching customer number in your Excel list.

The most critical step is to verify that query folding is working. After the merge, right-click on the final applied step and check if the "View Native Query" option is active (not grayed out). If it is, you've succeeded. Power BI is sending a single, efficient SQL statement to HANA to perform the filtering on the server side, which is exactly what you need.

Best regards,