Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

fastest way to do large amounts of updates

I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:

1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5

Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?

Would parameterized updates be faster?
Any help will be appreciated.
To answer some of my own questions, for an SqlCeResultSet object, you must call the Update function once per row. Also you can wrap it in a transaction, but that will probably slow the process down, although this may still be a good idea.

My main question still remains unanswerd: What is the fastest way to do large amounts of updates? I will be running some tests soon and will post my results here.
|||

Some things you can do to improve update performance:

1. make your update statement a parameterized query, prepare it, and reuse it for each update, changing only the parameter values

2. keep indexes on the table to a minimum (or even remove them in extreme cases - then readd them after the updates complete)

3. SqlCeResultSet is the fastest mechanism if ou are using CF2 and SQL Mobile - yes, you call update on each row

-Darren

Fastest Update/Insert Method

I have a large database of several hundred gig and I need to perform monthly
updates of about 4 gigs of that data. However I have to test to check if the
update records exists in the current database so that I may either perform
an update or an insert. I've tried all kind of different ways for the
update, but the fastest appears to be to delete all records in the current
database that exist in the update and then do everything as an insert. This
still takes 30 hours to complete. I need to know if there are any tricks or
tips for updating large databases more quickly.
TIAThere is probably 1001 tips, but here are the two I know.
I Remove all foreign keys before performing the insert /
update, if you need to know why then replay, nb I have not
included Primary key as you will need it to check if
record exists ;)
The second thing is to perform the update on the same
server, taking out the network. What I mean is this if 4gb
on server B is to copied onto 5gb on Server A, then it
will take less time if you write it to a file on server B,
compress it, send it Server A, uncompress it, load it into
a temporary table then perform the SQL.
Ok I now have a stupid question, did you try the
INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?
Hope this helps
Peter
>--Original Message--
>I have a large database of several hundred gig and I need
to perform monthly
>updates of about 4 gigs of that data. However I have to
test to check if the
>update records exists in the current database so that I
may either perform
>an update or an insert. I've tried all kind of different
ways for the
>update, but the fastest appears to be to delete all
records in the current
>database that exist in the update and then do everything
as an insert. This
>still takes 30 hours to complete. I need to know if there
are any tricks or
>tips for updating large databases more quickly.
>TIA
>
>.
>|||The Update resides in the same database as a separate Update table, I did
not remove the indexes from the Primary table before attempting the update,
but will try that, leaving the PK as the ony key on the table. I've tried
the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
(SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting all
the records that existing in the Primary table that don't existing in the
TempTable, then doing just an insert of all data from the TEMPTABLE. So
basically:
DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
INSERT INTO PrimaryTable SELECT * FROM TempTable
has been the fastest.
I've tried
DELETE PrimaryTable FROM TempTable WHERE
TempTable.PrimaryKey=PrimaryTable.PrimaryKey
but this is really slow. Also NOT EXISTS, etc.
I think the issue is that it is recomputing the indexes as the query runs.
It will probably be worth dropping the indexes and reapplying them after the
update. I do this on BULK INSERT routines.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> There is probably 1001 tips, but here are the two I know.
> I Remove all foreign keys before performing the insert /
> update, if you need to know why then replay, nb I have not
> included Primary key as you will need it to check if
> record exists ;)
> The second thing is to perform the update on the same
> server, taking out the network. What I mean is this if 4gb
> on server B is to copied onto 5gb on Server A, then it
> will take less time if you write it to a file on server B,
> compress it, send it Server A, uncompress it, load it into
> a temporary table then perform the SQL.
> Ok I now have a stupid question, did you try the
> INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?
> Hope this helps
> Peter
>
> >--Original Message--
> >I have a large database of several hundred gig and I need
> to perform monthly
> >updates of about 4 gigs of that data. However I have to
> test to check if the
> >update records exists in the current database so that I
> may either perform
> >an update or an insert. I've tried all kind of different
> ways for the
> >update, but the fastest appears to be to delete all
> records in the current
> >database that exist in the update and then do everything
> as an insert. This
> >still takes 30 hours to complete. I need to know if there
> are any tricks or
> >tips for updating large databases more quickly.
> >
> >TIA
> >
> >
> >.
> >|||In cases like this I usually separate out the Inserts from the Updates up
front by either placing them in separate staging tables or by a flag in the
existing single staging table. I usually determine this with an EXISTS type
statement. But when it comes down to any updating or Inserting you need to
do them in smaller batches. Trying to update 4GB at a time will take
forever as you are painfully aware. If you do them in smaller batches of
say 10 or 20K at a time you will usually find a much faster overall time.
Doing the updates in order of the clustered indexes usually helps. By that I
mean if you are updating a lot of rows and they are lumped together by the
CI expression the database can do partial scans instead of millions of
seeks.
--
Andrew J. Kelly SQL MVP
"DWinter" <dwinter@.attbi.com> wrote in message
news:eT2ytjnbEHA.404@.TK2MSFTNGP10.phx.gbl...
> The Update resides in the same database as a separate Update table, I did
> not remove the indexes from the Primary table before attempting the
update,
> but will try that, leaving the PK as the ony key on the table. I've tried
> the INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> (SELECT PrimaryKey from TEMPTABLE) ?, but it was slower than deleting
all
> the records that existing in the Primary table that don't existing in the
> TempTable, then doing just an insert of all data from the TEMPTABLE. So
> basically:
> DELETE PrimaryTable WHERE PrimaryKey IN(SELECT PrimaryKey FROM TempTable)
> INSERT INTO PrimaryTable SELECT * FROM TempTable
> has been the fastest.
> I've tried
> DELETE PrimaryTable FROM TempTable WHERE
> TempTable.PrimaryKey=PrimaryTable.PrimaryKey
> but this is really slow. Also NOT EXISTS, etc.
> I think the issue is that it is recomputing the indexes as the query runs.
> It will probably be worth dropping the indexes and reapplying them after
the
> update. I do this on BULK INSERT routines.
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ac001c46e75$c58c1ee0$a301280a@.phx.gbl...
> > There is probably 1001 tips, but here are the two I know.
> >
> > I Remove all foreign keys before performing the insert /
> > update, if you need to know why then replay, nb I have not
> > included Primary key as you will need it to check if
> > record exists ;)
> >
> > The second thing is to perform the update on the same
> > server, taking out the network. What I mean is this if 4gb
> > on server B is to copied onto 5gb on Server A, then it
> > will take less time if you write it to a file on server B,
> > compress it, send it Server A, uncompress it, load it into
> > a temporary table then perform the SQL.
> >
> > Ok I now have a stupid question, did you try the
> >
> > INSERT into TABLE Values (A B C) WHERE PrimaryKey not in
> > (SELECT PrimaryKey from TEMPTABLE) ?
> >
> > Hope this helps
> > Peter
> >
> >
> > >--Original Message--
> > >I have a large database of several hundred gig and I need
> > to perform monthly
> > >updates of about 4 gigs of that data. However I have to
> > test to check if the
> > >update records exists in the current database so that I
> > may either perform
> > >an update or an insert. I've tried all kind of different
> > ways for the
> > >update, but the fastest appears to be to delete all
> > records in the current
> > >database that exist in the update and then do everything
> > as an insert. This
> > >still takes 30 hours to complete. I need to know if there
> > are any tricks or
> > >tips for updating large databases more quickly.
> > >
> > >TIA
> > >
> > >
> > >.
> > >
>

Faster JOINs?

I've licensed a read-only database (no insertions, updates, or data
integrity issues) that comes in an Access MDB and so when I move the many
tables and data over to SQL Server, it's basically a flat file database
without indexes, relationships, constraints, etc. although the table rows
are in order. There are many relationships between the tables and the
queries I will be using of course use JOINs.
Although I don't know for sure, I intend to create indexes with SQL Server
on the tables because I would think this would speed up the queries
considerably (although the tables are in order). However, my question is,
does it make any sense to specify and create hundreds of relationships
between about a hundred tables as I'll be using JOINs quite a bit? Is it
faster to have these relationships already specified in the database, rather
than when the queries (soon to be SPs) execute?
Thanks for any discussion."Don Miller" <nospam@.nospam.com> wrote in message
news:%23bbZ7m2pFHA.3516@.TK2MSFTNGP15.phx.gbl...
> I've licensed a read-only database (no insertions, updates, or data
> integrity issues) that comes in an Access MDB and so when I move the many
> tables and data over to SQL Server, it's basically a flat file database
> without indexes, relationships, constraints, etc. although the table rows
> are in order. There are many relationships between the tables and the
> queries I will be using of course use JOINs.
> Although I don't know for sure, I intend to create indexes with SQL Server
> on the tables because I would think this would speed up the queries
> considerably (although the tables are in order). However, my question is,
> does it make any sense to specify and create hundreds of relationships
> between about a hundred tables as I'll be using JOINs quite a bit? Is it
> faster to have these relationships already specified in the database,
> rather
> than when the queries (soon to be SPs) execute?
>
The relationships will guide the index creation. An index is required on
the primary key side of the relationship, and suggested on the foreign key
side. If you create the relationships and supporting indexes, it may speed
up joins quite a bit.
David|||Ok, first:
The tables are only physically in order. This means naught to the
optimizer. You must add indexes to get performance out of any "ordering"
If you aren't going to update the database the go wild with indexes. as much
as you need.
Only if you can do it relatively painlessly. The optimizer won't use the
foreign keys for much if anything, but it will make it easier to build a
diagram of your tables and is great documentation. If FK's are named the
same as PK's then you could build a script for doing this pretty quickly
using the information schema.
Consider contacting the liscenser (sp?) and see if they want this. Maybe
you might make a buck for your troubles.
> The relationships will guide the index creation. An index is required on
> the primary key side of the relationship, and suggested on the foreign key
> side. If you create the relationships and supporting indexes, it may
> speed up joins quite a bit.
Definitely the primary keys, and most assuredly on the foreign key columns.
They may or may not be useful, depending on how the data is used, but for a
read only database, who cares. Indexes only hurt modification performance.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OIgQc%232pFHA.208@.TK2MSFTNGP10.phx.gbl...
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:%23bbZ7m2pFHA.3516@.TK2MSFTNGP15.phx.gbl...
> The relationships will guide the index creation. An index is required on
> the primary key side of the relationship, and suggested on the foreign key
> side. If you create the relationships and supporting indexes, it may
> speed up joins quite a bit.
> David
>|||First of all, thanks for taking the time to answer my questions. I'm
intrigued (and would like to save myself a lot of time) by your statement:

> If FK's are named the
> same as PK's then you could build a script for doing this pretty quickly
> using the information schema.
They are. Of course there are many FKs in many tables for one PK, I'm not
sure I know how to use the "information schema" or sysobjects? to keep them
straight. Also, is it possible to use a script to do the indexing as well
(e.g. every column with an "*ID" in the column name should be indexed)?
It sounds like one script could do all of this quickly. Could you point me
to examples of how this could be done? Thanks again.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uGK72f6pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> Ok, first:
> The tables are only physically in order. This means naught to the
> optimizer. You must add indexes to get performance out of any "ordering"
>
is,
> If you aren't going to update the database the go wild with indexes. as
much
> as you need.
>
it
> Only if you can do it relatively painlessly. The optimizer won't use the
> foreign keys for much if anything, but it will make it easier to build a
> diagram of your tables and is great documentation. If FK's are named the
> same as PK's then you could build a script for doing this pretty quickly
> using the information schema.
>
many
rows
> Consider contacting the liscenser (sp?) and see if they want this. Maybe
> you might make a buck for your troubles.
>
on
key
> Definitely the primary keys, and most assuredly on the foreign key
columns.
> They may or may not be useful, depending on how the data is used, but for
a
> read only database, who cares. Indexes only hurt modification
performance.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:OIgQc%232pFHA.208@.TK2MSFTNGP10.phx.gbl...
many
rows
is,
it
on
key
>|||There are information_schema views for tables, columns etc:
select * from information_schema.tables or .columns
From there you just make queries like:
select 'create index ' + table_name + '_' + column_name + ' on ' +
table_name + '(' + column_name + ')'
from information_schema.columns
where column_name like '%id'
The more complex you want them, the harder the script is to write, but they
payoff is pretty good if the output is complex.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Don Miller" <nospam@.nospam.com> wrote in message
news:uWOb7e%23pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> First of all, thanks for taking the time to answer my questions. I'm
> intrigued (and would like to save myself a lot of time) by your statement:
>
> They are. Of course there are many FKs in many tables for one PK, I'm not
> sure I know how to use the "information schema" or sysobjects? to keep
> them
> straight. Also, is it possible to use a script to do the indexing as well
> (e.g. every column with an "*ID" in the column name should be indexed)?
> It sounds like one script could do all of this quickly. Could you point me
> to examples of how this could be done? Thanks again.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uGK72f6pFHA.1028@.TK2MSFTNGP09.phx.gbl...
> is,
> much
> it
> many
> rows
> on
> key
> columns.
> a
> performance.
> --
> convincing."
> many
> rows
> is,
> it
> on
> key
>

Fast updates in SQL Server

Hi
In Oracle there is a concept that allows one to perform an update without
using the rollback logs so you can try to improve the performance of an
update. Does such functionality exist in SQL Server 2000? I cannot seem to
find anything on it in BOL
Thanks
NHi
In SQL Server every DML operations are logged. If you explain us a little
bit more about your requiremnts we will be able to help you .
How much data are you going to update?
Do you have any indexes defined on the table?
"Nesaar" <nesaarATprescientdotcodotza> wrote in message
news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi
> In Oracle there is a concept that allows one to perform an update without
> using the rollback logs so you can try to improve the performance of an
> update. Does such functionality exist in SQL Server 2000? I cannot seem to
> find anything on it in BOL
> Thanks
> N
>
>|||Look at this thread there, for some operations there is a minimized
transaction protocol, like TRUNCATE. Other details were discussed in
here:
http://www.mcse.ms/archive89-2005-2-1441624.html
HTH, jens Suessmeyer.|||He has a temp table that has about 25 million rows in it. This is joined to
a transaction table with about 110 million rows on it. The transaction table
has a clustered index and a few other indexes as well.
I have just told the developer he should create an index on his temporary
table for the columns he is joining on as a first step to try and increase
performance.
Thanks
N
I've created a temp table to store transactions (+-25mill rows)
then it joins from there onto our transaction table (110 mill rows; lots of
indexes) to update.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uhRnAM$HGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi
> In SQL Server every DML operations are logged. If you explain us a little
> bit more about your requiremnts we will be able to help you .
> How much data are you going to update?
> Do you have any indexes defined on the table?
>
>
> "Nesaar" <nesaarATprescientdotcodotza> wrote in message
> news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
without
to
>