Question

1. How do indexes improve SQL query performance?

2. How can stored procedure design improve query times in a data mart?

3. Why/How does de-normalization of a data mart design improve performance of queries?

4. What is a different between Client-Servervs. Distributed Architecture?

5. What are some primary features of a 3-tier architecture design?

Answer 1

1. To search for an information in a database we need to search every row until we find it. But with indexing we can reduce the search time ,they are pointers from each record to the corresponding original record where data is actually lresent and they order the data on disk to give the search result fast. Now creating too many or too less index is also not good.

2. Stored procedures help in improving the query at data mart as we dont need to rewrite the same query again and again . We give a name to this query with the help of stored procedure and use this name in database whenever we need the query. This saves the time in rewriting and also the confusion.

3. We normalize the table to remove redundancy and get more space . As storing the same information we use up a lot of space. But denormalization is inportant when we need the result more frequently than any other . As joins on tables take much time we can denormalize the table to get fast results .

4. In distributed architecture as the name suggests the data is distributed on multiple servers . These servers have complete database but when a user asks a query they work together to give the result. This is important as many users can access the database at the same time and the distribution helps to fetch the results fast

In client server architecture there is only 1 server and all database is present there only. In case if ther server fails whole data is lost. Also only 1 user can access them at a time also they have slow speed.

5. The 3 tier architecture has 3 levels:-

i)Physical Level

ii) Conceptual Level

iii)External Level

Here one level that is conceptual is added more than that of 2 tier . This extra level ensures that the user can not access the databse directly

At physical level information about where the data is actually stored is present.

At conceptual level the data is present in the form of tables.

At external level is where the user interacts . They use application to get the data