Home Database Mysql Tutorial sybase的SQL多表联合查询调优

sybase的SQL多表联合查询调优

Jun 07, 2016 pm 05:28 PM
sybase

在大型项目中关系型数据库多表联合查询是很频繁的,现在项目上有以下7张表,每张表达数据量也比较小,但是7张表通过多表联合查询

摘要:在大型项目中关系型数据库多表联合查询是很频繁的,现在项目上有以下7张表,每张表达数据量也比较小,但是7张表通过多表联合查询,查询的速度却非常慢,,希望能够给出一个查询效率比较快的sql。
 
 
表1:OPER,字段id,operkey等等(数据量3500)
 
表2:POLICY,字段id,name等等(数据量5000)
 
表3:STRATEGY,字段id,name等等(数据量5000)
 
表4:STRATEGY_APPROVER,字段id,userid,approver等等(数据量50000)
 
表5:POLICY_OPER(表1和表2的中间表),字段policyid,operid
 
表6:STRATEGY_POLICY(表2和表3的中间表),字段policyid,strategyid
 
表7:STRATEGY_APPROVER_RELA(表3和表4的中间表),字段strategyid,strategyapproverid
 
 
 
项目上使用的查询语句如下:
 
select * from STRATEGY where st.id in (
 
      select * from STRATEGY st
 
          inner join  STRATEGY_POLICY sp on st.id = sp.strategyid
 
          inner join  POLICY p on p.id = sp.policyid
 
          inner join  POLICY_OPER po on po.policyid = p.id
 
          inner join  OPER o o.id = po.operid
 
          inner join STRATEGY_APPROVER_RELA  sar on sar.strategyid = st.id
 
          inner join  STRATEGY_APPROVER sa on sa.id = sar.strategyid
 
      where st.id=? and st.name=? and .... and ....  and
 

 
 
注:以上查询效率及其慢,已经超过用户体验的最差值,希望能够提供较快的查询效率的SQL;

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Two Point Museum: All Exhibits And Where To Find Them
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between oracle and sybase What is the difference between oracle and sybase Jun 01, 2023 pm 01:46 PM

The differences between Oracle and Sybase are: 1. SQL syntax is inconsistent; 2. The post-processing mechanism for over-long fields is different. Oracle does not have an automatic truncation mechanism when inputting over-long characters, while Sybase’s processing mechanism is automatic truncation; 3. Chinese field byte length Inconsistent, 1 Chinese character in the Sybase database occupies 2 bytes, and 1 Chinese character in the Oracle database occupies 3 bytes; 4. Character type conversion is different; 5. Null value reading results are inconsistent; 6. Query statement output default sorting is inconsistent; 7 , The date format is inconsistent.

What database is sybase? What database is sybase? Sep 22, 2021 am 11:39 AM

Sybase is a database based on client/server architecture. It is an open, high-performance, programmable database that can use event-driven triggers, multi-threading, etc. to improve performance.

Understand the features and differences between Sybase and Oracle Understand the features and differences between Sybase and Oracle Mar 09, 2024 am 08:27 AM

Title: Detailed explanation of the characteristics and differences between Sybase and Oracle databases In the modern information age, database management systems play a vital role. Sybase and Oracle, as two well-known relational database management systems, have attracted widespread attention. This article will delve into the features and differences between Sybase and Oracle, and further demonstrate the similarities and differences between them through specific code examples. 1. Sybase database features: Powerful transaction processing capabilities: Sybase database is known for its outstanding

Core differences between Sybase and Oracle database management systems Core differences between Sybase and Oracle database management systems Mar 08, 2024 pm 05:54 PM

The core differences between Sybase and Oracle database management systems require specific code examples. Database management systems play a vital role in the field of modern information technology. As two well-known relational database management systems, Sybase and Oracle occupy an important position in the database field. important position. Although they are both relational database management systems, there are some core differences in practical applications. This article will compare Sybase and Oracle from multiple perspectives, including architecture, syntax, performance, etc.

Comparative analysis of MySql and Sybase: How to choose the right tool according to different needs Comparative analysis of MySql and Sybase: How to choose the right tool according to different needs Jun 16, 2023 pm 12:12 PM

In today's technological era, with the advent of the Internet and data era, databases have become the core of enterprises' information storage. As the amount of data increases and continues to expand, enterprises need an efficient, stable, and fast database tool to meet their growing needs. Among the many database tools, MySql and Sybase are two database tools that have attracted much attention. This article will conduct a comparative analysis of MySql and Sybase to help users choose the appropriate database tool according to different needs. MySql is a

Sybase installation guide and steps Sybase installation guide and steps Jan 02, 2024 am 11:06 AM

System environment configuration 1. Sybase installation 1. Install the Sybase database. In the license agreement interface, select the language: People's Republic of China (PRC). Select the I agree option below. 2. Select the installation path. Note: It is best to choose an installation hard disk with an NTFS partition, because some files larger than 2g cannot be saved in a fat32 partition. 3. Select full installation 4. The actual installation functions are as follows: 5. Next step installation 6. Installation completed 7. Next step 8. Pay attention to select customized configuration information. Next step 9. Select the page size: 8K, and use the default 10 for others. , Realize the installation configuration information. After the next step is successfully configured, restart. 2. Sybase upgrade 1. Select setup installation, as follows 2. Select the protocol language

Comparison of functions and performance of Sybase and Oracle database systems Comparison of functions and performance of Sybase and Oracle database systems Mar 08, 2024 pm 10:24 PM

Comparison of functions and performance of Sybase and Oracle database systems With the continuous development and progress of information technology, database systems, as one of the infrastructures for enterprise management information, play a vital role. Sybase and Oracle, as one of the mainstream relational database management systems (RDBMS), are widely used in their respective fields. This article will compare the functions and performance of two database systems, Sybase and Oracle, and analyze them with specific code examples. Basic

Look at the similarities and differences between Sybase and Oracle databases from different angles Look at the similarities and differences between Sybase and Oracle databases from different angles Mar 09, 2024 am 08:57 AM

Sybase and Oracle are two popular database management systems in the field of enterprise-level databases. They have their own advantages in terms of function, performance, reliability, etc., and are often widely used in practical applications. This article will explore the similarities and differences between Sybase and Oracle databases from different perspectives, and illustrate them with specific code examples. 1. Data type support In database design and development, the choice of data type is crucial. Both Sybase and Oracle support common data types

See all articles