Running updates - the quickest approach - please help.

Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.
A primary key doesn't necessarily have a clustered index. It can be
nonclustered. As for the update at hand, it depends. The index could help
you locate the specific rows you are updating, thus making the query faster.
However, you'll also be updating the index, which will decrease performance.
The only way to tell is to take a copy of the table and run it both ways,
comparing the results.
Can you post your complete DDL + query?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegrou ps.com...
Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.
|||How about
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here is your update statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegrou ps.com...
> Hi everyone,
> I have to update a column with about 1.34M rows. The column is a a
> primary key and hence has a clustered index associated with it. I have
> to run updates o nthis column.
> Would it be quicker to do the following:
> 1. Delete the index
> 2. Run the updates
> 3. Recreate the index
> OR
> 1. Just run the updates and never mind the index?
>
> Which approach would be faster? Anyone with any
> views/comments/suggestions/experience thay would like to share I would
> be very much appreciative.
> Al.
>

Running updates - the quickest approach - please help.

Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.A primary key doesn't necessarily have a clustered index. It can be
nonclustered. As for the update at hand, it depends. The index could help
you locate the specific rows you are updating, thus making the query faster.
However, you'll also be updating the index, which will decrease performance.
The only way to tell is to take a copy of the table and run it both ways,
comparing the results.
Can you post your complete DDL + query?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegroups.com...
Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.|||How about
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here is your update statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegroups.com...
> Hi everyone,
> I have to update a column with about 1.34M rows. The column is a a
> primary key and hence has a clustered index associated with it. I have
> to run updates o nthis column.
> Would it be quicker to do the following:
> 1. Delete the index
> 2. Run the updates
> 3. Recreate the index
> OR
> 1. Just run the updates and never mind the index?
>
> Which approach would be faster? Anyone with any
> views/comments/suggestions/experience thay would like to share I would
> be very much appreciative.
> Al.
>sql

Running updates - the quickest approach - please help.

Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.A primary key doesn't necessarily have a clustered index. It can be
nonclustered. As for the update at hand, it depends. The index could help
you locate the specific rows you are updating, thus making the query faster.
However, you'll also be updating the index, which will decrease performance.
The only way to tell is to take a copy of the table and run it both ways,
comparing the results.
Can you post your complete DDL + query?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegroups.com...
Hi everyone,
I have to update a column with about 1.34M rows. The column is a a
primary key and hence has a clustered index associated with it. I have
to run updates o nthis column.
Would it be quicker to do the following:
1. Delete the index
2. Run the updates
3. Recreate the index
OR
1. Just run the updates and never mind the index?
Which approach would be faster? Anyone with any
views/comments/suggestions/experience thay would like to share I would
be very much appreciative.
Al.|||How about
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here is your update statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
<almurph@.altavista.com> wrote in message
news:1141040040.741042.9260@.i39g2000cwa.googlegroups.com...
> Hi everyone,
> I have to update a column with about 1.34M rows. The column is a a
> primary key and hence has a clustered index associated with it. I have
> to run updates o nthis column.
> Would it be quicker to do the following:
> 1. Delete the index
> 2. Run the updates
> 3. Recreate the index
> OR
> 1. Just run the updates and never mind the index?
>
> Which approach would be faster? Anyone with any
> views/comments/suggestions/experience thay would like to share I would
> be very much appreciative.
> Al.
>

Running update syntax at set time

Hi

Is it possible for me to run an update syntax at a certain time say midnight for example?

I'm trying to update a bit field in my table (which acts as a checkbox in my Access front end), but only if three date fields are before todays date. The dates in question are in two other tables.

If so how would I go about doing this?

ThanksHave you looked at Books OnLine and read about the SQL Server Agent and scheduled jobs?|||What's Books OnLine, and where can I find it?|||What's Books OnLine, and where can I find it?Books Online = BOL = SQL Server Help - easily accessed via Query Analyser and Enterprise Manager.

BTW - are you sure you want to derive this overnight rather than at runtime or (possibly) at point of data capture?

Running UPDATE statements in parallel on the same table

Hi all,

Does an UPDATE statment lock the entire table or just the rows that will be affected by the UPDATE?

I ask because -

Can I run UPDATE statements in parallel on the same table on the same column. The need for doing this is because the table is a large fact table. I plan to execute the same UPDATE statements on different time sections of the data to expedite the processing.

If the UPDATE statment lock the entire table then I cannot run an UPDATE in parallel. If the UPDATE statement just locks the rows that will be affected then maybe I can because rows affected will be different for each UPDATE.

Let me know.

Thanks,

VivekIt all depends on the lock escalation

Imaging if you ad hundereds of current connections and the lock always ascended to table locks..that wouldn't be a very good RDBMS, now would it.

BOL has a good description of this if you want to read up on it.

Running SQL Scripts against SQL 2005

Our developers generate a large number of SQL scripts that get applied with each application update. Is there an easy way to select one script after the other and run them against a particular database?

Right now it appears I have to open them one by one (since the window selector is useless for actually picking a script) by scanning through the directory for the next script, confirming the connection (thankfully I'm using Integrated Security), Changing the current database (since it always defaults to master) and finally run the script. And I'm ending up doing this for every script. Hoping there's a better way to do this.

Thanks,

Larry

Management studio accepts a subset of sqlcmd statements. The one you want to use is :r. This reads the contents of the file and puts it in line with the query you have.

i.e.

:r c:\sql\myfirstscript.sql

:r c:\sql\mysecondscript.sql

go

:r c:\sql\mythirdscript.sql

in this example the first 2 scripts are combined and run. If they contain GOs in then each batch will be executed as normal.

The point to note is that the :r does execute the file but reads the file into the query. So make sure each file ends in GO or you seperate your :r statements by go.

TO use sqlcmd mode click on the icon on the toolbar with a window AND a red exclamation mark. Or go to Query and select SQLCMD mode

Running snapshot after schema update

Hi,
I want to know, if there is a schema change at the
publisher's side (done thru sp_repladdcolumn and/or
sp_addarticle etc), then the current snapshot becomes
invalid.
What's the best way to ensure that a fresh snapshot is
generated immediately?
I do not want to run the snapshot agent manually (or
schedule to run at a specific time). The requirement is
it should automatically as soon as there is a schema
change at Publisher. Is there any SP which I can call to
get this done?
regards
KP
Krishnaprasad,
you could create a wrapper stored procedure that calls both sp_repladdcolumn
and sp_start_job. When you need to add a column you call your wrapper rather
than the stored procedure directly.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

running slow

hi

below is my query ..it is running very slow ..can we optimize it.

thaxn

Update pruamc.Tbl_CAMS_UploadDetails
set Compare_Status='Success',additionalfield100=
(select file_name from Tbl_CMS_UploadMaster A,Tbl_CMS_UploadDetails B where A.cms_upload_master_id =B.cms_upload_master_id and B.CMS_Upload_Details_ID=@.CMS_Upload_Details_ID),
ManualMatchingStatus='1',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)

where CAMS_Upload_Details_ID=@.CAMS_Upload_Details_ID

The following query may increase the performance,

Code Snippet

Declare @.FileName as varchar(100);

Select @.FileName = file_name from Tbl_CMS_UploadMaster A

Join Tbl_CMS_UploadDetails B On A.cms_upload_master_id =B.cms_upload_master_id

and B.CMS_Upload_Details_ID=@.CMS_Upload_Details_ID;

Update

pruamc.Tbl_CAMS_UploadDetails

Set

Compare_Status='Success'

,additionalfield100=@.FileName

,ManualMatchingStatus='1',ReconciliationDate=convert(datetime,cast(getdate()as varchar(11)),103)

Where

CAMS_Upload_Details_ID=@.CAMS_Upload_Details_ID

|||

thanx mani

let me check..i will back.