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 the same SQL on several servers

I frequently do updates to Stored Procedures (and other types of SQL queries
too) on serveral server installations across our LAN/WAN.
Currently, I connect to each server in SSMS and run the code in a new query
window for each, which is a bit of a pain. Is there a way I can run the
script in one query window, but run it several times - each pointing to
different server. For example, is there an equivalent to the USE DB_NAME
statement, that reflects the server as well?"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OG0O3eRSGHA.1608@.TK2MSFTNGP09.phx.gbl...
>I frequently do updates to Stored Procedures (and other types of SQL
>queries too) on serveral server installations across our LAN/WAN.
> Currently, I connect to each server in SSMS and run the code in a new
> query window for each, which is a bit of a pain. Is there a way I can run
> the script in one query window, but run it several times - each pointing
> to different server. For example, is there an equivalent to the USE
> DB_NAME statement, that reflects the server as well?
>
Right-click on the query window and choose Connection\Change Connection, or
use SqlCmd.
David

Running SQL Server Stored Procedures through access

Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:

Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute

for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||There is definitely a reason, although I can't see what it is yet.

My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.

-PatP|||I would explicitly dim your cn and cmd objects.

Dim cn as new ADODB.connection
Dim cmd as new ADODB.command

At the bottom of your function, make sure you destroy the objects

cn.close
Set cn = Nothing
Set cmd = Nothing

Try using a OLEDB connection instead of a DSN.

Are there any parameters for this stored procedure? I do not see them here.

Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.

[databasename].[owner (usually dbo)].procedurename.|||a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.

Running SQL Server 2005 Dev Ed. and Express Ed. side by side.

I recieved the same error running both the SQL Svr Express SP1 and the SQL Svr SP1 updates. I resolved the problem with SQL Svr Express by installing the SQL Svr Express with Advanced Serives pakage. The SQL Svr SP1 still errors out with this message: Unexpected error ocurred: The following unexpected error ocurred <blank message> with an ok button. The following is the contents of the installtion log file:

7/14/2006 13:39:18.489 ================================================================================
07/14/2006 13:39:18.620 Hotfix package launched
07/14/2006 13:39:18.700 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
07/14/2006 13:39:18.800 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
07/14/2006 13:39:18.940 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
07/14/2006 13:39:19.060 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
07/14/2006 13:39:22.375 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
07/14/2006 13:39:22.535 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
07/14/2006 13:39:22.635 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
07/14/2006 13:39:22.756 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
07/14/2006 13:39:22.836 Local Computer:
07/14/2006 13:39:22.936 Target Details: SONYAS-LAP
07/14/2006 13:39:23.016 commonfilesdir = C:\Program Files\Common Files
07/14/2006 13:39:23.116 lcidsupportdir = e:\8960d915665e75cf26ff\1033
07/14/2006 13:39:23.256 programfilesdir = C:\Program Files
07/14/2006 13:39:23.356 supportdir = \\SONYAS-LAP\e$\8960d915665e75cf26ff
07/14/2006 13:39:23.477 supportdirlocal = e:\8960d915665e75cf26ff
07/14/2006 13:39:23.577 windir = C:\WINDOWS
07/14/2006 13:39:23.627 winsysdir = C:\WINDOWS\system32
07/14/2006 13:39:23.677
07/14/2006 13:39:23.707 Enumerating applicable products for this patch
07/14/2006 13:39:37.377 The patch installation could not proceed due to unexpected errors
07/14/2006 13:39:37.547
07/14/2006 13:39:37.708 Product Status Summary:
07/14/2006 13:39:38.629 Hotfix package closed

Doesn't really give much detail.

Moving to Setup so they can troubleshoot the issue w/ SQL SP1 patch.

Mike