You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "zy1263188600 (via GitHub)" <gi...@apache.org> on 2023/03/16 07:47:42 UTC

[GitHub] [shardingsphere] zy1263188600 opened a new issue, #24648: Possible performance optimization of time slicing

zy1263188600 opened a new issue, #24648:
URL: https://github.com/apache/shardingsphere/issues/24648

   ### Which version of ShardingSphere did you use?
   ShardingSphere-5.2.0
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   ### Expected behavior
   执行SQL
   ```SQL
   select * from f_busi where start_time > "2022-11-01 00:00:00" and start_time < "2024-03-01 00:00:00"
   ```
   The result seems to be generated according to Cartesian product
   ```
   routeDataSources:[fbusi-2024, fbusi-2022, fbusi-2023]
   ```
   ```
   routeTables:
   [f_busi_06, f_busi_05, f_busi_04, f_busi_03, f_busi_09, f_busi_08, f_busi_07, f_busi_02, f_busi_12, f_busi_01, f_busi_11, f_busi_10]
   [f_busi_06, f_busi_05, f_busi_04, f_busi_03, f_busi_09, f_busi_08, f_busi_07, f_busi_02, f_busi_12, f_busi_01, f_busi_11, f_busi_10]
   [f_busi_06, f_busi_05, f_busi_04, f_busi_03, f_busi_09, f_busi_08, f_busi_07, f_busi_02, f_busi_12, f_busi_01, f_busi_11, f_busi_10]
   ```
   ### Actual behavior
   ```
   routeDataSources:[fbusi-2024, fbusi-2022, fbusi-2023]
   ```
   ```
   routeTables:
   [f_busi_11, f_busi_12]
   [f_busi_06, f_busi_05, f_busi_04, f_busi_03, f_busi_09, f_busi_08, f_busi_07, f_busi_02, f_busi_12, f_busi_01, f_busi_11, f_busi_10]
   f_busi_03,f_busi_02, f_busi_01]
   ```
   ### Reason analyze (If you can)
   The data source information cannot be obtained in the implementation of this interface
   ![image](https://user-images.githubusercontent.com/74887513/225547846-b21bdf59-dcd3-48ce-8116-964abe1b9183.png)
   Because the data source information in the source code is not transferred
   ![image](https://user-images.githubusercontent.com/74887513/225548271-0008e8f7-6a32-4fc7-817c-5e8684a1d422.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] zy1263188600 commented on issue #24648: Possible performance optimization of time slicing

Posted by "zy1263188600 (via GitHub)" <gi...@apache.org>.
zy1263188600 commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1473391075

   1、Perhaps routing DataSource is passed into doSharding() as an input parameter, providing space for optimization using the ComplexKeysShardingAlgorithm custom algorithm
   2、Reconstruct the input parameter Collection<String>availableTargetTables of doSharding() to include data source information, such as:
   ```java
   doSharding(Collection<AvailableTargetTable> availableTargetTables)
   ```
   ```java
   public class AvailableTargetTable {
       String dataBase;
       Collection<String> availableTargetTables;
   }
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] zy1263188600 commented on issue #24648: Possible performance optimization of time sharding

Posted by "zy1263188600 (via GitHub)" <gi...@apache.org>.
zy1263188600 commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1479039668

   [demo.zip](https://github.com/apache/shardingsphere/files/11037439/demo.zip)
   Please run test.java.com.cdfortis.demo.DemoApplicationTests.start()
   @sandynz 
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] strongduanmu commented on issue #24648: Possible performance optimization of time sharding

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1481052488

   @sandynz Thank you for your remind, I will check it later.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] sandynz commented on issue #24648: Possible performance optimization of time slicing

Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1471905146

   Hi @zy1263188600 , could you supply your rule configuration?
   
   And could you share the possible solution to optimize performance?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] zy1263188600 commented on issue #24648: Possible performance optimization of time slicing

Posted by "zy1263188600 (via GitHub)" <gi...@apache.org>.
zy1263188600 commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1473362978

   ```properties
   #DataBase
   spring.shardingsphere.rules.sharding.default-database-strategy.complex.sharding-columns=id,start_time
   spring.shardingsphere.rules.sharding.default-database-strategy.complex.sharding-algorithm-name=database-inline
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=CLASS_BASED
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.strategy=complex
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithmClassName=com.cdfortis.fbusihistoricaldata.config.algorithm.MyComplexShardingAlgorithm
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.datetime-pattern=yyyy-MM-dd HH:mm:ss
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.datetime-lower=2020-01-01 00:00:00
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.datetime-upper=2027-12-31 23:59:59
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.sharding-suffix-pattern=yyyy
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.datetime-interval-amount=1
   spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.datetime-interval-unit=YEARS
   ```
   ```properties
   #Table
   spring.shardingsphere.rules.sharding.tables.f_busi.actual-data-nodes=fbusi-$->{2020..2027}.f_busi_$->{(1..12).collect{t ->t.toString().padLeft(2,'0')}}
   spring.shardingsphere.rules.sharding.tables.f_busi.table-strategy.complex.sharding-columns=id,start_time
   spring.shardingsphere.rules.sharding.tables.f_busi.table-strategy.complex.sharding-algorithm-name=f-busi-inline
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.type=CLASS_BASED
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.strategy=complex
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.algorithmClassName=com.cdfortis.fbusihistoricaldata.config.algorithm.MyComplexShardingAlgorithm
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.datetime-pattern=yyyy-MM-dd HH:mm:ss
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.datetime-lower=2020-01-01 00:00:00
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.datetime-upper=2027-12-31 23:59:59
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.sharding-suffix-pattern=MM
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.datetime-interval-amount=1
   spring.shardingsphere.rules.sharding.sharding-algorithms.f-busi-inline.props.datetime-interval-unit=MONTHS
   ```
   Other configurations ignored @sandynz 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] sandynz commented on issue #24648: Possible performance optimization of time sharding

Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1481043512

   Hi @strongduanmu , could you help to have a look at it?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] sandynz commented on issue #24648: Possible performance optimization of time slicing

Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1475249086

   `routeTables` in `Expected behavior` have much duplicated actual tables. Could you enable `sql-show: true, then supply Logic SQL and Actual SQLs?
   
   And how it optimize performance, could you supply abstract demo of MyComplexShardingAlgorithm?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] zy1263188600 commented on issue #24648: Possible performance optimization of time slicing

Posted by "zy1263188600 (via GitHub)" <gi...@apache.org>.
zy1263188600 commented on issue #24648:
URL: https://github.com/apache/shardingsphere/issues/24648#issuecomment-1475836604

   @sandynz
   This is the original log
   Logic SQL and Actual SQL：
   ```log
   [2023-03-20 09:29:36] [INFO ] [main] [ShardingSphere-SQL.74] : Logic SQL: SELECT * FROM f_busi 
    WHERE (start_time >= ? AND start_time <= ?)
   [2023-03-20 09:29:36] [INFO ] [main] [ShardingSphere-SQL.74] : Actual SQL: fbusi-2022 ::: 
   SELECT * FROM f_busi_01 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_02 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_03 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_04 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_05 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_06 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_07 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_08 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_09 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_10 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_11 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_12 WHERE (start_time >= ? AND start_time <= ?) ::: [2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00]
   [2023-03-20 09:29:36] [INFO ] [main] [ShardingSphere-SQL.74] : Actual SQL: fbusi-2023 ::: 
   SELECT * FROM f_busi_01 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_02 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_03 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_04 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_05 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_06 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_07 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_08 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_09 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_10 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_11 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_12 WHERE (start_time >= ? AND start_time <= ?) ::: [2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00]
   [2023-03-20 09:29:36] [INFO ] [main] [ShardingSphere-SQL.74] : Actual SQL: fbusi-2024 ::: 
   SELECT * FROM f_busi_01 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_02 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_03 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_04 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_05 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_06 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_07 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_08 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_09 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_10 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_11 WHERE (start_time >= ? AND start_time <= ?) UNION ALL 
   SELECT * FROM f_busi_12 WHERE (start_time >= ? AND start_time <= ?) ::: [2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00, 2022-11-01 00:00:00, 2024-03-01 00:00:00]
    ```
   demo What is it like A SpringBoot project?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org