In recent two days to study optimization. There is a problem confused ah. Help each master.

According to the general order of instructions, it should be
First the FROM
Then the WHERE
Then SELECT the

That assumes that there are multiple relations, in the worst situation is the FROM doing the cartesian product of two relations,
This time is arguably has all of the attributes parsing the new relationship. Then the problem is coming!

With a select * and the performance of the select attribute name isn't the same!!!!!!

Are you kidding me? Still have other terrier I don't know yet.

CodePudding user response:

Are you talking about resolving attribute or query efficiency, the cartesian product and attributes have what relation, select * and use the select attribute names, performance difference is feeble and micro basically, suggest or attribute name, because you wrote *, if the field changes the program may result in an error,

CodePudding user response:

reference 1st floor sxq129601 response:

are you talking about resolving attribute or query efficiency, the cartesian product and attributes have what relation, select * and use the select attribute names, performance difference is feeble and micro basically, suggest or attribute name, because you wrote, if the field changes may lead to a program error,

ah, of course is that the query efficiency... Well it seems is the same

CodePudding user response:

reference 1st floor sxq129601 response:

are you talking about resolving attribute or query efficiency, the cartesian product and attributes have what relation, select * and use the select attribute names, performance difference is feeble and micro basically, suggest or attribute name, because you wrote *, if the field changes may lead to a program error,

To see again, you mean write *, a change in the field of parsing out, call this SQL program complains.

But don't write star, if the base table field name change, SQL will be an error... The result is not the same. All results also no difference...
In addition you said the cartesian product and attributes have what relation, looking at the concept of database system, there is a principle need to supplement.
If you are behind the from the statement with two tables, for example,

Select *
The from instructor, course;
In accordance with the resolution order should be first cartesian product of two tables, as a new table to input to the select statement,
Situation should be the driver table all columns and table driven all columns, and then return all rows, this is where the new table,
At this time to see the select qualification,
* and all the performance of the column name should be consistent, because according to the above, * and all column names in the from of statements to do that step has done
In this step only display screen,
All optimization is focused on the from the table behind the statement, rather than a select statement should write star, this is the question, two days before the
But looks like in reality rarely see someone on the from optimization, most of them are directly write the name of the table,

CodePudding user response:

with select * and the performance of the select attribute name isn't the same!!!!!!


A total of 50 columns, for example, write * is return to 50 columns, select col1, col2, col3, the writing is to write a part of the column, may only be 10 columns, also may be five columns,

The original poster can return a different number of columns with the client, look at the response time, is not the same, especially when a LOB field, gap is very big still,

CodePudding user response:

HMM. Moderators adults.
May I understand above slightly exaggerated. I want to talk about the same conditions. The performance is the same.
For example, a total of 50 columns, for example, * will no doubt be back 50 columns, and I write directly 50 column names, of course, also return to 50 columns. The performance should be consistent.

And the beginning of open posts because, in fact performance difference is in the FROM statements. And select statement should be little impact on final statement execution time.


The

reference 4 floor wmxcn2000 reply:

with select * and the performance of the select attribute name isn't the same!!!!!!


A total of 50 columns, for example, write * is return to 50 columns, select col1, col2, col3, the writing is to write a part of the column, may only be 10 columns, also may be five columns,

The original poster can return a different number of columns with the client, look at the response time, is not the same, especially when a LOB field, gap is very big still,

CodePudding user response:

For normal SQL (cartesian product is ignored, or bugs, and so on and so forth), the return data to the client out of the steps on the database side, affect the performance of the biggest is the SQL execution plan, how to access SQL contained in the object, is the index and table scan or walk? Is nested loops, or walk a hash join... Concurrent high and low, of course, also can affect the performance of the whole library, from the perspective of the whole library, lower the SQL statement execution time alone, in the case of high concurrency, not necessarily good long SQL executed when alone,