OracleTimestampBuffer performance problem in vw7.4.1 ?

Hi David,

The query is generated by Lens and looks like this:

SELECT *

FROM

            psi.psitxblock psitxblock1,

            psi.psitransmission psitransmission1,

            psi.psitxlink psitxlink1,

            psi.WOnRepeatLink WOnRepeatLink1,

            psi.psischedule psischedule1

WHERE

((((((((psitxblock1.txb_id_tx=psitransmission1.oid) AND

(psitransmission1.tx_id_txlink=psitxlink1.oid(+))) AND

(psitransmission1.tx_id_repeatLink=WOnRepeatLink1.oid(+))) AND

(psitransmission1.tx_id_schedule=psischedule1.oid)) AND

(psischedule1.sch_isactive=:p1)) AND

(psischedule1.sch_id_channel=:p2)) AND

(psitxblock1.txb_txdate=:p3)) AND

(psitxblock1.txb_id_group  IS NULL ))

When I replace the :p1 … :p3 variables with the real values the query returns results immediately. When the variables are

bound, it takes around two seconds before I get an answer in vw7.4.1. ‘:p3’ is the one that contains a Date.

The column type in Oracle is ‘Date’.

I’m binding an instance of Date. But, when I trace through the code, Lens converts the Date to a Timestamp. You can see this in the Date>>oracleConversion method:

oracleConversion

                        ^#Timestamp

And, on LensDatabaseContext there is the method #initializeBasicTypes that says:

…

addBasicType: (LensEncodedType new

                                    memberClass: Date;

                                    name: #Date;

                                    fieldType: (self typeNamed: #Timestamp);

                                    encodeSelector: #asTimestamp;

                                    decodeSelector: #asDate).

…

Which is also used to convert Date to Timestamp. I wonder why this conversion to Timestamps is necessary. I know that in Oracle a Date column can also contain Time information so perhaps this is the reason.

As a workaround I’ve overridden the method OracleSession>>timestampBufferClass so that it always returns OracleBuffer instead of the OracleTimestampBuffer, but I’m not sure what the consequences of this are.

I’ve ran all our tests and everything seems to work fine.

From your mail it seems that the OracleTimestampBuffer is needed for Timestamp objects. Would this mean that I can change the Date>>oracleConversion and LensDatabaseContext>>initializeBasicTypes so that they don’t convert Dates to Timestamp ?

Thanks,

Mark

This issue doesn’t sound familiar.

-          what is the query you’re actually using, and

-          what is the column type, and

-          are you binding an instance of Date, or is it a Timestamp?

I think Dates will proceed as usual, but Timestamps will receive their own new dedicated timestamp buffer, supported by Oracle. So I would be surprised if you are using a Date bind object (but this is just a guess).

It is odd that Oracle doesn’t support Timestamp indexes. When the details are in, we might need to document this.

- Dave W

Hi,

I’ve encountered a problem with the OracleTimestampBuffer class in vw7.4.1. When I perform a query that contains a condition on a date column on oracle, and the query uses bind variables to supply the parameters, the query execution seems to block for about 2 seconds before I get a result.

Running the same query in VW7.2.1 doesn’t block the image. VW7.2.1 doesn’t have the OracleTimestampBuffer class.

When I change the references to OracleTimestampBuffer with OracleBuffer in vw7.4.1 and run the query again, I get the results instantaneous.

So far, I’ve noticed this problem with one specific query. When I add more conditions to the query it sometimes suddenly does run quickly.

The problem also only shows up on databases with a lot of data in the tables.

Has anybody encountered something like this ?

Thanks,

Mark

Hi David,

The query is generated by Lens and looks like this:

SELECT *

FROM

            psi.psitxblock psitxblock1,

            psi.psitransmission psitransmission1,

            psi.psitxlink psitxlink1,

            psi.WOnRepeatLink WOnRepeatLink1,

            psi.psischedule psischedule1

WHERE

((((((((psitxblock1.txb_id_tx=psitransmission1.oid) AND

(psitransmission1.tx_id_txlink=psitxlink1.oid(+))) AND

(psitransmission1.tx_id_repeatLink=WOnRepeatLink1.oid(+))) AND

(psitransmission1.tx_id_schedule=psischedule1.oid)) AND

(psischedule1.sch_isactive=:p1)) AND

(psischedule1.sch_id_channel=:p2)) AND

(psitxblock1.txb_txdate=:p3)) AND

(psitxblock1.txb_id_group  IS NULL ))

When I replace the :p1 … :p3 variables with the real values the query returns results immediately. When the variables are

bound, it takes around two seconds before I get an answer in vw7.4.1. ‘:p3’ is the one that contains a Date.

The column type in Oracle is ‘Date’.

I’m binding an instance of Date. But, when I trace through the code, Lens converts the Date to a Timestamp. You can see this in the Date>>oracleConversion method:

oracleConversion

                        ^#Timestamp

And, on LensDatabaseContext there is the method #initializeBasicTypes that says:

…

addBasicType: (LensEncodedType new

                                    memberClass: Date;

                                    name: #Date;

                                    fieldType: (self typeNamed: #Timestamp);

                                    encodeSelector: #asTimestamp;

                                    decodeSelector: #asDate).

…

Which is also used to convert Date to Timestamp. I wonder why this conversion to Timestamps is necessary. I know that in Oracle a Date column can also contain Time information so perhaps this is the reason.

As a workaround I’ve overridden the method OracleSession>>timestampBufferClass so that it always returns OracleBuffer instead of the OracleTimestampBuffer, but I’m not sure what the consequences of this are.

I’ve ran all our tests and everything seems to work fine.

From your mail it seems that the OracleTimestampBuffer is needed for Timestamp objects. Would this mean that I can change the Date>>oracleConversion and LensDatabaseContext>>initializeBasicTypes so that they don’t convert Dates to Timestamp ?

Thanks,

Mark

This issue doesn’t sound familiar.

-          what is the query you’re actually using, and

-          what is the column type, and

-          are you binding an instance of Date, or is it a Timestamp?

I think Dates will proceed as usual, but Timestamps will receive their own new dedicated timestamp buffer, supported by Oracle. So I would be surprised if you are using a Date bind object (but this is just a guess).

It is odd that Oracle doesn’t support Timestamp indexes. When the details are in, we might need to document this.

- Dave W

Hi,

I’ve encountered a problem with the OracleTimestampBuffer class in vw7.4.1. When I perform a query that contains a condition on a date column on oracle, and the query uses bind variables to supply the parameters, the query execution seems to block for about 2 seconds before I get a result.

Running the same query in VW7.2.1 doesn’t block the image. VW7.2.1 doesn’t have the OracleTimestampBuffer class.

When I change the references to OracleTimestampBuffer with OracleBuffer in vw7.4.1 and run the query again, I get the results instantaneous.

So far, I’ve noticed this problem with one specific query. When I add more conditions to the query it sometimes suddenly does run quickly.

The problem also only shows up on databases with a lot of data in the tables.

Has anybody encountered something like this ?

Thanks,

Mark

Hi David,

The query is generated by Lens and looks like this:

SELECT *

FROM

            psi.psitxblock psitxblock1,

            psi.psitransmission psitransmission1,

            psi.psitxlink psitxlink1,

            psi.WOnRepeatLink WOnRepeatLink1,

            psi.psischedule psischedule1

WHERE

((((((((psitxblock1.txb_id_tx=psitransmission1.oid) AND

(psitransmission1.tx_id_txlink=psitxlink1.oid(+))) AND

(psitransmission1.tx_id_repeatLink=WOnRepeatLink1.oid(+))) AND

(psitransmission1.tx_id_schedule=psischedule1.oid)) AND

(psischedule1.sch_isactive=:p1)) AND

(psischedule1.sch_id_channel=:p2)) AND

(psitxblock1.txb_txdate=:p3)) AND

(psitxblock1.txb_id_group  IS NULL ))

When I replace the :p1 … :p3 variables with the real values the query returns results immediately. When the variables are

bound, it takes around two seconds before I get an answer in vw7.4.1. ‘:p3’ is the one that contains a Date.

The column type in Oracle is ‘Date’.

I’m binding an instance of Date. But, when I trace through the code, Lens converts the Date to a Timestamp. You can see this in the Date>>oracleConversion method:

oracleConversion

                        ^#Timestamp

And, on LensDatabaseContext there is the method #initializeBasicTypes that says:

…

addBasicType: (LensEncodedType new

                                    memberClass: Date;

                                    name: #Date;

                                    fieldType: (self typeNamed: #Timestamp);

                                    encodeSelector: #asTimestamp;

                                    decodeSelector: #asDate).

…

Which is also used to convert Date to Timestamp. I wonder why this conversion to Timestamps is necessary. I know that in Oracle a Date column can also contain Time information so perhaps this is the reason.

As a workaround I’ve overridden the method OracleSession>>timestampBufferClass so that it always returns OracleBuffer instead of the OracleTimestampBuffer, but I’m not sure what the consequences of this are.

I’ve ran all our tests and everything seems to work fine.

From your mail it seems that the OracleTimestampBuffer is needed for Timestamp objects. Would this mean that I can change the Date>>oracleConversion and LensDatabaseContext>>initializeBasicTypes so that they don’t convert Dates to Timestamp ?

Thanks,

Mark

This issue doesn’t sound familiar.

-          what is the query you’re actually using, and

-          what is the column type, and

-          are you binding an instance of Date, or is it a Timestamp?

I think Dates will proceed as usual, but Timestamps will receive their own new dedicated timestamp buffer, supported by Oracle. So I would be surprised if you are using a Date bind object (but this is just a guess).

It is odd that Oracle doesn’t support Timestamp indexes. When the details are in, we might need to document this.

- Dave W

Hi,

I’ve encountered a problem with the OracleTimestampBuffer class in vw7.4.1. When I perform a query that contains a condition on a date column on oracle, and the query uses bind variables to supply the parameters, the query execution seems to block for about 2 seconds before I get a result.

Running the same query in VW7.2.1 doesn’t block the image. VW7.2.1 doesn’t have the OracleTimestampBuffer class.

When I change the references to OracleTimestampBuffer with OracleBuffer in vw7.4.1 and run the query again, I get the results instantaneous.

So far, I’ve noticed this problem with one specific query. When I add more conditions to the query it sometimes suddenly does run quickly.

The problem also only shows up on databases with a lot of data in the tables.

Has anybody encountered something like this ?

Thanks,

Mark

Thanks David.

Hi David,

The query is generated by Lens and looks like this:

SELECT *

FROM

            psi.psitxblock psitxblock1,

            psi.psitransmission psitransmission1,

            psi.psitxlink psitxlink1,

            psi.WOnRepeatLink WOnRepeatLink1,

            psi.psischedule psischedule1

WHERE

((((((((psitxblock1.txb_id_tx=psitransmission1.oid) AND

(psitransmission1.tx_id_txlink=psitxlink1.oid(+))) AND

(psitransmission1.tx_id_repeatLink=WOnRepeatLink1.oid(+))) AND

(psitransmission1.tx_id_schedule=psischedule1.oid)) AND

(psischedule1.sch_isactive=:p1)) AND

(psischedule1.sch_id_channel=:p2)) AND

(psitxblock1.txb_txdate=:p3)) AND

(psitxblock1.txb_id_group  IS NULL ))

When I replace the :p1 … :p3 variables with the real values the query returns results immediately. When the variables are

bound, it takes around two seconds before I get an answer in vw7.4.1. ‘:p3’ is the one that contains a Date.

The column type in Oracle is ‘Date’.

I’m binding an instance of Date. But, when I trace through the code, Lens converts the Date to a Timestamp. You can see this in the Date>>oracleConversion method:

oracleConversion

                        ^#Timestamp

And, on LensDatabaseContext there is the method #initializeBasicTypes that says:

…

addBasicType: (LensEncodedType new

                                    memberClass: Date;

                                    name: #Date;

                                    fieldType: (self typeNamed: #Timestamp);

                                    encodeSelector: #asTimestamp;

                                    decodeSelector: #asDate).

…

Which is also used to convert Date to Timestamp. I wonder why this conversion to Timestamps is necessary. I know that in Oracle a Date column can also contain Time information so perhaps this is the reason.

As a workaround I’ve overridden the method OracleSession>>timestampBufferClass so that it always returns OracleBuffer instead of the OracleTimestampBuffer, but I’m not sure what the consequences of this are.

I’ve ran all our tests and everything seems to work fine.

From your mail it seems that the OracleTimestampBuffer is needed for Timestamp objects. Would this mean that I can change the Date>>oracleConversion and LensDatabaseContext>>initializeBasicTypes so that they don’t convert Dates to Timestamp ?

Thanks,

Mark

This issue doesn’t sound familiar.

-          what is the query you’re actually using, and

-          what is the column type, and

-          are you binding an instance of Date, or is it a Timestamp?

I think Dates will proceed as usual, but Timestamps will receive their own new dedicated timestamp buffer, supported by Oracle. So I would be surprised if you are using a Date bind object (but this is just a guess).

It is odd that Oracle doesn’t support Timestamp indexes. When the details are in, we might need to document this.

- Dave W

Hi,

I’ve encountered a problem with the OracleTimestampBuffer class in vw7.4.1. When I perform a query that contains a condition on a date column on oracle, and the query uses bind variables to supply the parameters, the query execution seems to block for about 2 seconds before I get a result.

Running the same query in VW7.2.1 doesn’t block the image. VW7.2.1 doesn’t have the OracleTimestampBuffer class.

When I change the references to OracleTimestampBuffer with OracleBuffer in vw7.4.1 and run the query again, I get the results instantaneous.

So far, I’ve noticed this problem with one specific query. When I add more conditions to the query it sometimes suddenly does run quickly.

The problem also only shows up on databases with a lot of data in the tables.

Has anybody encountered something like this ?

Thanks,

Mark