There are numerous database vendors in the world each with their own brand of SQ

Question

There are numerous database vendors in the world each with their own brand of SQL. There is also a SQL standard governed by the American National Standards Institute (ANSI). Most large database vendors practice some form of ANSI-standard SQL but will also have their own unique SQL commands.

1)Discuss two pros and two cons of establishing and using a standard for SQL.

2)There exist several vendor-specific implementations of SQL.

Select two different SQL implementations and provide a brief description of each. Include the name of the vendor.

Of the two implementations, you choose, in your opinion, which of the two is the "best" implementation? Provide the reasoning for your answer.?

3)What are some suggestions for improving query processing, discuss three suggestions for improving query processing.

Explanation / Answer

1) Pros of establishing and using a standard for SQL

(a)Integration: By establishing a standard for SQL it will help in smooth integration with multiple enterprise applications, this will help in providing more functionality and expanding the power of Querying. Various reporting solutions can develop APIs by using which the connectivity can be established and data needs can be fulfilled.

(b)Portability: When there is a standardized public definition for SQL, users can rest assured that any applications they develop to the specifications of that standard are portable to any environment that supports the same standard. This is an extremely important budgetary consideration for any large scale end user application development project.

Cons of establishing and using a standard for SQL

(a)Vulnerable System: By establishing a standard to SQl, it becomes vulnerable as anyone with the knowledge of the standard SQL can always intrude and impact their changes in the DB by using the standard setup.

(b) Performance: performance may also impact the standards as there might be different vendors losing their proprietary impact on the market hold and there by having poor revenues. Performance tuning of the Database is also having different approaches by different vendors and by standardizing the SQL this edge will be lost and no regard for competition.

3) suggestions for improving query processing

Avoid Multiple Joins in a Single Query: Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply and other complex sub queries. It reduces the choices for Optimizer to decide the join order and join type. Sometime, Optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or sub queries.

Creation and Use of Indexes : We are aware of the fact that Index can reduce the data retrieval time but have a reverse effect on DML operations, which may degrade query performance. With this fact, Indexing is a challenging task, but could help to improve SQL query performance and give you best query response time

Statistic Creation and Updates : take care of statistic creation and regular updates for computed columns and multi-columns referred in the query; the query optimizer uses information about the distribution of values in one or more columns of a table statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.

Optimizing the Schema Definitions: Place appropriate FORIGEN KEY, NOT NULL and CEHCK constraints. Availability of the right constraint on the right place always helps to improve the query performance, like FORIGEN KEY constraint helps to simplify joins by converting some outer or semi-joins to inner joins and CHECK constraint also helps a bit by removing unnecessary or redundant predicates.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.