Do While in SQL: amount

Showing posts with label amount. Show all posts

Sunday, March 25, 2012

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>|||Could you plese explain how I would use timestamping? I've looked it
up, but I'm not quite sure how to go about it.
Quote:
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets
updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
So if I just use the update statement then if multiple users are
updating 300000 records at a time, I may run into the situation where
they are trying to update the same pages right?
On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) Can't you combine your sproc into a single statement instead of using a
> table variable?
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> 3) Timestamping is another mechanism used to ensure rows are not changed
> underneath you between your initial grab and the actual update.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jason Lepack" <jlep...@.gmail.com> wrote in message
> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>
> > I'm using SQL Server 2000.
> > I have a situation where I need to select a variable amount of records
> > for a specific type_id and status_id and update some fields. Now
> > multiple users are going to be using this at the same time, and I want
> > to prevent multiple users from updating the same records.
> > Here's what I have so far:
> > CREATE TABLE [dbo].[label] (
> > [label_id] [int] NOT NULL ,
> > [label_status_id] [smallint] NOT NULL ,
> > [label_type_id] [smallint] NOT NULL ,
> > [master_job_id] [int] NULL,
> > [label_reprint_index] [int] NULL
> > ) ON [PRIMARY]
> > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> > (
> > @.lt_id bigint,
> > @.mj_id bigint,
> > @.num_labels bigint
> > )
> > AS
> > declare @.t table
> > (
> > label_id int Primary Key
> > )
> > set rowcount @.numlabels
> > insert into @.t
> > select
> > label_id
> > from
> > label
> > where
> > label_type_id = @.lt_id
> > and label_status_id = 1
> > set rowcount 0
> > declare @.count int
> > set @.count = 0
> > update
> > label
> > set
> > master_job_id = @.mj_id,
> > label_status_id = 2,
> > @.count = label_reprint_index = @.count + 1
> > where
> > label_id in (
> > select label_id
> > from @.t
> > )
> > I have to use a FIFO update for the labels, so do I need to build in
> > protection to keep multiple users from updating the same records... Hide quoted text -
> - Show quoted text -|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > 1) Can't you combine your sproc into a single statement instead of using a
> > table variable?
> > 2) If you do the above, a simple begin tran/update/error
> > check/rollback-commit sequence will ensure each record only gets updated by
> > a single process (which ever fires off first). This could lead to
> > contention if you are doing large ranges of rows.
> > 3) Timestamping is another mechanism used to ensure rows are not changed
> > underneath you between your initial grab and the actual update.
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > "Jason Lepack" <jlep...@.gmail.com> wrote in message
> >news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> > > I'm using SQL Server 2000.
> > > I have a situation where I need to select a variable amount of records
> > > for a specific type_id and status_id and update some fields. Now
> > > multiple users are going to be using this at the same time, and I want
> > > to prevent multiple users from updating the same records.
> > > Here's what I have so far:
> > > CREATE TABLE [dbo].[label] (
> > > [label_id] [int] NOT NULL ,
> > > [label_status_id] [smallint] NOT NULL ,
> > > [label_type_id] [smallint] NOT NULL ,
> > > [master_job_id] [int] NULL,
> > > [label_reprint_index] [int] NULL
> > > ) ON [PRIMARY]
> > > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> > > (
> > > @.lt_id bigint,
> > > @.mj_id bigint,
> > > @.num_labels bigint
> > > )
> > > AS
> > > declare @.t table
> > > (
> > > label_id int Primary Key
> > > )
> > > set rowcount @.numlabels
> > > insert into @.t
> > > select
> > > label_id
> > > from
> > > label
> > > where
> > > label_type_id = @.lt_id
> > > and label_status_id = 1
> > > set rowcount 0
> > > declare @.count int
> > > set @.count = 0
> > > update
> > > label
> > > set
> > > master_job_id = @.mj_id,
> > > label_status_id = 2,
> > > @.count = label_reprint_index = @.count + 1
> > > where
> > > label_id in (
> > > select label_id
> > > from @.t
> > > )
> > > I have to use a FIFO update for the labels, so do I need to build in
> > > protection to keep multiple users from updating the same records... Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegroups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>> Could you plese explain how I would use timestamping? I've looked it
>> up, but I'm not quite sure how to go about it.
>> Quote:
>> 2) If you do the above, a simple begin tran/update/error
>> check/rollback-commit sequence will ensure each record only gets
>> updated by
>> a single process (which ever fires off first). This could lead to
>> contention if you are doing large ranges of rows.
>> So if I just use the update statement then if multiple users are
>> updating 300000 records at a time, I may run into the situation where
>> they are trying to update the same pages right?
>> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>>
>> > 1) Can't you combine your sproc into a single statement instead of
>> > using a
>> > table variable?
>> > 2) If you do the above, a simple begin tran/update/error
>> > check/rollback-commit sequence will ensure each record only gets
>> > updated by
>> > a single process (which ever fires off first). This could lead to
>> > contention if you are doing large ranges of rows.
>> > 3) Timestamping is another mechanism used to ensure rows are not
>> > changed
>> > underneath you between your initial grab and the actual update.
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> > "Jason Lepack" <jlep...@.gmail.com> wrote in message
>> >news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>> > > I'm using SQL Server 2000.
>> > > I have a situation where I need to select a variable amount of
>> > > records
>> > > for a specific type_id and status_id and update some fields. Now
>> > > multiple users are going to be using this at the same time, and I
>> > > want
>> > > to prevent multiple users from updating the same records.
>> > > Here's what I have so far:
>> > > CREATE TABLE [dbo].[label] (
>> > > [label_id] [int] NOT NULL ,
>> > > [label_status_id] [smallint] NOT NULL ,
>> > > [label_type_id] [smallint] NOT NULL ,
>> > > [master_job_id] [int] NULL,
>> > > [label_reprint_index] [int] NULL
>> > > ) ON [PRIMARY]
>> > > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
>> > > (
>> > > @.lt_id bigint,
>> > > @.mj_id bigint,
>> > > @.num_labels bigint
>> > > )
>> > > AS
>> > > declare @.t table
>> > > (
>> > > label_id int Primary Key
>> > > )
>> > > set rowcount @.numlabels
>> > > insert into @.t
>> > > select
>> > > label_id
>> > > from
>> > > label
>> > > where
>> > > label_type_id = @.lt_id
>> > > and label_status_id = 1
>> > > set rowcount 0
>> > > declare @.count int
>> > > set @.count = 0
>> > > update
>> > > label
>> > > set
>> > > master_job_id = @.mj_id,
>> > > label_status_id = 2,
>> > > @.count = label_reprint_index = @.count + 1
>> > > where
>> > > label_id in (
>> > > select label_id
>> > > from @.t
>> > > )
>> > > I have to use a FIFO update for the labels, so do I need to build in
>> > > protection to keep multiple users from updating the same records...
>> > > Hide quoted text -
>> > - Show quoted text -- Hide quoted text -
>> - Show quoted text -
>|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegroups.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> 1) Can't you combine your sproc into a single statement instead of using
>> a
>> table variable?
>> 2) If you do the above, a simple begin tran/update/error
>> check/rollback-commit sequence will ensure each record only gets updated
>> by
>> a single process (which ever fires off first). This could lead to
>> contention if you are doing large ranges of rows.
>> 3) Timestamping is another mechanism used to ensure rows are not changed
>> underneath you between your initial grab and the actual update.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Jason Lepack" <jlep...@.gmail.com> wrote in message
>> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>>
>> > I'm using SQL Server 2000.
>> > I have a situation where I need to select a variable amount of records
>> > for a specific type_id and status_id and update some fields. Now
>> > multiple users are going to be using this at the same time, and I want
>> > to prevent multiple users from updating the same records.
>> > Here's what I have so far:
>> > CREATE TABLE [dbo].[label] (
>> > [label_id] [int] NOT NULL ,
>> > [label_status_id] [smallint] NOT NULL ,
>> > [label_type_id] [smallint] NOT NULL ,
>> > [master_job_id] [int] NULL,
>> > [label_reprint_index] [int] NULL
>> > ) ON [PRIMARY]
>> > CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
>> > (
>> > @.lt_id bigint,
>> > @.mj_id bigint,
>> > @.num_labels bigint
>> > )
>> > AS
>> > declare @.t table
>> > (
>> > label_id int Primary Key
>> > )
>> > set rowcount @.numlabels
>> > insert into @.t
>> > select
>> > label_id
>> > from
>> > label
>> > where
>> > label_type_id = @.lt_id
>> > and label_status_id = 1
>> > set rowcount 0
>> > declare @.count int
>> > set @.count = 0
>> > update
>> > label
>> > set
>> > master_job_id = @.mj_id,
>> > label_status_id = 2,
>> > @.count = label_reprint_index = @.count + 1
>> > where
>> > label_id in (
>> > select label_id
>> > from @.t
>> > )
>> > I have to use a FIFO update for the labels, so do I need to build in
>> > protection to keep multiple users from updating the same records... Hide
>> > quoted text -
>> - Show quoted text -
>

Do I need to examine locking for this?

Friday, March 9, 2012

Dividing the sale amount by month

Hello,

I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.

For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:

Month 1: 50$

Month 2: 100$

Month 3: 100$

Month 4: 100$

Month 5: 100$

Month 6: 50$

I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.

Thanks,

Grace

Its definately possible but I would say if you've already figured out how to do it in script component why bother trying to do it elsewhere? I doubt you'd be able to do it more efficiently either!

-Jamie

|||

Thanks Jamie,

I just wanted to confirm my method. I always tend to think that script component is not much efficient especially when i'm using it to open connection to the database and insert multiple rows. It is faster when SQL Destination is used. But in my case i can't use the destination component.

Grace

|||

There's no problem with perf of the script component. It is compiled code (different from DTS) so it is very quick!

-Jamie

Wednesday, March 7, 2012

Distribution Server and snapshot

I have a 120GB database I would like to replicate. I would be doing
transactional replication. I would like to know what amount of disk space I
will need for the snapshot and the distribution database.
What effect on performance does having the distribution database on a
production server? Can distribution database be on the other side of a WAN
from the publication database?
What is performance hit difference between syncronous mirroring verus
transactional replication on the primary database server. I have SE 2005
x64 SQL server so I can only do syncronous mirroring.
Thanks,
You will probably need about 120 Gigs for the snapshot. The size of the
distribution database is a function of the amount of data you push through
it on a daily basis, if your subscribers are named or anonymous (anonymous
means much greater storage), and what your latency is.
Mirroring in general consumes less resources than replication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:FCFE4B7B-69DC-4CAC-A5CD-7E1360374782@.microsoft.com...
>I have a 120GB database I would like to replicate. I would be doing
> transactional replication. I would like to know what amount of disk
> space I
> will need for the snapshot and the distribution database.
> What effect on performance does having the distribution database on a
> production server? Can distribution database be on the other side of a WAN
> from the publication database?
> What is performance hit difference between syncronous mirroring verus
> transactional replication on the primary database server. I have SE 2005
> x64 SQL server so I can only do syncronous mirroring.
> Thanks,

Sunday, March 25, 2012

Do I need to examine locking for this?

Do I need to examine locking for this?

Do I need to examine locking for this?

Friday, March 9, 2012

Dividing the sale amount by month

Wednesday, March 7, 2012

Distribution Server and snapshot

Do While in SQL

Blog Archive

About Me