Hi,
I am working on a small project and have a few questions and thought the best answer would come from you.
As there are millions of records (syslog generated messages properly inserted into the relevant tables in the database), I am using MonetDB, an open-source column-store database management system.
It has both JDBC and ODBC, and works fine in PHP (Yii2) with what I’ve done so far. Basically I use ODBC and since Yii2 (or PHP) doesn’t have a MonetDB schema and/or driver, I am using pgsql as after several tests, I found it to be the best one for MonetDB. I don’t know if designing a special MonetDB schema is worth a try.
Below is how I configure the connection to my MonetDB database (the parameters come from a MySQL database), and I can then use any of the Yii2 classes, to create the connection, the command, the query and use dataReaders and more. Although I can’t create a proper model (the way I can do with say MySQL) but I have also managed to make use of model and the search function (custom though) and use the SqlDataProvider class for my grids.
$dsn = "odbc:Driver=MonetDB;Host=".HParams::getParameterValue("warehouse_db_host").";".
"Port=".HParams::getParameterValue("warehouse_db_port").";".
"Database=".HParams::getParameterValue("warehouse_db_name");
$username = HParams::getParameterValue("warehouse_db_username");
$password = HParams::getParameterValue("warehouse_db_password");
$MonetDB = new \yii\db\Connection([
'driverName'=>'pgsql',
'dsn' => $dsn,
'username' => $username,
'password' => $password,
'enableProfiling' => false,
'enableLogging' => false,
'emulatePrepare' => false,
]);
And for the driver MonetDB for the odbc dsn, I have two files on my ubuntu box (one for the driver and one for the data source) and it works fine.
/etc/odbcinst.ini
[MonetDB]
Description = ODBC for MonetDB
Driver = /usr/lib/libMonetODBC.so
Setup = /usr/lib/libMonetODBCs.so
UsageCount = 1
/etc/odbc.ini
[MonetDB]
Description = ODBC Connection for MonetDB
Driver = MonetDB
I don’t need to specify the database, host, port, username and password in the above system-wide data source as my app will provide those as part of the dsn config.
Below is how I configure the $dataProvider for my grids and can make use of the sort and pagination functionalities almost out of the box
$dataProvider = new \yii\data\SqlDataProvider([
'sql' => $sql,
'db' => $connection,
'totalCount' => $totalCount,
'sort' => [
'attributes' => $sortAttributes,
'defaultOrder'=>[
'accessday' => SORT_DESC,
],
],
'pagination' => [
'pageSize' => 250,
],
]);
From what I see, independent of how I create my commands whether I use native SQL statements or I use the query builder class, all my select statements are being prepared. I also read the below in the documentation.
Command supports SQL statement preparation and parameter binding. Call bindValue() to bind a value to a SQL parameter; Call bindParam() to bind a PHP variable to a SQL parameter. When binding a parameter, the SQL statement is automatically prepared. You may also call prepare() explicitly to prepare a SQL statement.
Now, my question is whether there is a way to explicitly disable this feature, or maybe pass on a parameter so my SQL statements aren’t prepared. The reason I want to disable this is because it’s making a big difference in performance and I’ve done so many tests and tried to analyze what’s causing the performance issues, and I can only pinpoint the fact that the statements are prepared.
When I run the exact same queries directly on the server using mclient which is the MonetDB command-line tool or using DbVisualizer (uses JDBC) or any other tool with ODBC from a windows machine, the same queries that take a long time to return results (my Yii2 app) take significantly less time, and the only difference I am able to see is the queries coming from my Yii2 app are being prepared and not run straight away. Unless there may be another “hidden” issue which I’m not seeing.
With the way my app works, I don’t need the statements to be prepared as the same SQL statement will only be used once.
I checked the framework code, and I can see the below in Command.php in /vendor/yiisoft/yii2/db/ within the queryInternal function which is also called from all the different queryXX functions:
$this->prepare(true);
And I don’t think it’s parametrized (it may be but I didn’t check every single class) but also at the same time I don’t want to make any changes to the code.
Since I’m also tricking the system to make use of pgsql, I have a feeling the pgsql classes also have something to do with this.
I’ve done some research, checked this and other forums, gone through the Yii2 documentation, but couldn’t find a solution.
Your advice will greatly appreciated.
Thank you in advance.
Kevork