Updating A Large Table: Set @@rowcount Versus Explicit Transactions
Jul 23, 2005
When I need to perform an update against multi-million row table I
typically specify @@rowcount, to reduce locks.
e.g.
set @@rowcount 1000
while exists (select * from myTable where col2 is null)
update myTable
set col2 = col1 + 'blahblah'
where col2 is null
However, my boss' script does something like this. I think it works OK
but it seems overly complicated to me. Any thoughts?
while exists (....)
begin tran
insert into #table
select ...
update myTable
set ...
from myTable join #table ...
(@numberOfRows is a counter variable, tracking #rows that have been
updated since last batch)
if @numberOfRows > 1000
begin
commit
begin tran
end
end
View 2 Replies
ADVERTISEMENT
Feb 27, 2006
I have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @@TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @@TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @@TRANCOUNT to commit at in this case?
Thanks crew,
Carl
View 4 Replies
View Related
Jun 21, 2006
I€™m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements).
Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback.
Is there any way to distinguish if transaction in trigger is explicit or autocommited, because for autocommited transaction I also need use ROLLBACK TRANSACTION?
I€™m using SQL 2005!
Best regards
edvin
View 6 Replies
View Related
Feb 4, 2006
I'm trying to move my current use of an sql 2000 db to sql 2005.
I need to update a table definition (to change a field to an Identity)
I'm getting a dialog box (in SQL server management studio) on save saying :
'xxxx' table
- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.
I press 'Yes' to the dialog box.
After 35 seconds, I get another dialog box saying:
'xxxx' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.
Any ideas where I can change this timeout?
Daniel
View 10 Replies
View Related
Jan 25, 2008
I am trying to run a query that deletes duplicates records on a table with 24m records. The problem is each time I run it the log file fills up and I get an error saying the log file is full. For this reason the query never ends.
Is there anyway to turn of logging when running a query?
I think it also has to do with disk drive runng out of space as the log file is growing to over 12gb.
It is running in simple mode already.
View 11 Replies
View Related
Mar 14, 2008
Why is it important to use Transactions when updating or inserting multiple records ?
View 1 Replies
View Related
Feb 8, 2004
I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?
View 2 Replies
View Related
Mar 2, 2014
A server I'm working on has a very unique situation, where user tables and production tables reside on the same database. Users update / create tables or populates these tables, so it can't be a table-specific trigger. However, they give a new meaning to "kamikaze pilots" as it's not uncommon for them to "accidentally" update / insert / delete 500,000,000 + records in a single statement. I've tried educating them to use batching, but to no avail, so now I'm forced to stop these statements BEFORE they execute, based on rowcount, as they fill up the database log so quickly that it goes into recovery mode (It has a 200GB log file - insane, I know).
I recon the mosts transactions allowed should be 1,000,000 records in a single statement. Looking for database trigger to stop them from executing statements with large records?
View 6 Replies
View Related
Oct 4, 2006
hello guyshere is my problem:i am developing a asp.net web app in .net 2.0. i have some sensitive data in my database. which is encrypted using DES ( with some key which is only known by the top level authorities ). now there is an option of changing the secret key. on changing the key the sensitive data has to decrypted using the old key and then again encrypted using the new key. Now if the no of records increases i am afraid that it might take a longer time and the application might look as it got hanged. guys i have no clue on how to do this. if you guys have any idea on how to implement this please let me know. any help would be appreciatedVignesh
View 7 Replies
View Related
Jul 7, 2006
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
Thanx,
Mike
View 11 Replies
View Related
Sep 15, 2005
for some unknow reasons.. my store proc stop working.. and i got an error.. i have installaed the latest SP4 for SQL server 2000 and still have the problem !any ideas why ?? Message "An explicit value for the identity column in table 'LCMS_Modules' can only be specified when a column list is used and IDENTITY_INSERT is ON."CREATE procedure LCMS_Modules_Add
@PageID int, @ModuleDefID int, @Panename nvarchar(32), @Title nvarchar(128), @Admins nvarchar(256)
as
insert into LCMS_Modulesvalues(@PageID, @ModuleDefID, 99, @Panename, @Title, '0;', @Admins, 0, '', 'Center', '', '', '', 1, 0)GO
View 1 Replies
View Related
Aug 16, 2007
Hi guys, can anybody help to solve this problem.
set @count=0Insert into User_t (userid, counter) select userid, count+1 from resultset is not working
0/p: bhasker 1 bhanu 1 kishore 1
but o/p must be bhasker 1 bhanu 2 kishore 3
View 5 Replies
View Related
Dec 21, 2007
I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.although in my table I set IDENTITY_INSERT on (master Key)Please help me
View 5 Replies
View Related
Feb 5, 2006
Hello!
Do anybody know how to fix this error?
Cannot insert explicit value for identity column in table 'Gallery' when IDENTITY_INSERT is set to OFF.?
Thanks!
Varcar!
View 1 Replies
View Related
Apr 10, 2008
Code Snippet
Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click
Dim addclient As New newclient()
newclient.ClientID = 0
Dim result As DialogResult
result = addclient.ShowDialog
If result = DialogResult.OK Then
Dim Client As clients_and_sitesDataSet1.ClientsRow
Client = Clients_and_sitesDataSet1.Clients.NewClientsRow
Client.ClientID = addclient.ClientID
Client.Clientname = addclient.Clientname
Client.Street_and_number = addclient.Street_and_number
Client.Zipcode = addclient.Zipcode
Client.Place = addclient.Place
Client.Phone = addclient.Phone
Client.Email = addclient.Email
Client.Contact_person = addclient.Contact_person
Clients_and_sitesDataSet1.Clients.AddClientsRow(Client)
ClientsTableAdapter.Update(Client)------------------------------problem
MessageBox.Show("New Client is saved")
Else
MessageBox.Show("User cancelled operation")
End If
addclient = Nothing
End Sub
Code in VB (2005 express edition)
Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line.
when i try to run the program, i get an exeption
Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF
how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out.
And please give me specific guide lines on how to put in the code and code lines. I tried using
SET IDENTITY_INSERT Clients ON
But than I get all errors bout things not being declared, set not being supported anymore, things like that.
Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
View 10 Replies
View Related
Jul 25, 2007
In sql I perform the following
SELECT * FROM
xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer)
left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component)
right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802.
Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci
WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
What is going on here?
View 5 Replies
View Related
May 31, 2006
Does @@ROWCOUNT always return an accurate acount of rows affected by last query OR can it be equal to zero when some rows have been affected?
View 2 Replies
View Related
Jul 20, 2005
What's the difference between the rowcount column returned by:DBCC SHOW_STATISTICS('table_name','primarykey')andSELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2Is the second query always accurate, whereas the 1st may not be if the tablestats are bad?
View 1 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Dec 5, 2006
I have a series of tasks in a Sequence Container. One of them is a Data Flow task, and inside that task is a Row Count transformation that counts the number of rows I add to a table. The Row Count transformation was added to record the number of rows written to a table in a log table.
When I try to retrieve the resulting variable (RowCount) in the Data Flow task, I get the default value (0). When I try to retrieve it in a subsequent task, I get the value 1. When I try to consume it in the OnPostExecute Event Handler of the original Data Flow task, I get a value of NULL.
1) When is the appropriate time to call the variable assigned to a Row Count transformation so it can be written to a log table?
2) Is there a way during debugging to see the set value of the RowCount variable?
I am using Microsoft SQL Server Integration Services Designer Version 9.00.2047.00 in Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200)
Thank you in advance for your posts.
View 5 Replies
View Related
Jan 17, 2008
In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1
It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.
Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?
Thanks in advance for any help.
View 4 Replies
View Related
Jun 10, 2007
I am designing a database schema where several tables have one-to-many relationships to records in other tables. One way to implement it is to create a link table for each pair of tables that have a relationship:
identity
Table1_ID
Table2_ID
1
12
9
2
12
15
3
18
42With the SQL 2005 support for the XML data type, there is the possibility of storing the IDs in an XML column. The XML stored with a record (equivalent to record 12 from the above example) might look like this:
<Links>
<Table2_LinkType>
<ID>9</ID>
<ID>15</ID>
</Table2_LinkType>
<Table8_LinkType>
. . .
</Table8_LinkType>
</Links>The XML column method has the advantage of not requiring that a separate table be created but does not enforce referential integrity. The link table method has the advantage of allowing constraints to enforce referential integrity but has the disadvantage of requiring the creation of a separate table for each pair of tables having a relationship and joining to an additional table has performance implications. Implementing standard Add, List and Delete operations for the link table method is straightforward. As a test and to familiarize myself with the new XML features, I created Set, List and Delete stored procedures for the XML method. Both methods will work.
In deciding which method to go with are there any other issues I should be considering besides database integrity, complexity and possible performance issues?
From the standpoint of best practices and coding standards is one method preferred over the other?
Here is some additional information. The data set I will be working with has table sizes numbering in the hundreds of thousands. Any given record will only be linked to at most a half-dozen records in any other table.
I searched on-line for information and I was able to find plenty of good articles discussing how to use the new XML data type in SQL. However, I was not able to find any information on when not to use the XML data type over equivalent joined-table methods.
View 1 Replies
View Related
Nov 6, 2014
I want to be able to return the rows from a table that have been updated since a specific time. My query returns results in less than 1 minute if I hard code the reference timestamp, but it keeps spinning if I load the reference timestamp in a table. See examples below (the "Reference" table has only one row with a value 2014-09-30 00:00:00.000)
select * from A where ReceiptTS > '2014-09-30 00:00:00.000'
select * from A where ReceiptTS > (select ReferenceTS from Reference)
View 5 Replies
View Related
Nov 30, 2005
Hi,I am expanding our data warehouse solution with new filegroups onseveral subsystems.I want to know which idea is better!- create clustered indexes on tables to 'move' them to new filegroups- create these tables on the new filegroups.The background of this question is as follows:- we want the whole data on the new filegroups- we want to know if there is any difference in performance between the2 solutionsThanks in advance,Danny
View 4 Replies
View Related
Jun 25, 2007
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
View 11 Replies
View Related
Aug 27, 2007
Hi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
View 12 Replies
View Related
Jan 27, 2007
I have some problems getting my SSIS package to run in a transaction, I wonder if anyone can assist.
What I want to do is run one package, which consists of a
- 1) create staging table SQL statement
- 2) read (all) from Access MDB to staging
- 3) copy (only new) from staging table to real table
- 4) drop the staging table
I changed the package to "require" a transactions, and left all containers in it to "supported" (default, I think). I left all the transaction type to default "serializeable".
The package does not work, since the table I created on step one is not "seen" when step 2 wants to insert into it. If I set the package transaction back to "supported" (default) the package works, but an error on step 2 or 3 will not rollback changes and not remove the staging tables...
So, my question really is: How do I make step 1 results visible to step 2 in the same transaction? Or do I need to take a completly different approach for this?
Stupid question I think, but I seem to not be able to find the right way to handle this situation...
Thanks for reading!
Ralf
View 4 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Nov 7, 2007
Just a quick easy question. If I alter a table (add a column to the table), will it take the table offline during the ALTER process? I am adding the column to the end of the table not in the middle. I know if I add it in the middle it will offline the table.
View 4 Replies
View Related
Sep 3, 2015
Multi user ASP.Net website, SQL Server backend.When using transactions to insert multiple rows into a table and then commiting them once you're happy that everything looks good, the table(s) gets locked before commiting.Basically, I want to perform updates and inserts under a transaction but still allow other users to carry on using the site and potentially the tables that are being used with the transaction.
Question:Â Is there a way to stop the table lock occurring and for SQL to just do a record lock?, thus avoiding blocking other users?I know that a possibility is to use Snapshot replication, but am a little worried about turning it on due to the small team we have for testing.
View 10 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Jun 4, 2007
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance!!
View 5 Replies
View Related
Jun 4, 2008
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
View 2 Replies
View Related