Sybase Interview Question's - SET V:

1. Explain Stored Procedures on performance?Query optimizer generates a query plan for a st. proc based on the parameters parsed in the first time it is executed. To make st.prc to perform well, first the quires used in the procedure are to be checked. Also since it is compiled and stored in memory, procedure may become inefficient over time. Further execution used only if it is gain otherwise again the st. proc is recompiled.For example when it was first compiled, it may be fast as the table size is small and the table scan is better choice. But when the table grows into huge, index selection may be a better option than the table scans for that query. But the procedure will still do only table scan as it is pre-compiled, so as a general rule, if the table size changes by 20%, we have to run update statistics on the table and recompile the stored procedure. For stored proc in which, the parameter has the strong influence over the result set, we have to make it with recompile option, so that it compiles every time it is accessed.
2. What is sp_dboption?It is used to enable or disable db option for your database
3. Explain Denormalization techniques?Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently
4. Which is better Stored procedure or Trigger?1.If you intend to set or modify some column values in the proc/trig, a trig is the right place to do it. A BEFORE trigger can directly modify the row buffer before it is updated/inserted. A procedure would have to issue a UPDATE, thus a performance penalty.2. The trigger has all the row data automatically available to it. For the procedure to have the same information, you'll either have to pass all values to the proc via parms, or the proc will need to re-read the data, a performance penalty.3. If your application issues a single update, delete, or insert that modifies multiple rows, a FOR EACH ROW trigger will be fired for each row. It could be difficult to provide the SP the information that allows it to identify the set of rows that were modified.
5. I made two tables A B id1 age id1 age 1 Asked me to find out all the id1's which are in table A and select * from A where not exists(select id1 from B) do not exist in table b
6. How did you know the index was the cause of the performance degration?
7. Compare left and Right Outer Join?For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL
8. What is Join and Its Types?Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
9. what are different global variables ?@@rowcount-returns no of rows processed by preceeding command.@@error- returns the error code for last or current query.@@transtate- returns the current transaction state. @@tranchained-returns the current transcation mode.@@server- returns the server name.@@version-returns the version of the sql server and OS used in a system.@@spid- current process id.@@identity-returns the lastly incremented identity value.@@nestlevel-No of level in stored procedures/trigger.@@sqlstatus-status of previous fetch statement in cursor.
10. what is temporary tables and its types?Sub query: A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.