You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iotdb.apache.org by 江天 <jt...@163.com> on 2019/06/25 12:41:09 UTC
About prepared statement
Hi,
As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
4. The user calls `pStmt.execute()` to execute an insertion.
5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.
Re: Re: About prepared statement
Posted by Jialin Qiao <qj...@mails.tsinghua.edu.cn>.
Hi, Tian Jiang,
This improvement is very meaningful.
I have reviewed your PR and approved it.
Best,
--
Jialin Qiao
School of Software, Tsinghua University
乔嘉林
清华大学 软件学院
> -----原始邮件-----
> 发件人: "江天" <jt...@163.com>
> 发送时间: 2019-06-26 11:13:48 (星期三)
> 收件人: "dev@iotdb.apache.org" <de...@iotdb.apache.org>
> 抄送:
> 主题: Re: About prepared statement
>
> I have made some further improvements. Each prepared statement is now bind with a statement id and with this statement id, the user can skip setting some parameters if they remain unchanged since last execution.
> In an ideal situation, each device itself connects to IoTDB and its deviceId and measurements are always the same. These devices may only send their deviceIds and measurements at the very beginning and in the following insertions, they only need to send time and values to IoTDB, which will save a lot of band-width from sending the unchanged parameters.
> In a simple experiment of 1000000 insertions, prepared statement costs 41133ms(set all parameters each time), ideal prepared statement costs 36794ms(only values and time are set), normal statement costs 57540ms.
>
> > 在 2019年6月25日，下午11:04，江天 <jt...@163.com> 写道：
> >
> > The relative pull request is on https://github.com/apache/incubator-iotdb/pull/206 <https://github.com/apache/incubator-iotdb/pull/206>. I would be glad if someone could review it for me.
> > A naive test showed that when executing 1000000 insertions, prepared statement costed 41094ms, normal statement costed 54035ms, which is a significant improvement.
> >
> >> 在 2019年6月25日，下午10:58，江天 <jt...@163.com> 写道：
> >>
> >> I have created an issue IOTDB-122 <https://issues.apache.org/jira/browse/IOTDB-122>, you may find it on https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues <https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues>.
> >>
> >>
> >>> 在 2019年6月25日，下午9:17，江天 <jt...@163.com> 写道：
> >>>
> >>> Follow the preceding mail,
> >>>
> >>> People of interest are welcomed to join this thread, discuss about this new feature and give advice. If no one has opposite opinion, I would like to set off to develop this feature.
> >>>
> >>> Best regards,
> >>>
> >>> Tian Jiang
> >>>
> >>>> 在 2019年6月25日，下午8:41，江天 <jt...@163.com> 写道：
> >>>>
> >>>> Hi,
> >>>>
> >>>> As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
> >>>>
> >>>> Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
> >>>>
> >>>> 1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
> >>>> 2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
> >>>> 3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
> >>>> 4. The user calls `pStmt.execute()` to execute an insertion.
> >>>> 5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
> >>>> 6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.
> >>>
> >>
> >
>
Re: About prepared statement
Posted by 江天 <jt...@163.com>.
I have made some further improvements. Each prepared statement is now bind with a statement id and with this statement id, the user can skip setting some parameters if they remain unchanged since last execution.
In an ideal situation, each device itself connects to IoTDB and its deviceId and measurements are always the same. These devices may only send their deviceIds and measurements at the very beginning and in the following insertions, they only need to send time and values to IoTDB, which will save a lot of band-width from sending the unchanged parameters.
In a simple experiment of 1000000 insertions, prepared statement costs 41133ms(set all parameters each time), ideal prepared statement costs 36794ms(only values and time are set), normal statement costs 57540ms.
> 在 2019年6月25日，下午11:04，江天 <jt...@163.com> 写道：
>
> The relative pull request is on https://github.com/apache/incubator-iotdb/pull/206 <https://github.com/apache/incubator-iotdb/pull/206>. I would be glad if someone could review it for me.
> A naive test showed that when executing 1000000 insertions, prepared statement costed 41094ms, normal statement costed 54035ms, which is a significant improvement.
>
>> 在 2019年6月25日，下午10:58，江天 <jt...@163.com> 写道：
>>
>> I have created an issue IOTDB-122 <https://issues.apache.org/jira/browse/IOTDB-122>, you may find it on https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues <https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues>.
>>
>>
>>> 在 2019年6月25日，下午9:17，江天 <jt...@163.com> 写道：
>>>
>>> Follow the preceding mail,
>>>
>>> People of interest are welcomed to join this thread, discuss about this new feature and give advice. If no one has opposite opinion, I would like to set off to develop this feature.
>>>
>>> Best regards,
>>>
>>> Tian Jiang
>>>
>>>> 在 2019年6月25日，下午8:41，江天 <jt...@163.com> 写道：
>>>>
>>>> Hi,
>>>>
>>>> As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
>>>>
>>>> Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
>>>>
>>>> 1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
>>>> 2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
>>>> 3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
>>>> 4. The user calls `pStmt.execute()` to execute an insertion.
>>>> 5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
>>>> 6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.
>>>
>>
>
Re: About prepared statement
Posted by 江天 <jt...@163.com>.
The relative pull request is on https://github.com/apache/incubator-iotdb/pull/206 <https://github.com/apache/incubator-iotdb/pull/206>. I would be glad if someone could review it for me.
A naive test showed that when executing 1000000 insertions, prepared statement costed 41094ms, normal statement costed 54035ms, which is a significant improvement.
> 在 2019年6月25日，下午10:58，江天 <jt...@163.com> 写道：
>
> I have created an issue IOTDB-122 <https://issues.apache.org/jira/browse/IOTDB-122>, you may find it on https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues <https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues>.
>
>
>> 在 2019年6月25日，下午9:17，江天 <jt...@163.com> 写道：
>>
>> Follow the preceding mail,
>>
>> People of interest are welcomed to join this thread, discuss about this new feature and give advice. If no one has opposite opinion, I would like to set off to develop this feature.
>>
>> Best regards,
>>
>> Tian Jiang
>>
>>> 在 2019年6月25日，下午8:41，江天 <jt...@163.com> 写道：
>>>
>>> Hi,
>>>
>>> As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
>>>
>>> Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
>>>
>>> 1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
>>> 2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
>>> 3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
>>> 4. The user calls `pStmt.execute()` to execute an insertion.
>>> 5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
>>> 6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.
>>
>
Re: About prepared statement
Posted by 江天 <jt...@163.com>.
I have created an issue IOTDB-122 <https://issues.apache.org/jira/browse/IOTDB-122>, you may find it on https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues <https://issues.apache.org/jira/projects/IOTDB/issues/IOTDB-122?filter=allopenissues>.
> 在 2019年6月25日，下午9:17，江天 <jt...@163.com> 写道：
>
> Follow the preceding mail,
>
> People of interest are welcomed to join this thread, discuss about this new feature and give advice. If no one has opposite opinion, I would like to set off to develop this feature.
>
> Best regards,
>
> Tian Jiang
>
>> 在 2019年6月25日，下午8:41，江天 <jt...@163.com> 写道：
>>
>> Hi,
>>
>> As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
>>
>> Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
>>
>> 1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
>> 2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
>> 3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
>> 4. The user calls `pStmt.execute()` to execute an insertion.
>> 5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
>> 6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.
>
Re: About prepared statement
Posted by 江天 <jt...@163.com>.
Follow the preceding mail,
People of interest are welcomed to join this thread, discuss about this new feature and give advice. If no one has opposite opinion, I would like to set off to develop this feature.
Best regards,
Tian Jiang
> 在 2019年6月25日，下午8:41，江天 <jt...@163.com> 写道：
>
> Hi,
>
> As some have mentioned, sql parser(antlr) may consume about 40% of time in ingestion, especially when small sqls executed sent frequently. Luckily, IoTDB insertion sqls are currently all alike and simple, there are 4 most meaningful parts of such sqls: deviceId, measurements, values and time. For such a simple structure, using tools like antlr may be just too heavy.
>
> Intuitively, PreparedStatement in Standard JDBC interface can be just used for reliving parsing overhead when sqls are similar. I will describe how PreparedStatement works as follow (this is still left to be implemented):
>
> 1. The user wants to create a prepared insert statement and called `connection.prepareStatement(“Insert”)`;
> 2. The connection matches the parameter string with some templates, finds out it is an insertion and returns an IoTDBPreparedInsertStatement pStmt.
> 3. The user calls `pStmt.setDevice(“root.device1”); pStmt.setTime(100); pStmt.setMeasurements(measurementArray); pStmt.setValues(valueArray);` to set parameters for next insertion.
> 4. The user calls `pStmt.execute()` to execute an insertion.
> 5. The PreparedInsertStatement creates a TSInsertionReq, puts deviceId, measurements, values and time into this request and sends this request to the server.
> 6. The server receives the request, extracts parameters from the request and executes an insertion directly through database engine and return a TSInsertionResp to the user.