Store procedure and Transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Store procedure and Transactions

Author

Topic

tarakshah
Starting Member

3 Posts

Posted - 2007-11-23 : 07:35:06

[/size=2]Hello All,

I had table with around 40-45 fields. Currently i am having Insert and update SP's with 40 params for storing/updating record. But i think to split the table into 2 tables improving performance.

- So first question is - its appropriate decision to split the table? and by spliting will i gain performance during selecting/Insert/Updating records?

- if I split the table, i need to implement Transactions for Data consistency. for that i had 2 options
1. To make a SP with 40 params and then in that call other 2 sps (for 2 tables)

eg. Create Procedure MainSP @Name,@ADD,.....(40 Params)

Begin Transaction

Exec(usp_InsertTable1 ....20params) -- INsert in Table1

Exec(usp_InsertTable2 ....20params) -- INsert in Table2

Commit Transaction

2. Option is to implement Transactions in .Net

eg.

SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}

Please suggest which option is correct. Pls also suggest any other options which is preferable and optimize. Pls help.

Thanks in Advance.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 08:10:51

Both solutions would work, but if you do it in Sp you have less roundtrips from your application to server.
But I doubt that you can gain anything from horizontal splitting. You'll need additional JOINS in your queries, and you will lose more on perf then you can gain.

How big is your row? Could you provide the current layout of your table with 40 columns?

tarakshah
Starting Member

3 Posts

Posted - 2007-11-23 : 09:30:40

Thanks for your reply. I forget to mention in last post, i am using SQL Server 2005 and application is Web based app. in asp.net 2.0 wiht C#. My table is structure is below mention. Table is having mostly varchar fields, decimal and datetime and one ntext field. Pls suggest how to proceed...

CREATE TABLE [dbo].[Ship](
[ShipID] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[PreparedBy] [varchar](50) NULL,
[FileNo] [varchar](50) NOT NULL,
[NwId] [int] NOT NULL,
[CId] [int] NOT NULL,
[NPId] [int] NULL,
[Vessel] [varchar](50) NULL,
[Shipper] [varchar](80) NULL,
[PortofOrigin] [varchar](80) NULL,
[LoadingPort] [varchar](80) NULL,
[DischargePort] [varchar](80) NULL,
[FinalDestination] [varchar](80) NULL,
[MBL] [varchar](50) NOT NULL,
[CNo] [varchar](50) NOT NULL,
[AMSHBLNo] [varchar](50) NOT NULL,
[SubHBLNo] [varchar](50) NULL,
[CargoLocation] [varchar](80) NULL,
[CargoPhone] [varchar](50) NULL,
[CargoFax] [varchar](50) NULL,
[ETD] [datetime] NULL,
[ETA] [datetime] NULL,
[ETADest] [datetime] NULL,
[ITNo] [varchar](50) NULL,
[ITDate] [datetime] NULL,
[ITPort] [varchar](50) NULL,
[ProductDesc] [varchar](80) NOT NULL,
[Pieces] [int] NOT NULL,
[Weight] [decimal](18, 2) NOT NULL,
[CBM] [decimal](18, 2) NOT NULL,
[PrepaidCharges] [decimal](18, 2) NOT NULL,
[BOLRecivedDate] [datetime] NULL,
[CheckReceivedDate] [datetime] NULL,
[ReleasedBy] [varchar](50) NULL,
[ReleasedDate] [datetime] NULL,
[DeliveredDate] [datetime] NULL,
[TelexRelease] [bit] NOT NULL,
[NeedReleaseOrder] [bit] NOT NULL,
[DoorDelivery] [bit] NOT NULL,
[TransferToQB] [bit] NOT NULL,
[Remarks] [ntext] NULL,
CONSTRAINT [PK_Ship] PRIMARY KEY CLUSTERED
(
[ShipID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-23 : 09:44:25

Leave it as is.
SQL server stores only real data in varchar, so 'abc' in varchar(1000) occupies 3 bytes, not 1000
So your row does not look very wide.
In any case, the price of joining is mich higher, so do not solve the problem which does not exist yet.

tarakshah
Starting Member

3 Posts

Posted - 2007-11-24 : 02:02:05

Thanks for your suggestion. One of my concern is was also when table grows large in future (expecting to grow at thousand rows in 6-8 months), will there be performance hit during Select Statement (or during Insert/Update) with this flat table structure?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-24 : 06:42:17

Thousands rows? :)
I am working right now with a table with 1'800'000'000 rows.

I dont see any potential problems with your table right now.
All data in relation 1:1 to a ship can be kept in the same table.
Only if you have multiple rows of something per ship you should put it into a separate table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources