The nested loop strategy may be effective for database optimization when no indexes exist on either table for the join column (in this case, DeptNum). The database developer has two options here. We can either choose to use EMPLOYEE or DEPARTMENT in the outer loop, and the choice we make is very important. In general, the database developer will want to choose the table with the smaller number of records for the outer loop. This can drastically reduce the number of loop iterations. The DBMS will make this determination automatically, but it is helpful to be aware of what’s going on behind the scenes when writing queries.
Using EMPLOYEE as the outer loop:
C=be+ (be*bd)= 10,000+(10,000*5)= 60,000 block accesses.
Using DEPARTMENT as the outer loop:
C=bd + (bd*be)= 5+(5*10,000)= 50,005 block accesses.
Using DEPARTMENT as the outer loop cut the number of block accesses down by about 10,000, which is a substantial improvement.
Ayoka is a Made in USA enterprise application services company with one clear objective: delivering the best customer service to all of our clients. Ayoka’s commitment to Made in USA custom software development ensures that our client’s culture is understood, objectives are clearly communicated and allows us to provide tangible advice to our clients that are building custom enterprise applications that are essential to operating their modern business.
Ayoka’s custom software development culture combines the entrepreneurial feel of a start-up company with the confidence and stability of a successful professional services firm. Our vision is to become the ONLY choice for affordable enterprise software development and custom software development in AMERICA. We are proud of our consistent track record of delivering successful projects on time and on budget. We strive to provide custom software development projects that make our clients money. Get in touch today to see how Ayoka’s services can benefit your company.