sql performance [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

which sql below have better performance, and why?

1. table A(100 rows), table B(1000 rows)

   (1)sel开发者_开发技巧ect * from A, B; (2) select * from B, A;

2. (1) select * from T;

   (2) select col1, col2 from T;

---

1. The order of your tables does not matter, the same execution plan will be generated.

2. The second option will be slightly faster because less data is being transmitted to you. There's not much in it though.

---

1. All databases worth their salt will have the same performance. The database optimiser will find an efficient way regardless of the order.

Note: If you have a lot of tables in your query it may be harder for the optimiser, and then you may get different results with different order.

1. (I assume that T only has 2 columns) The difference will be innoticable. In most cases you want to specify the columns as that will keep your code easier to maintain and less bug-prone.

---

1: query (1) & (2) will produce the same cartesian product of 100x1000=1000x100 records, the performance is the same

1. (2) will be faster if either col1 or col2 are keys or have indexes, otherwise the faster is (1)