No, this database is accessed by only me no one is using it.
really, this slow performance is really weared,

I have tested it and when i run the Stored Procedure the sql server
process is eating up to 98% from the processor,

and the Stored Procedure inserts something like that
INSERT INTO CardStatus
    (SrlNo, StatusID,StatusDate, PresentStatus,[TimeStamp])
Values
    (@SRL, 0,GetDate(),0,GetDate())

set @CurrstatID = (select IDENT_CURRENT('CardStatus'))

/*Card Loc*/
Insert into CardLoc
    (SrlNo, LocID, TransferDate, TransID, [TimeStamp])
Values
    (@SRL, 0, GetDate(), @TransID,GetDate())

set @CurrlocID = (select IDENT_CURRENT('CardLoc'))

/*Card Main*/
INSERT INTO dbo.CardMain
    (
        SrlNo, CTypeID, [TimeStamp], CardStatusID, CartonNo, BoxNo,
PacketNo,LocID
    )
Values
    (
        @SRL,Cast(Substring(@SRL,1,1) AS bigint), GETDATE(),@CurrstatID,
        Cast(substring(@Carton,2,3) as varchar), Cast(Substring(@Box,5,2)
        as varchar), Cast(Substring(@Packet,5,2) as varchar),@CurrlocID
    )

which in a loop of 10,000 time,
if you need more details, I can send them to you,

Thanks

the Recovery Option is set to FULL
and the Hard disk subsystem is ATA NTFS (ONE PARTITION) system and
non-RAID

and the Log files are on the same Partition.