Sybase problems 3

1. I have written a query which you can see below. The query qpproximately takes 50- 60 SEC to run,
which I believe is way too high. The total rows returned is 10,000 .
Any idea to improve the query timings ?

The number of rows in each table are as follows :
ACCOUNTS : 1,580
PERSONNEL : 1,616
DIVISION : 15
LABOR_COMMITTED : 23,000
FISCAL_SYSTEM_PERIODS : 20
FISCAL_YEARS : 20

INDEXES :
ACCOUNTS TABLE- 1. ACCOUNT_ID ( Primary Key), 2. IN_AC_COMBINED(ACCOUNT_ID , ACCOUNT_MANAGER) LABOR_COMMITTED TABLE- 1.LABOR_COMMIT_ID ( Primary Key) 2. IN_LC_COMBINED(PERSONNEL_ID, ACCOUNT_ID) PERSONNEL TABLE- 1. PERSONNEL_ID ( Primary key)

The Actual Query :
SELECT AC.ACCOUNT_NUMBER , AC.ACCOUNT_NAME , AC.ACCOUNT_END_DATE , P1.NAME , D.DIVISION_NUMBER , P.SURNAME_ISI || ' '|| P.FIRST_NAME Person_Name, ISNULL(P.EMPLOYEE_ID,P.VISITOR_NUMBER) Employee_Number, CONVERT(DATETIME , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR) Month_Year , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR Month/Year, ROUND(LC.LCS_EFFORT,2) LCS_Effort , 'ACTUAL' AS 'Labor Type' , (SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) STAFF_CAT FROM ACCOUNTS AC(INDEX IN_AC_COMBINED), PERSONNEL P1 ,DIVISIONS D, LABOR_COMMITED LC (index in_lc_combined) , PERSONNEL P , FISCAL_SYSTEM_PERIODS FSP , FISCAL_YEARS FY WHERE P1.PERSONNEL_ID IN (SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT WHERE PARENT.PERSONNEL_ID IN (7167,7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0) AND AC.ACCOUNT_MANAGER = P1.PERSONNEL_ID AND P1.DIVISION_ID = D.DIVISION_ID AND AC.ACCOUNT_ID = LC.ACCOUNT_ID AND LC.PERSONNEL_ID = P.PERSONNEL_ID AND FSP.FISCAL_SYSTEM_PERIOD_ID = LC.FISCAL_PERIOD_ID AND FY.FISCAL_YEAR_ID = LC.FISCAL_YEAR_ID AND LC.COMMITED_DATE IS NOT NULL AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007') AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')

----------------------------------------------------------------------------------------------------------------------------
2. I have a table with 10 million rows. about 9.5 million rows are identified by certain criteria must be deleted from this table.
If i use DELETE statment for this, optimizer takes nearly 15-20 minutes to execute the query,so how can i minimize the execution time?

------------------------------------------------------------------------------------------------------------------------------

3. I was working on a stored procedure for a pending QC . I need to negate the bskSymbol which have multiple consSymbol .

so for that i am using the query given below and saving that in a #table .

select bskSymbol from RSK_IM_ModifyBasket group by bskSymbol having count(*) > 1 and BatchId = 0

or Like ->

select into ... #temporary table
....
bskSymbol not in (select bskSymbol
from RSK_IM_ModifyBasket b
group by bskSymbol
having count(*) > 1 and BatchId = 0
)

does not produce any one to one mapping of bskSymbol and consSymbol .

where as the query given below shows some bskSymbol columns with one to one mapping of bskSymbol and consSymbol . by one to one mapping i mean for some bskSymbol we have count value as 1 .

select bskSymbol,count(consSymbol) from RSK_IM_ModifyBasket where BatchId = 0 group by bskSymbol

can u suggest where am i wrong ?.

---------------------------------------------------------------------------------------------------------------------------------------------------------
Hi friends,
I am facing a problem with bcp process on the following environment
OS - sun solaris
database - sybase

I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
any suggestion will be appriciated.

sol:

think you might be limited by your disk I/O speeds. But things you can use to try and speed up the process is a fifo special file (man mkfifo) as the bcp file and a larger network packet size (bcp -A 8704)
Would it be worth altering your unix script to do one table at a time. It might avoid the competition for resources. It would be good to time each stage so you can see where the issue is.

Do you drop the indexes on the destination table before bcping in, then create them again afterwards? hoping to use fast bcp.

Another option might be to try putting the 13 tables into their own database and simply dump the database at the source and restore it on the destination rather than bcping in and out each table.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Does anyone know of an easy way to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sybase problems 2

Cursor - Not fetching next record
Posted: Apr 13, 2007 5:45 PM Reply


Hi,
I am BusinessObjects Developer and new to Sybase-IQ.
I had created on stored procedure and facing an issue that the cursor is not fetching next record.
Can anybody help to debug this issue.

Below is the stored procedure.
declare @catcode varchar(12);
declare @MX integer;
declare @MTH integer;
declare @Yr integer;
declare @Yr_Max integer;
declare @Yr_Min integer;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
if sqlstate = err_notfound then
print sqlstate;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
close CRS_catcode
end
end

-------------------------------------------------------------------------------------------------------------
Re: Cursor - Not fetching next record
Posted: Jul 12, 2007 5:34 PM Reply


hi dear
u have not use fetch next statement in your sp before the close of the cursor

try this , i think it will help u


/* Notice: Formatted SQL is not the same as input */
(52,32)then
DECLARE @catcode VARCHAR(12);
DECLARE @MX INTEGER;
DECLARE @MTH INTEGER;
DECLARE @Yr INTEGER;
DECLARE @Yr_Max INTEGER;
DECLARE @Yr_Min INTEGER;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;

set @Yr=@Yr+1
end loop
end if;

IF sqlState = Err_NotFound
IF sqlState = Err_NotFound Then
PRINT sqlState;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
----------------------------------------------
fetch next CRS_catcode
into @catcode ;
-----------------------------------------------
end

close CRS_catcode

end

Sybase problems

1. I am facing a problem with bcp process on the following environment
OS - Linux
database - sybase

I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
------------------------------------------------------------------------------------------------------------------------------------------------------------
2. I am having five user in each Sybase server,Now i want to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers how do i do this in efficient way.
------------------------------------------------------------------------------------------------------------------------------------------------------------
3. I have the following procedure:

create PROCEDURE sp_SEARCH_Contragents(@nSPFirmID integer,@nFilterID integer,@szName varchar(255)=null,@szTaxNum varchar(255)=null,@szBULSTAT varchar(255)=null,@szFullCode varchar(255)=null,@szCity varchar(255)=null,@szPhone varchar(255)=null,@szAddress varchar(255)=null)
as
begin
declare @szNameSign varchar(2),
@szTaxNumSign varchar(2),
@szBULSTATSign varchar(2),
@szFullCodeSign varchar(2),
@szCitySign varchar(2),
@szPhoneSign varchar(2),
@szAddressSign varchar(2)


create table #tResult(
Name varchar(255) null,
TaxNum varchar(30) null,
BULSTAT varchar(10) null,
FullCode varchar(100) null,
City varchar(255) null,
Phone varchar(100) null,
Address varchar(255) null,
FoldID integer null,
)


insert into #tResult( Name,TaxNum,BULSTAT,FullCode,City,Phone,Address,Fo ldID)
select..........from where.............

insert into #tResult(Name,TaxNum,BULSTAT,FullCode,City,Phone,A ddress,FoldID)
select.................. from where..............


select Name as Name,
TaxNum as TaxNum,
BULSTAT as BULSTAT,
FullCode as FullCode,
City as City,
Phone as Phone,
Address as Address,
FoldID as FoldID
from #tResult
end

If I execute the procedure from ISQL with some input values it is working correctly. But if I try to execute it from Java like

ResultSet res = stmt.executeQuery("exec sp_SEARCH_Contragents ");

or like this

CallableStatement cs = conn.PreparedCall("{call sp_SEARCH_Contragents )}");

I don't get any rows or error,could you fick the problem where i am having ?

Set III

If you are create trigger for insert in which you are going to insert 10 rows how many time it will fire?

Only once

2

Compare IN and EXISTS?

in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns

3

What is Index Covering?

Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.

4

What is Checkpoint?

The point at which all data pages that have been changed are guaranteed to have been written to the database device.

5

What is direct update and deffered update?

Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.
Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.

6

What is @@spid,@@rowcount,@@error?

returns the no of rows affected by the preceding query

7

I what to see all locks in the server?

sp_lock,syslocks

8

What is sp_help and sp_helpdb?

sp_help use to display the objects in a database,sp_helpdb used to display the databases in your server

9

What are the different types of lock in Sybase?

Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.

Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

10

Explain Join order for Performance?

The optimal query plan for a join involves picking the best indexes for each table and the most efficient order to process the tables in the joins. The query optimizer checks all the join orders for cost optimization. The minimum I/O time which reduces cost will be selected for processing. If many tables are joined, SQL breaks into possible tables of a group. Examine the tables in the group and query optimizer performs permutation on each table and makes the final four tables, which minimize the cost and I/Os.

11

Explain Index and Performance?

Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.

In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals

12

what is a ghost record ?

These are records that have been logically deleted but not physically deleted from the leaf level of an index.
The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.
Ghost records will be mentioned later in the series when I discuss page compaction.

13

Compare Delete and Truncate?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

14

What is the use of timestamp?

timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes.

15

What are the Inbuilt User defined datatypes available in Sybase?

sysnames,timestamps

16

Write a query to select all records that do not have a null value?

17

How the Hotspot can be Resolved?

using heap table

18

How can I select the first 500 rows from a table having million rows & generate a flat file?

set rowcount 500

19

Write a query to find out the Nth max and min?

To find Nth max:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)

To find Nth min:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name

20

What system procedures are used to list all the indexes?

sp_indexes

21

What are the modes of transaction? And how do you change from one mode to another?

unchained mode: The default mode, called unchained or Transact-SQL mode,
requires explicit begin transaction statements paired with commit
transaction or rollback transaction statements to complete the
transaction.

chained mode: The SQL standards-compatible mode, called chained mode,
implicitly begins a transaction before any data retrieval or
modification statement. These statements include: delete, insert,
open, fetch, select, and update. You must still explicitly end the
transaction with commit transaction or rollback transaction.
You can set either mode using the chained option of the set command.

23

How do u run a file with lot of queries on ISQL?

isql -U -P -S -iinput file

24

What is the error code of Deadlock and How will you eliminate Deadlock?

errorcode for dead lock is 1205 and for stored procedure is -3.

25

How do you handle Error in Sybase?

The @@ERROR Function: SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.

SP_ADDMESSAGE:Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:


RAISERROR:You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.

26

How do you handle error in BCP?

-e

27

Write a query to convert the date to dd/mm/yy?

select convert(char,date_exp,101)

28

When a query is sent to the database and an index is not being used, what type of execution is taking

table scan

place?

29

What is an advantage to using a stored procedure as opposed to passing an SQL query from an

faster execution , avoid dead lock,avoid network traffic

application

30

What are defaults? Is there a column to which a default can't be bound?

When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound

31

Where do you think the users names and passwords will be stored in sql server?

syslogins

32

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).

33

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

34

What is bit datatype and what's the information that can be stored inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

35

What are constraints? Explain different types of constraints?

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

36

What are “GRANT” and “REVOKE’ statements?

37

What are Wildcard operators in sybase?

Selects documents that contain matches to a character string containing variables. The WILDCARD operator lets you define a search string with variables, which can be used to locate related word matches in documents.

38

How will you find second maximum value in a table?

select max(column) from table where column<(select max(column) from table)

39

What is Optimistic locking?

Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.

40

What are the types of Direct update not in place?

cheap direct update and Expensive direct update

41

How the Trigger differ from rules and constraints?

42

If I want current database name?

db_name()

43

If I want to check my query syntax what shall I do?

set parseonly on

44

Why we should go for Deallocating the cursors?

45

How do you call the Remote stored Procedures?

exec server_name.db_name.proc_name

46

What is the Drawbacks of Normalization?

Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. The acceptance of reduced performance requires the knowledge that when a query or transaction request is sent to the database, there are factors involved, such as CPU usage, memory usage, and input/output (I/O). To make a long story short, a normalized database requires much more CPU, memory, and I/O to process transactions and database queries than does a denormalized database. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data. A more in-depth discussion concerning database performance occurs in Hour 18, "Managing Database Users."

47

Explain BCP and Performance?

48

Explain Tempdb and Performance?

49

What is Thresholds and how will you add ?

50

What is the maximum row length limited by size of data page in SQL Server?

2KB

Set II

what is In & OUT in stored procedure?

The output keyword may be specified in the sp creation statement if the parameters can be passed both in and out of the sp,when passing out you must specifiy output keyword in execution statement

2

Tell me the limitation of the Trigger?

any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger

3

can I update view?

yes,but view had created using only one table than update is possiable

4

what is difference between constraints and rules?

rules deals with constant and one rule can bound one column,constraints can bound many column and it can be compare with column in another table

5

how will you bind a rule?

sp_bindrule

6

explain about @@sqlstatus?

It returns the status of the pervious fetch statement in a cursor

7

What are the steps involved in creating cursor?

Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors

8

What is Transaction Log?

It is a database level system table called syslogs,which contains a sequential list of all modification to every objects in the database.

9

compare Rollback transaction and Rollback Trigger?

10

How will bcp out the Identity column?

-E

11

What is Demand lock and Dead lock?

A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. Demand Lock:A demand lock prevents any more shared locks from being set on a data resource (table or data page). Any new shared lock request has to wait for the demand lock request to finish.

12

How to get the last month’s last Day?

select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))

13

What is patindex()?

returns the starting postion of the specified ,else 0

14

How do I get the Duplicate rows from a table?

select * from table name group by column1 having count(*)>1

15

what is command permission and object permission?

OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults)

16

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.

17

What is sp_dboption?

It is used to enable or disable db option for your database

18

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

19

Which is better Stored procedure or Trigger?

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.

20

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

21

How did you know the index was the cause of the performance degration?

22

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

23

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.

24

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.

25

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.

26

Compare Union and Union all?

union remove the duplicates and go for worktable creation where as union all allow duplicates and don't go for WT

27

Compare Subquery and Correlated Subquery?

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.

28

What is Update Statistics?

29

Compare Primary key and Unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

30

Advantages and disadvantages of cursors?

31

Compare Select into and Insert into?

select into it will create default table and copy the value to that table from other,insert into use to insert the value only in existing table

32

What is “COMPUTE BY” clause in Sybase?

When ever we use compute by ,we must also include order by

33

What is Check point in Transaction Log?

Checkpoint writes all dirty pages for the database from cache to disk,starting with the log and also it reduces the amount of work the server need to do at recovery time

34

Compare 2 nd and 3 rd Normal form?

Second normal form-nonkey fields must depend on the entire pr. Key.
Third normal form---nonkey fields must not depend on other nonkey fields.

35

What is –I option in Sybase tools?

It is the name of the interface file to use when trying to find a server to connect to

36

Is there any way to create Primary or Foreignkey on a table other than Query?

sp_primarykey,sp_foreginkey

37

If I want to round the value 999.99 as 1000 ?

round(999.99,0)

38

What is Distribution page?

server keeps distribution information for each index on a separate page in the datebase

39

Compare Composite Index and Multiple Index

40

What is Row Padding?

Locking of the page for a long time make the other process to wait. So the time required to complete the process will increase which is not desirable. SQL prevents dirty reads by using shared locks. Make the row big enough so that only one row fit on a page which avoid the locking of page. This is called row padding.

41

Explain Database and Performance?

42

How will you create Login name and User in Sybase?

sp_addlogin,sp_adduser

43

Is it necessary to drop & recreate all procedures and triggers every few months ?

44

What is difference between Count() and Count(*)?

count(*) will inclued null rows and faster

45

Advantage of Stored Procedure?

faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency

46

In a query which is better? using IN or EXISTS?

in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns

47

what are triggers? advantage ? disadvantage?

48

How do u copy the output results into a file?

isql -Usa -P -S -oos_file_name

49

Write a query to find out the Nth max and min?

To find Nth max:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)

To find Nth min:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name

50

CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after

non clustered index

executing the above statement?

Questions and Answers Set I

Questions
Answers
1
what is stored procedure?what is the maximum parameter can pass through it?
It is a db objects that exists independly of the table ,Which contain a set of query.maximum paraperters pass through it is 255
2
what is Advantage of stored procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
3
what is Trigger?how many trigger can a table have and what are?
It is internal part of a statement that fired when insert,update,delete operation taken place.Three trigger,Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
4
What is magic table?
The table that exists within a trigger named as Inserted and deleted are Magic table
5
what is view?how many table can join for creating view?
It is logical way of looking at physical data located in a table,16
6
how will you add a column and drop a column?
Alter table table name [add][drop] column name
7
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor
8
compare WHERE clause and HAVING clause?
Where clause cannot have Aggeregate function ,and it select the row before the group by function .having select the row after the group by
9
What is dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.
10
What is sp_dboption?
It is used to enable or disable db option for your database
11
What is CURSOR?
It is a pointer , use for row by row operation
12
Why we should go for Deallocating the cursors?
When we Deallocate the cursor so that server will clear the memory space occupied by the cursor we can use that space for some other action
13
How many index can have for a table?
250
14
What is Clustered and Nonclustered index?
When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater
15
How do I get the Duplicate rows from a table?
select * from table group by column having count(*)>1
16
While creating index I forgot to tell what type what will happen?
nonclustered index
17
Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
18
What is BCP?and Types?
It is used for import and export the values from table to os file and vice-ver.Fast bcp and Slow bcp
19
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
20
While bcp in default,rules,constraints can be applied?
Only default can apply
21
What is Hotspot?
Whan a multipe processes attempt to modify a same page in table
22
How do I force the lock?
Using Holdlock
23
What is Isolation levels and explain them?
The categories of locking behavior within transaction that are defined by ANSI,Level are 0,1,2,3
24
how to get Yesterday’s date?
dataadd(dd,-1,getdate())
25
What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to
26
How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name
27
What are defaults? Is there a column to which a default can't be bound?
When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound
28
If I want current database name?
db_name()
29
If i want creation text of a particular object (such as SP,Trigger,view,rule,default)what shall i do
sp_helptext,defncopy,syscomments
30
Compare Join and SubQuery in performance?
Generally Join queries consume more memory than sub query. Sub query in turn involve intermediate table creation so affects performance, because of high I/Os fetches need for processing. If the RAM memory is more, then the Joins can be used instead of sub queries. Under memory constraints we can go for sub queries.
Sometimes sub queries are flattened to join to improve the performance. Also making the outer query to compare inner query with equality option can materialize the subquery.
31
What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.

Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
32
List out all System procedure in your server?
select name from sysobjects where type='P'
33
There's a performance issue What will be your very first step towards the solution?
First you need to identify which query is creating the problem and than to identify where the problem lies in that query. Query that is taking maximum time is the first you want to consider for the optimization. To check which query is taking maximum time to execute place getdate() both before and after the query. Identity the type of the query whether it is a data look-up query, or data modification query.
34
What is @@rowcount?
returns the no of rows affected by the preceding query
35
How do you check whether the rollback was correct?
By checking the @@trancount value before rollback statement execute
36
What is Index Covering?
Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.
37
How do you select unique rows using Sybase?
using Distinct keyword
39
How many database Sybase 11 have ,list out with explanations?
four.Master,Model,sybsystemprocs,tempdb
40
What is Roll Forward and Roll Back?
Roll forward : commited transaction not written to data area are rolled forward into the data.Roll back:uncommited transaction rollback all data modification done with in transaction are reversed
41
If I want to round the value 999.99 as 1000 ?
round(999.99,0)
42
What is Phantom reads?
when one transaction reads a set of rows that satisfy a search
condition, and then a second transaction modifies the data (through an
insert, delete, update, and so on). If the first transaction repeats
the read with the same search conditions, it obtains a different set
of rows.
43
How will you Restart and Exit on while loop?
continue,break
44
Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
45
How many columns can table have?
250
46
How will you find second maximum value in a table?
select max(column1) from table where column1<(select max(column1) from table)
47
How shall I simulate from level 0 to level 3 in Isolation?
using holdlock
48
Can I explicitly insert a value in a Identity column?
set identity_insert on
49
Can I change the data type of column ?
yes using modify keyword
50
How will you tune a query?

Q 122

I) 1. Give some notes on Indexes.

2. Expalin the steps involved during the first execution and subsequent executions of a stored

procedure.

3.State whether the following statements are true or false

1) Wildcards can be contained in values passed to stored procedure-T(like)

2) Rules, defaults and column properties do not apply to parameters defined with user defiend datatypes -T

3) Parameter name should be 20 characters in length-F(29)

4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)

5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)

4. Explain the set commands given below

1) set rowcount (Instruct the server to rturn only the first n rows of data)

2) set statistics io on (Asks the server for the no. of logical and physical page requests)

3) set nocount on ( stops reporting the number of rows returned)

4) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)

5) set statistics time on (requests the execution time)

5. What is isolation level, list different isolation levels in Sybase and what is default?

To avoid the manual overriding of locking, we have transaction isolation level which are tied

with transaction.

List of different isolation levels are isolation level 0,1,2,3.

Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.

Isolation level 0-This allows reading pages that currently are being modified. It allows dirty

read

Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read

Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction

Isolation level 1 is the default.

II) The database has the Following relations:

Classes(class,type,country,numGuns,bore,displacement)

Ships(name,class,launched)

Battles(name,date)

Outcomes(ship,battle,result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, class name does not coincide with any ship name in the database). The relation Classes includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The relation Ships includes the ship's name, its class name, and launch year. The relation Battles covers the name and date of the battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the relation Outcomes. Note: the relation Outcomes may include the ships not included in the relation Ships.

  1. Point out the battles in which at least three ships from the same country took part.
  2. Find all ship names beginning with letter "R".
  3. For each class, define the number of ships of this class that were sunk in battles. Result set: class, number of sunked ships.
  4. For each class, consisting of at least three ships, define the number of ships (that must be at least 1) of this class sunk in battles. Result set: class, number of sunken ships.

Q 121

I) 1. State whether the following is true or false

1) The object names of the SQL server can be upto 50 characters in length and are case-sensitive

2) The name of the tables can be same in the same database

3) The default property for the identity column is null

4) Identity columns must use the numeric datatype and have a scale of 0

Ans: F-30, T, F, T

2. Answer the following

1) A numeric column allows to store upto _38__ decimal places

2) The scale of the numeric datatype for identity column is_0___

3) Views can have __16__levels of nesting

4) Maximum number of parameters the stored procedure can accept is _255__

(set identity_insert table_name on)

3. Explain the views with check option

4. Give the function name for the description given

1) Largest integer less than or equal to the specified value-floor(num_expr)

2) Current active roles for user-show_role()

3) Returns a specified part of date_expr value as a string- datename(datepart,dateexpr)

4) Replace expr1, if null with expr2- isnull(expr1, expr2)

5) Generates string of int_expr spaces- space(int_exp)

6) Returns length of expr in bytes-datalength(expr)

5. What are the different kinds of locks in sybase?

Locking: The process of restricting access to resources in a multi-user environment to

maintain security and prevent concurrent access problems. SQL Server

automatically applies locks to tables or pages.

Shared locks—SQL server applies shared lock for read operations. All the processes can read, but no process can write. (Eg- select statement.)

Exclusive lock--- SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

Update lock –allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

1) Find the maker producing at least two different PC having speed

not less than 750 MGz. Result set: Maker.

2) Find out Nth min price which includes pc,laptop,printer

2) For each group of laptops with the identical model number, add following record

into PC table: code: minimal code among laptops in the group +20;model:

laptop's max model number +1000;speed: maximal speed among laptops in the

group;ram: maximal ram size among laptops in the group *2 ,hd: maximal hd

capacity among laptops in the group *2;cd: default value;price: maximal price

among laptops in the group divided by 1.5;

insert pc(code,model,speed,ram,hd,price) select min(code)+20,model+1000,max(speed),max(ram)*2,max(hd)*2,max(price)/1.5 from laptop group by model