ODBC: ERROR 3815: Join inner did not fit in memory

ozi_samur

Hi,

When I run a query which joins via VARCHAR column that I run in isql ( ODBC ) then I am getting the error :

ERROR 3815:  Join inner did not fit in memory 

But if I run same query in vsql client it completes without an error. I am sure there is not any other query and I run the same query with same user. Is there any some memory configuration in ODBC connection?

Thanks.

Prasanta_Pal

1. Run the statistics if not run, the inner table should be a dimension table. if no statistics are run, vertica may not determine which table should be inner.

2. Try for a mergejoin ( see the projection design and the join column)


3. If it is hash join,

A hash join to an inner relation could require additional memory than was initially allocated to the query. If memory is available, the hash join executes the query without needing to spill to disk.  If memory is not available, the query automatically re-executes with join spill enabled (ENABLE_JOIN_SPILL), but query performance will be slower.

Two ways you can do this:

a. At the session level, Select add_vertica_options('EE','ENABLE_JOIN_SPILL');

b. At the statement level, select /*+ set_vertica_options(EE,ENABLE_JOIN_SPILL) */....


There are several ways of enabling hash join spill in Vertica:

c.      Statement level:   Select /*+ set_vertica_options('EE','ENABLE_JOIN_SPILL') */….

d.      Session level:  Select set_vertica_options('EE','ENABLE_JOIN_SPILL');

e.      If using 7.1, then at the database level:  select set_config_parameter('EEVerticaOptions','ENABLE_JOIN_SPILL');

ozi_samur

You could not answer my question. As I said with the same user it is completed in vsql client without a problem. When I use odbc, or python code I am getting that error. How vsql completes the query?

I set the above parameters but it did not change anything. I am getting the same error again.

id10t

Hi Ozi!

Define trace file and DEBUG mode in vertica.ini file, send query with ODBC driver and post a DEBUG info from log.

We have to see an ODBC query to understand a difference between vsql and ODBC. And also post your odbc.ini config (hide sensitive info like UID/PWD).


Example

vertica.ini

[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib=/usr/lib/x86_64-linux-gnu/libodbcinst.so
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp

Execute with pyODBC

In [3]: sql = sql.strip()

In [4]: print sql
SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };

In [5]: cxn.execute(sql)
Out[7]: <pyodbc.Cursor at 0x7f9be8016630>

Trace file:

Jan 13 15:03:37 INFO  3954849600 StatementState::InternalPrepare: Preparing query: SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Original Query: SELECT 
    t1.a,
    t2.b, 
    t2.c
FROM
    {IJ tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a };
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Query is issued as ExecDirect
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Query: SELECT t1.a, t2.b, t2.c FROM   tbl AS t1 INNER JOIN tbl AS t2 ON t1.a = t2.a   ;
Jan 13 15:03:37 INFO  3954849600 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x228fd90_0
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_WVARCHAR
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_BIGINT
Jan 13 15:03:37 INFO  3954849600 VPQResultSet::SetupColumns: column type is SQL_TYPE_TIMESTAMP

Cheers.