36 posts tagged with "pawsql"

PawSQL Advisor, as a SQL optimization tool originated from China, recently surpassed 10,000 downloads in the JetBrains marketplace. However, behind this achievement lies a difficult journey overcoming technical, market, and user habit challenges. This article shares the obstacles faced and breakthrough strategies, demonstrating the resilience of a localized technology product.

I. Challenges for Domestic Tools: Payment Reluctance, Trust Barriers, and Ecosystem Pressure​

1. Low Payment Willingness Among Individual Users
   Compared to overseas developers who are accustomed to subscription payment models, domestic individual users prefer free tools and generally show low willingness to pay. Although PawSQL Advisor provides a free SaaS version (PawSQL Cloud), the JetBrains plugin requires payment, directly limiting the conversion rate of individual users. The payment habits in the domestic market force it to rely on enterprise-level private deployments as the main revenue source.

2. Technical Trust and User Education Challenges
   As an emerging tool, users have doubts about the accuracy of automated optimization suggestions. For example, performance verification requires connection to real databases, which may raise concerns about resource consumption in production environments. Complex index recommendation logic also requires users to have certain database knowledge to understand. How to make developers trust the "black box" optimization capabilities of the tool became a core barrier to initial promotion.

3. Ecosystem Adaptation and Domestic Compatibility Challenges
   Facing complex technical ecosystems of domestic databases (such as Dameng, Kingbase), PawSQL needs to invest substantial resources to adapt SQL parsers for different dialects and ensure optimization rule compatibility. Additionally, domestic enterprises' strict requirements for "independent control" also increase product certification and compliance costs.

II. Breakthrough Strategies: Technical Differentiation, Scenario Focus, and Ecosystem Integration​

Despite numerous difficulties, PawSQL Advisor has achieved counter-trend growth through a "technology + scenario" dual-drive approach:

1. Building Technical Closed Loop for Competitive Advantage
   Compared to competitors like EverSQL, PawSQL Advisor not only provides index recommendations but also integrates performance verification and execution plan visualization functions, addressing the trust issue of "whether optimization suggestions are effective." With over 190 built-in optimization rules covering complex scenarios such as HAVING condition push-down and window function optimization, plus intelligent index recommendation capabilities, it forms a powerful technical moat.

2. Layered Strategy Covering All User Scenarios

   • Individual Users: Lowering the usage threshold through a free SaaS version, guiding users to experience before converting to paid plugin users.
   • Enterprise Users: Providing privately deployed PawSQL Engine, supporting integration with CI/CD processes, meeting the high data security requirements of industries such as finance and telecommunications.
   • Domestic Adaptation: Deep support for domestic databases like Dameng and Kingbase, becoming one of the few performance optimization solutions in the domestic innovation field.

3. Ecosystem Integration Enhancing Developer Stickiness
   As a JetBrains plugin, PawSQL Advisor seamlessly embeds into mainstream tools like IDEA and DataGrip, supporting one-click optimization of selected SQL or batch processing of files. Additionally, the VSCode plugin extends the user base, forming cross-platform coverage. This "tool as workflow" design significantly reduces user learning costs.

III. User Recognition: Efficiency Revolution in Real Scenarios​

Users' active choices are the best validation of product value:

• Financial Industry Case: A bank optimized OceanBase database through PawSQL, compressing complex queries from minute-level to second-level response times, reducing hardware resource usage by 30%.
• Developer Community Feedback: Automated rules (such as avoiding SELECT * and implicit type conversion optimization) help teams reduce code review workload by 50%.
• Technical Reputation: In Zhihu's SQL optimization challenge, its automatic optimization capabilities even surpassed human experts, with performance improvement effects generating discussion in technical circles.

IV. Future Challenges: Continuous Innovation and Ecosystem Expansion​

1. Exploration of AI Technology Integration
   The team is attempting to incorporate large language models (such as DeepSeek) into SQL parsing and optimization suggestion generation to address more complex query scenarios. However, balancing AI "creativity" with optimization rule "certainty" still requires technological breakthroughs.

2. Deep Binding with Domestic Ecosystem
   With the advancement of domestic innovation policies, PawSQL needs to further adapt to more domestic databases and strive to enter government and state-owned enterprise procurement lists. This requires the product to reach higher standards in compatibility and security.

3. Global Market Exploration
   Although focusing on the domestic market, overseas downloads of the JetBrains plugin account for over 30%. The team plans to launch multilingual versions and explore emerging markets such as Southeast Asia and Europe to diversify single market risk.

Conclusion​

PawSQL Advisor's 10,000 downloads represent not only a breakthrough in numbers but also a dual victory in technical confidence and market strategy for domestic tools. It proves that even when facing "inherent disadvantages" such as payment habits and ecosystem barriers, through precise positioning, technical depth, and ecosystem integration, local products can still occupy a place in niche fields. In the future, with the advancement of AI and domestic innovation trends, PawSQL may become another benchmark for Chinese technology going global.

Try Now: Visit the JetBrains Marketplace to install the plugin, or go to the official website for enterprise-level solutions.

In the wave of digital transformation across the financial industry, distributed databases have become the preferred choice for financial institutions building core business systems due to their high scalability, availability, and performance. TDSQL, the distributed database launched by Tencent Cloud, is widely used in finance, internet, and government sectors. However, with continuous business growth and explosive data expansion, optimizing TDSQL database performance has become a challenge for many enterprises and developers. This article introduces PawSQL's specialized optimization guide for TDSQL databases, helping users fully leverage TDSQL's performance potential.

I. The Importance of TDSQL Database Performance Optimization​

TDSQL databases face demanding challenges in financial applications, including high concurrency, large data volumes, and high reliability requirements. Performance optimization affects not only business response speed and user experience but also directly impacts financial institutions' operational efficiency and risk control capabilities. By optimizing TDSQL databases, users can achieve the following objectives:

• Improve Business Response Speed: Reduce query latency, increase transaction processing speed, and provide users with a smoother service experience.
• Lower Operational Costs: Optimize resource utilization, reduce hardware investment and maintenance costs, and improve economic benefits.
• Enhance System Stability: Improve database stability and reliability through reasonable optimization strategies, reducing the risk of system failures.

II. PawSQL's Specialized Optimization Capabilities for TDSQL​

1. Deep SQL Syntax Support for TDSQL​

PawSQL provides deep SQL syntax support for TDSQL databases, helping users better leverage TDSQL's features for performance optimization.

• Complete Support for MySQL Syntax: Since TDSQL is developed based on MySQL, PawSQL fully supports the MySQL syntax system, ensuring that users can seamlessly work with TDSQL's syntax requirements when using PawSQL for SQL optimization.
• Complete Parsing of TDSQL-Specific DDL Syntax: TDSQL has some unique DDL syntax, such as creating distributed tables and defining partitioned tables. PawSQL can fully parse these special syntaxes and provide accurate SQL optimization recommendations.

-- hash sharding or broadcast table
CREATE TABLE [IF NOT EXISTS] tbl_name
    [(create_definition)]
    [local_table_options]
shardkey=column_name|noshardkey_allset
-- range or list sharding
CREATE TABLE [IF NOT EXISTS] tbl_name
    [(create_definition)]
    [local_table_options]
TDSQL_DISTRIBUTED BY range|list (column_name) [partition_options]

2. Enhanced Optimization Rule System​

PawSQL has added multiple optimization rules targeting TDSQL's distributed characteristics to help users avoid common performance issues.

• Distributed SQL Design Standards:

  • Avoid Table Joins on Non-Shard Keys: In distributed databases, joining tables on fields that are not shard keys causes cross-node data transmission, increasing network overhead. PawSQL reminds users to use shard keys as join fields whenever possible.
  • Avoid Table Joins in Distributed Database DML: In distributed environments, DML operations involving table joins may lead to complex distributed transaction processing, affecting performance. PawSQL recommends users avoid table joins in DML operations.
  • Distributed Database DML Missing Equality Conditions on Shard Fields: In distributed databases, DML operations without equality conditions on shard fields lead to full table scans, severely impacting performance. PawSQL reminds users to always include equality conditions on shard fields in DML operations.

• Shard Key Design Standards:

  • Not Recommended to Use Multiple Fields as Shard Keys: Using multiple fields as shard keys may cause uneven data distribution and increase query complexity. PawSQL recommends using a single field as the shard key when possible.
  • Use High-Cardinality Fields as Shard Keys: The higher the cardinality of shard keys, the more evenly data is distributed, resulting in better query performance. PawSQL reminds users to select high-cardinality fields as shard keys.

• Distribution Strategy Design Standards:

  • Large Tables Should Not Use Replication Distribution: Using replication distribution for large tables causes data storage redundancy, increasing storage costs and network overhead. PawSQL recommends using hash or range distribution for large tables.
  • Hash Distribution is Recommended: Hash distribution achieves uniform data distribution and improves query performance. PawSQL recommends prioritizing hash distribution.
  • Avoid Non-Distributed Tables: Non-distributed tables in distributed databases may lead to centralized data storage, affecting system scalability and performance. PawSQL reminds users to avoid using non-distributed tables whenever possible.

III. SQL Optimization Full Lifecycle Matrix​

1. Development and Testing Phase: Intelligent SQL Optimization​

During the development and testing phase, PawSQL provides a one-stop online SQL optimization tool for application developers and testers.

• Query Rewriting Optimization: PawSQL automatically rewrites SQL queries, such as converting complex subqueries into more efficient join queries and optimizing the order of query conditions, helping users improve query performance.
• Intelligent Index Recommendations: Based on SQL query characteristics and data distribution, PawSQL provides intelligent index recommendations. Creating appropriate indexes can significantly improve query speed and reduce data scan volume.

The PawSQL optimization platform has been integrated with popular IDEs (VSCode and JetBrains), allowing developers to optimize SQL without leaving their development environment, improving work efficiency.

2. Code Integration Phase: Comprehensive SQL Auditing​

During the code integration phase, PawSQL's auditing platform provides comprehensive and precise intelligent SQL auditing capabilities for SQL quality management teams through its leading core technologies.

• Proprietary SQL Parser: PawSQL's proprietary SQL parser accurately parses various complex SQL statements, providing precise syntactic information for subsequent rule matching and optimization recommendations.
• Syntax Tree-Based Rule Matching: PawSQL performs in-depth analysis of SQL statements by constructing SQL syntax trees, matching various optimization rules, and ensuring the accuracy of audit results.
• Contextual Information Updates: PawSQL dynamically updates audit results based on the contextual information of SQL statements, providing optimization recommendations that better match actual situations.

PawSQL conducts comprehensive checks across syntax standards, performance efficiency, security, and other dimensions, providing targeted optimization suggestions to help enterprises improve SQL performance and application efficiency. For TDSQL's distributed characteristics, PawSQL offers specialized distributed query optimization recommendations, with applicable rule data exceeding 190 rules.

3. Operations Phase: Performance Inspection Platform​

During the operations phase, PawSQL's database performance inspection platform automatically captures slow queries generated in the database regularly and provides SQL optimization recommendations.

• Slow Query Inspection: PawSQL automatically captures slow query statements in the database, analyzes their execution plans and performance bottlenecks, and provides detailed optimization recommendations. By optimizing slow queries, users can significantly improve overall database performance.
• Database Object Inspection: PawSQL also regularly inspects database objects to identify potential performance, security, maintainability, and other issues, providing optimization recommendations. For example, it checks index usage, table space utilization, and other factors, helping users detect and resolve potential problems promptly.

PawSQL supports slow query inspection and database object inspection for TDSQL databases, providing comprehensive performance monitoring and optimization support for operations personnel.

IV. Conclusion​

PawSQL for TDSQL databases provides users with a one-stop performance optimization solution. From intelligent SQL optimization during development and testing to comprehensive SQL auditing during code integration and the performance inspection platform during operations, PawSQL covers the entire lifecycle of database performance optimization. By using PawSQL, users can fully leverage the performance potential of TDSQL databases, improve business response speed, reduce operational costs, and enhance system stability.

In financial-grade distributed database applications, performance optimization is a long-term and challenging task. PawSQL will continue to monitor TDSQL database developments and user needs, continuously optimizing and improving the rule system to provide higher quality and more efficient performance optimization services. Experience PawSQL now to unlock TDSQL database performance potential and safeguard your business development!

Try it now: https://www.pawsql.com/app

0. Overview​

In the latest version of PawSQL, comprehensive support for SQL Server databases has been implemented, offering SQL optimization, SQL review, and performance inspection capabilities. This covers the entire lifecycle of SQL development, testing, and operations, helping users fully leverage the performance potential of SQL Server databases.

1. Managing SQL Server Databases​

Workspaces provide the environment for SQL optimization. PawSQL supports two methods for creating workspaces for SQL Server optimization tasks:

• Offline - DDL Parsing: Building workspaces by parsing database DDL files.
• Online - Metadata Retrieval: Directly acquiring metadata from SQL Server databases to quickly establish workspaces.

2. Comprehensive SQL Review Rule Set​

PawSQL leverages its leading core technologies—proprietary SQL parser, syntax tree-based rule matching, and contextual information updates—to provide intelligent and precise SQL auditing capabilities for SQL quality management teams. It conducts comprehensive checks across multiple dimensions including syntax standards, performance efficiency, and security, while offering targeted optimization suggestions to help enterprises improve SQL performance and application efficiency.

3. Rich Query Rewriting Optimization​

PawSQL provides SQL rewriting optimization algorithms based on both heuristic rules and cost models, recommending semantically equivalent but performance-optimized SQL for SQL Server. It also offers text comparison between original and rewritten SQL, highlighting the modified sections.

4. Intelligent Index Recommendations​

PawSQL's intelligent index recommendation engine offers optimal indexing strategies for application queries on SQL Server, adapting to various SQL syntax requirements and significantly improving query efficiency.

5. Execution Plan Visualization (PPV)​

PawSQL Plan Visualizer (PPV) supports graphical display and analysis of SQL Server execution plans, helping users easily understand query execution processes and quickly identify performance bottlenecks.

6. Performance Validation​

PawSQL automatically collects SQL Server execution plans before and after optimization, obtaining execution costs to ensure recommended optimization suggestions genuinely improve database performance.

7. Automatic Slow Query Collection and Management​

PawSQL's performance inspection platform supports collection and management of slow queries in SQL Server, with both crontab-based scheduled collection and manual collection options.

8. Database Object Inspection​

PawSQL's performance inspection platform supports scheduled or manual inspection of database objects in SQL Server, covering tables, columns, character sets, indexes, constraints, and various other database objects to identify potential security and performance issues, providing appropriate alerts.

9. IDE interations​

The PawSQL optimization platform has been integrated with popular IDEs (VSCode and JetBrains), allowing developers to optimize SQL without leaving their development environment, improving work efficiency.

🌟 Summary​

PawSQL for SQL Server offers a comprehensive SQL optimization solution for SQL Server databases. Whether handling routine queries or complex data processing, PawSQL provides professional and efficient solutions to enhance your database performance.

OceanBase database has been widely adopted in finance, e-commerce, and government sectors due to its high availability, strong consistency, and high performance. As business scale expands and data volume surges, optimizing OceanBase database query performance becomes increasingly important. PawSQL provides comprehensive performance optimization support for OceanBase database, helping users fully unleash OceanBase's performance potential.

I. Core Optimization Technologies​

PawSQL introduces two specialized capabilities for OceanBase database:

1. Deep OceanBase SQL Syntax Support

• Dual-mode compatibility: Full support for MySQL/Oracle syntax systems
• Complete parsing of OceanBase-specific DDL syntax:

  CREATE TABLE nation_d (
      n_nationkey INTEGER NOT NULL PRIMARY KEY,
      n_name CHAR(25) NOT NULL,
      n_regionkey INTEGER NOT NULL,
      n_comment VARCHAR(152)
  ) duplicate_scope = cluster/none;
  
  CREATE TABLE part (
      p_partkey int NOT NULL PRIMARY KEY,
      p_name VARCHAR(55) NOT NULL,
      p_mfgr CHAR(25) NOT NULL,
      p_brand CHAR(10) NOT NULL,
      p_type VARCHAR(25) NOT NULL,
      p_size NUMBER NOT NULL,
      p_container CHAR(10) NOT NULL,
      p_retailprice NUMBER(15,2) NOT NULL,
      p_comment VARCHAR(23)
  ) PARTITION BY HASH(p_partkey) PARTITIONS 16;
  

2. Rule System Upgrade

• Three new distributed design standards:
  • Avoid table join fields that are not distribution keys
  • DML in distributed databases should avoid table joins
  • DML operations in distributed databases lack sharding field equality conditions
• Three new distribution key design standards:
  • Local tables are not recommended in distributed databases
  • Multiple fields are not recommended for distribution keys
  • Distribution keys should use highly distinctive fields
• Two new distribution strategy design standards:
  • Replicated distribution is not recommended for large tables
  • Hash distribution is recommended for distribution method

II. Product Matrix of SQL Lifecycle​

2.1 Development and Testing Phase: Intelligent SQL Optimization​

PawSQL Optimization Platform is a one-stop online SQL optimization tool for application developers and testers. It integrates industry best practices for relational database query optimization, helping application developers and database administrators solve SQL performance issues through query rewriting optimization and intelligent index recommendations. PawSQL Optimization Platform has completed integration with common IDEs, allowing developers to perform SQL optimization without leaving their development environment.

The PawSQL Optimization Platform is an online SQL optimization tool for developers and DBAs, incorporating industry-leading query optimization technologies, including:

• Intelligent Query Rewriting: Automatically optimizes inefficient SQL statements
• Index Recommendation Engine: Precisely recommends optimal index combinations
• Distributed Optimization Strategies: Provides specialized optimization suggestions for OceanBase's distributed characteristics

2.2 Integration Phase: Intelligent SQL Review​

PawSQL Review Platform, with its leading core technologies such as self-developed SQL parser, syntax tree-based rule matching, and context information updates, provides comprehensive and precise (accuracy over 95%) intelligent SQL review capabilities for SQL quality management teams. It conducts comprehensive checks from multiple dimensions including syntax specifications, performance efficiency, and security, providing targeted optimization suggestions to help enterprises improve SQL performance and application efficiency.

For OceanBase database's distributed characteristics, PawSQL provides specialized distributed query optimization suggestions, with applicable rules exceeding 190.

2.3 Operating Phase: Performance Patroller​

PawSQL Database Performance Patroller automatically captures slow queries generated in the database periodically and provides SQL optimization suggestions, including automatic SQL rewriting, intelligent index recommendations, and existing redundant index analysis. It automatically inspects database objects periodically, identifying potential performance, security, and maintainability issues, and provides optimization suggestions.

III. Summary​

PawSQL for OceanBase database provides you with a one-stop performance optimization solution. From daily query optimization to complex distributed scenario handling, PawSQL can help you tackle challenges with ease. 🚀 Experience PawSQL now and unlock OceanBase database's performance potential!

💡 Introduction​

PawSQL for Jetbrains (PawSQL Client) is a powerful Jetbrains plugin that seamlessly integrates PawSQL engine's intelligent SQL optimization capabilities into your development environment. It supports smart index recommendations, query rewriting, and automated performance validation. Working in conjunction with PawSQL Cloud (https://pawsql.com) or enterprise private deployment of the PawSQL optimization platform, it provides developers with one-click SQL performance optimization capabilities.

🎯 One-Click SQL Optimization in IDEA/DataGrip​

Select the SQL text to be optimized, right-click, and choose which workspace to perform SQL optimization

✨ Core Features​

• 🚀 One-Click Optimization - Intelligently analyze and optimize SQL query performance
• 📊 Index Optimization - Smart recommendations for optimal index combinations, comprehensively improving query efficiency
• 🔄 Query Rewriting - Automatically generate equivalent SQL statements with better performance
• 📈 Performance Validation - Precisely quantify optimization effects, providing detailed performance improvement data

🔧 Installation Guide​

Install from Jetbrains Marketplace​

1. Launch Jetbrains IDE tool (such as IDEA/DataGrip)
2. Search for "PawSQL Client" in Plugins and install

Install from Disk​

1. Download this plugin from Jetbrains Marketplace

2. Install it from Disk

⚙️ Initial Configuration​

1. Fill in the following information in the configuration interface:

• PawSQL Server address (e.g., https://pawsql.com)
• Account information
• Password

2. Click save to complete configuration

🌐 About PawSQL​

PawSQL focuses on database performance optimization automation and intelligence, providing solutions covering the entire process of SQL development, testing, and operations. It widely supports various mainstream commercial and open-source databases including KingbaseES, offering developers and enterprises a one-stop innovative SQL optimization solution. It effectively solves database SQL performance and quality issues, improves database system stability, application performance, and infrastructure utilization, saving enterprises significant maintenance costs and time investment.