There's a pretty long SQL, the query is very slow, but added after the union all speed from six minutes to ten seconds, but I really don't understand why, specific SQL statements below
When I was in part B of the SQL statement to add a union all of it doesn't matter, speed from the above six minutes into ten seconds

Take out this part A and part B separately query, speed is very fast in just A few seconds, when they join queries is very slow, but added union all speed increased after several times, really don't understand why, ask the great spirit guide, left connection in from A table and A union all can make faster

CodePudding user response:

Please check this two field data type is what? If inconsistent, can lead to poor performance when A left join B.
Db_Model. Rix_No
Db_Matrix. Rix_No

CodePudding user response:

The

reference 1/f, started the first reply:

please check these 2 what is the data type of the field? If inconsistent, can lead to poor performance when A left join B.
Db_Model. Rix_No
Db_Matrix. Rix_No

Two tables of the field type is the same as well

CodePudding user response:

Suggest the SQL execution plan actually, see the main cost in which link.

CodePudding user response:

After two sub queries, redo associated query, there should be a "predicate push"

CodePudding user response:

The

reference 3 floor started first response:

suggest to check the SQL execution plan, actual see major cost in which link.

This thing is happened in the two databases, both inside the database table structure is the same, but the inside of the data has perhaps the difference
I compared the two databases to database X and Y, when I put the SQL database X, do not add the Union all he was also quick, but put the SQL database Y, perform is very slow, it is important to note that the X in the database data quantity larger than Y database data quantity, has led to the data quantity is little Y database query instead more slowly, and the same two different database SQL execution plan, later I joined the Union in the SQL database Y after all, the execution plan would be as good as database X become

Different database, the same table structure, the same SQL statement execution plan is not the same as

CodePudding user response:

You are executed in the database tool and, without UNION ALL the time, whether there will be a lot of data, after the search, show only the front of a few, if it is after adding the UNION ALL, if the UNION ALL will show you,

CodePudding user response:

This is not the oracle?
In oracle, you add a irrelevant union all query not appear this kind of circumstance, unless it's cache effect, from the first time a few minutes into the second time a few seconds for the third time is possible, but it has nothing to do with you without the union all query

CodePudding user response:

refer to 6th floor AHUA1001 response:

you are executed in the database tool and, without UNION ALL the time, whether there will be a lot of data, after the search, show only the front of a few, if it is after adding the UNION ALL, isn't it will show you the UNION ALL,

This is the SQL server is not oracle, SQL server edition piece is too cold and cheerless, so move to oracle ask here

CodePudding user response:

refer to 7th floor minsic78 response:

this is not the oracle?
In oracle, you add a irrelevant union all query not appear this kind of circumstance, unless it's cache effect, from the first time a few minutes into the second time a few seconds for the third time is possible, but it has nothing to do with you without the union all query

In oracle, I do not encounter this problem, this is the SQL server

CodePudding user response:

refer to 6th floor AHUA1001 response:

you are executed in the database tool and, without UNION ALL the time, whether there will be a lot of data, after the search, show only the front of a few, if it is after adding the UNION ALL, isn't it will show you the UNION ALL,

This is SQL server, the query statements only more than two hundred data, without the union all query the data need to load all the data after 6 minutes, and only after more than ten seconds, and add do not add the union all query results are the same