Still following up on last week's discussion, related to Database System Assignment 2, where students were asked to observe the differences that occur between the following query codes.
SELECT * FROM products p, order_details o WHERE p.ProductID=o.ProductIDAnd
SELECT * FROM products p JOIN order_details od ON od.ProductID=p.ProductID
Both query codes are representations of the following relational schema.
Then students are asked to provide reasons related to the statement below.
1. Both codes are correct?
Answer: {CORRECT} because both codes express the same output,
2. Is there an explicit difference between the two codes?
Answer: {ADA} is located at Query Time
3. In your opinion, which is the best? What are the reasons? (include empirical evidence)
FIRST CODE
SELECT * FROM products p, order_details o WHERE p.ProductID=o.ProductID
The first code is BETTER, because it has a faster algorithm, which can be seen from the number of query times consumed, which is smaller, while the second code tends to consume more query time.
With the same cycle time and cycle run, the following data comparison is obtained:
Step 1
By limiting the processor's task to concentrate on an observation (process management), in the following manner.
Step 2
Executing SQL code in the HeidiSQL window or by pressing F9 on the keyboard repeatedly until the specified limit will produce a query time data comparison like this.
Query Times Table
From the 20 samples taken, it turns out that a very significant comparison has been obtained, therefore it can be concluded that the query algorithm in the First Code is better than the Second Code. This analysis can be applied to each database that you have, or if you don't have one, especially students who may be new, and may not have followed this discussion from the beginning, then here I present the northwind database again so that friends can experiment, for that download it HERE .