PDO, unixODBC and setup option for next query

vaitac · June 2015

Hello Everyone,

I have following query:

WITH 
basic AS (...), 
purchases AS (...),
...
activity AS (...)
SELECT
  ...
FROM schema.each_day dr
LEFT JOIN basic ON dr.name = basic.name
LEFT JOIN purchases ON dr.name = purchases.name
...
LEFT JOIN activity ON dr.name = activity.name
WHERE
  dr.name BETWEEN '2015-01-01' AND '2015-05-31';

And using Vertica Community Edition v.7.1.1. It has such option:

SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');

Setting this option really improves my query performance, query works five (5) times faster.

I've checked that from console client vsql.

Now I would like to fetch query results from my PHP script. I'm using PDO and unixODBC.

I was playing with PDO attributes like:

PDO::ATTR_EMULATE_PREPARES
PDO::ATTR_AUTOCOMMIT

I've tried to create single instance of PDO and then call each query one by one but nothing happens:

(Yes, I know that this option can be setup by superuser only and I'm using superuser credentials)

$r = $instance->query("SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION')");    
$r = $instance->prepare("SELECT show_current_vertica_options()");
$r->fetchAll();

last fetchAll() return empty array like no option set. Here is what I've expected to see:

db => SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
 add_vertica_options 
---------------------
 Options Set
(1 row)

=> SELECT show_current_vertica_options();
                          show_current_vertica_options                          
--------------------------------------------------------------------------------
 Opt Vertica Options
--------------------
ENABLE_WITH_CLAUSE_MATERIALIZATION

Does anyone have any idea hot to enable this option thru ODBC and keep it alive for next query?

Or maybe there is a way to enable this option thru Vertica configuration files or adminTools ?

Let me know!

Best,

Vitaliy.

Sharon_Cutter · June 2015

It's important to ask why this feature is disabled by default. It was initially enabled by default, then became disabled, which says to me that it's not production ready. Comments from anyone at HP?

--Sharon

vaitac · June 2015

> It was initially enabled by default, then became disabled, which says to me that it's not production ready.

This option was introduced at Vertica 7.1.X see: http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/NewFeatures/7.1/7.1.0/WITHClauseMaterialization.htm

By default, HP Vertica continues to use the inline expansion method. To enable the new functionality, use add_vertica_option:

SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');

Sharon_Cutter · June 2015

Hi Vitaliy,

For setting it globally, try the WithClauseMaterialization configuration parameter - set it to 1. But this assumes that you are ok with every single With clause being materialized, which may not provide optimal performance for every statement.

I haven't tried setting this through ODBC. You mentioned you couldn't get it to stick for the session. Does the option work if you set it as a hint in the query being executed through ODBC?

select /*+ add_vertica_options(OPT, ENABLE_WITH_CLAUSE_MATERIALIZATION)*/ ...

--Sharon

vaitac · June 2015

Hi Sharon,

/* hint */ doesn't work as well thru PDO/unixODBC

I used following command thru "vsql":

ALTER DATABASE mydb SET WithClauseMaterialization = 1;

then did:

SELECT current_value, catalog_value, default_value, change_requires_restart FROM configuration_parameters WHERE parameter_name = 'WithClauseMaterialization';

and got:

 current_value | catalog_value | default_value | change_requires_restart 
---------------+---------------+---------------+-------------------------
 1 | 1 | 0 | f

looks like it works and running query thru "vsql" shows same "good" results.

But once I tried to run this query thru unixODBC/PDO in order to check which value "WithClauseMaterialization" within session:

SELECT current_value, catalog_value, default_value, change_requires_restart FROM configuration_parameters;

i got:

Array
(
    [0] => Array
        (
            [source] => DEFAULT
            [node_name] => ALL
            [parameter_name] => AHMBackupManagement
            [current_value] => 
            [catalog_value] => 
            [default_value] => 
            [change_requires_restart] => 
            [description] => 
        )

    [1] => Array
        (
            [source] => DEFAULT
            [node_name] => ALL
            [parameter_name] => ActivePartitionCount
            [current_value] => 
            [catalog_value] => 
            [default_value] => 
            [change_requires_restart] => 
            [description] => 
        )
.......
    [212] => Array
        (
            [source] => DATABASE
            [node_name] => ALL
            [parameter_name] => WithClauseMaterialization
            [current_value] => 
            [catalog_value] => 
            [default_value] => 
            [change_requires_restart] => 
            [description] => 
        )

)

Now for me it look like PDO thru unixODBC session uses it's own environment with all default values and settings, I've tried:

$pdo = new PDO($dsn, $user, $pass, $options);

$st1 = $pdo->prepare("ALTER DATABASE mydb SET WithClauseMaterialization = 1;");
$st2 = $pdo->prepare("SELECT current_value, catalog_value, default_value, change_requires_restart FROM configuration_parameters WHERE parameter_name = 'WithClauseMaterialization';");
$st1->execute();
$st1->closeCursor();
$st2->execute();
print_r($st2->fetchAll(PDO::FETCH_ASSOC));

with no luck still:

Array
(
    [0] => Array
        (
            [source] => DATABASE
            [node_name] => ALL
            [parameter_name] => WithClauseMaterialization
            [current_value] => 
            [catalog_value] => 
            [default_value] => 
            [change_requires_restart] => 
            [description] => 
        )

)

Any Ideas?

marcothesane · June 2015

Hi All -

it's very probably not an ODBC issue.

Here I do it using my own developed ODBC client written in C:

SQL>ALTER DATABASE ver71 SET WithClauseMaterialization = 1;
database altered
_tm_prepare    00:00:00.001
_tm_execute    00:00:00.108
_tm_cmd_total 00:00:00.109
[14:06:23] dbadmin [ public, v_catalog, v_monitor, v_internal ]
SQL>SELECT current_value, catalog_value, default_value, change_requires_restart,parameter_name FROM configuration_parameters where parameter_name='WithClauseMaterialization';
current_value           : 1
catalog_value           : 1
default_value           : 0
change_requires_restart : [ 0 ]
parameter_name          : WithClauseMaterialization

Looks like your database client uses different, random, existing connections to execute two consecutive SQL calls against the same database ...

check the query_requests system table; search for the entries with the SQL statements you submitted (in the column request), and check if they have the same session id ....

good luck -

marco

PDO, unixODBC and setup option for next query

Comments

Leave a Comment