CODEWITHSUNDEEP
c#oracle-database
stephenbayer
stephenbayer

Reputation: 12431

handling large amounts of data to include in an oracle select statement

Recent bug report states that a method being called is crashing the service causing it to restart. After troubleshooting, the cause was found to be an obnoxious Oracle SQL call with thousands of strings passed. There is a collection of strings being passed to a method from an external service which often is more than 10,000 records. The original code used a where clause on the passed collection using the LIKE keyword, which I think is really, really bad. 

public IList<ContainerState> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 
    String.Format(@"Select CTNR_NO, CNTR_STATE FROM CONTAINERS WHERE CTRN_SEQ = 0 AND ({0})", 
        string.Join("OR", containerNumbers
                .Select(item => string.Concat(" cntr_no LIKE '", item.SliceLeft(10), "%' ")))
    );
    return DataBase.SelectQuery(sql, MapRecordToContainerState, new { }).ToList();
}

Clarification of in house methods used which may be confusing:

DataBase.SelectQuery is an internal library method using generics which gets passed the sql string, a function to map the records to .NET objects, and the parameters being passed and returns an IEnumerable of Objects of type retuned by the Mapping function.

SliceLeft is an extension method from another internal helper library that just returns the first part of a string up to the number of characters specified by the parameter.

The reason that the LIKE statement was apparently used, is that the strings being passed and the strings in the database only are guaranteed to match the first 10 characters. Example ("XXXX000000-1" in the strings being passed should match a database record like "XXXX000000-8").

I believed that the IN clause using the SUBSTR would be more efficent than using multiple LIKE clauses and replaced the code with:

public IList<ContainerRecord> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 
String.Format(@"Select CTNR_NO, CNTR_STATE FROM CONTAINERS WHERE CTRN_SEQ = 0 AND ({0})", 
              string.Format("SUBSTR(CNTR_NO, 1, 10) IN ({0}) ", 
                            string.Join(",", containerNumbers.Select(item => string.Format("\'{0}\'",  item.SliceLeft(10) ) ) )
                            )
              );
    return DataBase.SelectQuery(sql, MapRecordToContainerState, new { }).ToList();
}

This helped slightly, and there were fewer issues in my tests, but when there are huge amounts of records passed, there is still an exception thrown and core dumps occur, as the SQL is longer than the server can parse during these times. The DBA suggests saving all the strings being passed to a temporary table, and then joining against that temp table.

Given that advice, I changed the function to:

public IList<ContainerRecord> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 

@"
        CREATE TABLE T1(cntr_num VARCHAR2(10));
        DECLARE GLOBAL TEMPORARY TABLE SESSION.T1 NOT LOGGED;
        INSERT INTO SESSION.T1 VALUES (:containerNumbers);

        SELECT 
                DISTINCT    cntr_no, 
                            '_IT' cntr_state 
        FROM 
                tb_master 
        WHERE 
                cntr_seq = 0 
            AND cntr_state IN ({0})
            AND adjustment <> :adjustment
            AND SUBSTR(CTNR_NO, 1, 10) IN (SELECT CNTR_NUM FROM SESSION.T1);
";

        var parameters = new
        {
            @containerNumbers = containerNumbers.Select( item => item.SliceLeft(10)).ToList()
        };
    return DataBase.SelectQuery(sql, MapRecordToContainerState, parameters).ToList();
}

Now I'm getting a "ORA-00900: invalid SQL statement". This is really frustrating, how can I properly write a SQL Statement that will put this list of strings into a temporary table and then use it in a SELECT Statement to return the list I need?

Upvotes: 0

Views: 838

Answers (1)

ivenxu
ivenxu

Reputation: 709

There are couple possible places could cause this error, it seams that the "DECLARE GLOBAL TEMPORARY" is a JAVA API, I don't think .net has this function. Please try "Create global temporary table" instead. And, I don't know whether your internal API could handle multiple SQLs in one select sql. As far as I know, ODP.net Command class can only execute one sql per call. Moreover, "create table" is a DDL, it therefore has its own transaction. I can't see any reason we should put them in the same sql to execute. Following is a sample code for ODP.net,

using (OracleConnection conn = new OracleConnection(BD_CONN_STRING))
        {
            conn.Open();
            using (OracleCommand cmd = new OracleCommand("create global temporary table t1(id number(9))", conn))
            {
                // actually this should execute once only
                cmd.ExecuteNonQuery();
            }

            using (OracleCommand cmd = new OracleCommand("insert into t1 values (1)", conn)) {
                cmd.ExecuteNonQuery();
            }

            // customer table is a permenant table 
            using (OracleCommand cmd = new OracleCommand("select c.id from customer c, t1 tmp1 where c.id=tmp1.id", conn)) {
                cmd.ExecuteNonQuery();
            }
        }

Upvotes: 1

Related Questions