Fastest Way To Remove Large Table?
Nov 22, 1999
We have a table that we BCP into, the data is then processed and inserted into its appropriate table.
Then the table or its data needs to be removed. This seems to be a very slow operation to remove
the table or table data. I have tried drop table, and truncate table and it takes nearly as long as
the bcp operation. The table has 12 million rows. I didn't think either operation wrote to the
transaction log except for page extent management. Why is the drop and truncate so slow. Suggestions?
View 1 Replies
ADVERTISEMENT
Mar 3, 2006
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.
View 3 Replies
View Related
Mar 23, 2008
Hi Guys,
What is the fast way to move huge table (77 million) records with 25 columns across servers? The servers are not linked though.
Thanks for the help.
View 3 Replies
View Related
Jul 23, 2005
I've probably not given the best title to this topic, but thatreflects my relative "newbie" status.I have a table that goes essentiallyTSDATETIMEjobnumberVARCHARjobentryVARCHAR...the TS is a time stamp, and the other two fields are job numberand entries. There are (many) more fields, but this is the core ofit. The relationship is there will be several entries per job, withone row in the table per entry (i.e several rows per job).In constructing a web interface I want to create a list of recentjob numbers, and summarize it broadly as followsmax(ts)jobnumbercount(jobentry)...I can do this by a select command as followsselect top 30 max(ts) as time, jobnumber, count(jobentry)from Jobsgroup by jobnumberorder by time descHowever I'm now finding that this is quite slow now that my teat tablehas around 400,000 entries (Even though I've added indexes to mostrelevant fields). In particular it's much slower thanselect top 30 jobnumberfrom Jobsgroup by jobnumberorder by jobnumber descleading me to suspect that the aggregate functions are slowing thisdown. I would guesstimate the difference in speed is around a factorof 10-20.As I type this I think I've just realised it's theorder by time descthat is probably causing the problem (in that it will need tocalculate max(ts) for all jobnumber's in the table in order toexecute the "order by" clause).I think I can see the solution now (amazing what typing out a longquestion can do for you :-)My question was going to be if there was any approved method/tricksfor avoiding this sort performance hit.It seemed to me that if I could first grab a decent number of recentrecords (quickly) I could then execute the aggregate functions againstthat smaller set of records. I know in broad terms how any entriesthere can be per job, so I could select enough to make at least 30jobs, and then execute the real query against that. In my case therewill be probably less than 10 entries per job, so I could grab 300records and execute against that, instead of against the whole400,000.That being the case is this best donea) as a subquery, and if so, howb) by creating a temporary intermediate table (which isin essence a more explicit version of (a) isn't it?)Another solution that occurred wasc) to create and maintain a summary table, with justone record per jobnumber with a view to having itserve this particular (common) enquiry.For (c) to work I would probably need triggers on the Jobs table tomaintain the summary table. This would probably represent moreoverhead overall, but should certainly give a better responsewhen the query outlined above is executed on the web site.The response time to this query is becoming critical on the webinterface to avoid timeouts.Any suggestions or comments are welcome. If it's RTFM, then I'dappreciate page numbers :-)Thanks for reading this far :-)--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 4 Replies
View Related
Jan 8, 2008
Hi,
I have a number of large tables (50,000,000 + rows and 30+ columns) that are refreshed once per month with data from another system. Data is first loaded into a staging table, that has an indentical strcuture to the final table, cleansed and then copy to the final table.
The current copy process is simple but inefficient:
Truncate Table <final-table>
Insert Into <final-table>
Select * from <staging-table>
If possible I don't want to use DTS for this and I'd like to use a batch size of around 10,000 rows to help reduce the size of the log file because I have 3 of these types of Inserts running concurrently.
What is the fatest way to copy data from one table to another, within a stored procedure using SQL 2000 on a large, clustered server that uses logging?
Thanks,
Jason
View 5 Replies
View Related
Jul 20, 2005
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 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
Feb 15, 2008
I need a SSIS Package that compares the two tables and removes the rows in the first table with keys that do not exist in the second table. For example....
I have a table of returns based on returnID. In another table I have returnErrors that are based on returnID as well. I want a package that will uses my returns table as a source and compares that dataset to the dataset of the returnError and remove or spilt the data so that my remaining dataset only has returns that have returnErrors. I can do this in T-SQL, but I am looking for a SSIS solution that uses the conditional split transformation or some other transformation(s) combinations.
-- Ryan
View 7 Replies
View Related
Oct 14, 2013
I have a table like below
patientidcode
11 101.3
12 102..3
13 103.
14 104..3.
15 109.3
16 105..3
but need like below to remove . (dot)
patientidcode
11 101.3
12 102.3
13 103
14 104.3
15 109.3
16 105.3
View 3 Replies
View Related
Jan 2, 2001
I have small table that has been corrupted.
I'm getting the following error when running a simple select statement:
"could not open FCB for invalid file ID 0 in database 'data_base_name'.
Table or database may be corrupt. connection broken."
I have already created a new table, but I have been unsuccessful at removing the corrupted table. Does anyone know the steps to go about removing this table? And, if anyone has seen this type of error before, why did it occur?
Is it a bug with microsoft SQL7? Any info would be greatly appreciated.
Thanks in advance
View 3 Replies
View Related
Aug 7, 2013
I have a query like this
SELECT TOP 1 * FROM ITAM_RAMS_STAGING
not getting value...seems like lock associated with that table
how to remove the lock if exists
View 2 Replies
View Related
Jan 10, 2008
I deleted a login name from the server Security - Logins. The login name was added to the database (myDatabase) permissions before it was deleted. I meant right click at myDatabase. Select Properties, and the Permissions tab. What is the command for removing the login name from myDatabase Permissions?
I did it from the Query Command window yesterday using maybe EXEC SP_????????? but I could not find it again on the web. Can someone please help me with this?
Thanks.
DanYeung
View 2 Replies
View Related
Jan 29, 2008
Hi All
I have the dbo.OperatingHour It has many duplicates and I want to remove duplicates permanently
The statement below works but when I open the table there are no changes
Insert into OperatingHour(Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays])
(SELECT DISTINCT Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays] FROM OperatingHour)
View 2 Replies
View Related
Mar 17, 2015
Version: SQL 2008 R2
Problem: I have a user who wants to remove table, but when she tries to remove it ( drop the table), she gets an error:
===========================================================
MSG 3724, Level 16, State 2, Line 6: Cannot drop the table, 'dbo.<table name>' because it is being used for replication.
MSG 2714, Level 16, State 6, Line 2
There is already an object named '<table name>' in the database.
============================================================
To investigate, I saw the following:
In the SSMS---> Replcation---> Local Publication--> [XYZ].Pub_XYZ
[SERVER Name].[XYZ_Replication]
Local Subscriptions:
[Reference DB].[ServerInstance].[Instance Name]:ABC.ABC_TO_XYZ.Reference DB).
How do I remove that table? Do I need to edit Replication? How? Which one?
View 15 Replies
View Related
Nov 30, 2006
Hi all,
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Nov 16, 2007
I am developing an application that has a table with lots of records(network traffic) but the data is summarize every so often to create summary records (old records are deleted). The problem is that I have a PK based on an autoincrement ID (int) that will run out of numbers. However, this ID is not referenced anywhere, (not a foreign key from another table, not use for deletion and there is no update in this table whatsoever).
So my possibilites are:
1.- reseed the id when it is about to run out.
2.- make the id bigint
3.- remove the id and change the PK to 2 other fields
4.- remove the id and without PK
I am leaning toward option 4, because I do not see the need for a PK, but I understand that it is quite out of the normal.. So I would like to hear from other people ( I do not have much experience with DB).
I also like option 3. I already have a index on one of the other fields (time).
Any input will be appreciated.
Claudio Robles
View 7 Replies
View Related
Jul 23, 2005
If I use BCP to export a very large table will that table be blockedfor writes during the export process? I don't want to prevent usersfrom accessing that table during the bcp process?Thank You, TFD.
View 1 Replies
View Related
Oct 12, 2006
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
Thanks
deebeez1
View 1 Replies
View Related
May 21, 2007
Hi
I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that 'email@email.com' is replaced as email@email.com.
Thank you
zipfeli
View 2 Replies
View Related
Aug 16, 2005
I am working SQL Server 2005 and One Table Which contain only one column without primary keyNow I want to remove all duplicate value from that table with only single query
View 2 Replies
View Related
Mar 20, 2007
I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove...
my tables is like that
Names
------------
Rakesh
Rakesh
Rakesh Kumar Sharma
Rakesh Kumar Sharma
Baburaj
Raghu
Raghu
and Output of query should be like that
Names
-----------
Rakesh
Rakesh Kumar Sharma
Baburaj
Raghu
Thanks in advance
View 3 Replies
View Related
Dec 3, 2006
If we want to remove the duplicate row and leave only one row instead of 2 or 3 rows for example with the same column values.
2/ The same question but when all the columns of the row are duplicate except the id field.
Thanks a lot.
View 3 Replies
View Related
Jan 10, 2007
Hi guys
I have been using SQL server 2005. I have got a huge table with about 1 million rows.
Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that??
Is there a query in SQL to remove duplicate rows???
thanks
Mita
View 4 Replies
View Related
Aug 1, 2007
Is there any way to remove IDENTITY property on particular table? I tried removing IDENTITY property using Manangement studio, but this operation behind the scene use migration concept that is by creating tmp table and then populating with data; droping the orginal and renaming the tmp back to original.
Second, i want some kind of generic solution using certain system table like aya.sysobjects, sys.identitycolumn etc such a way that i should be able to remove the idenity property from all of the table accross a database.
Mandip
View 5 Replies
View Related
Mar 5, 2008
Hi,
I currently have one table that lists all projects and tasks within the organisation. One of the table fields is the task status, open or closed. I would like to be able to have a process by which the tasks that are completed are removed from the table and placed into another (archive) table. The same records then being removed from the original table. which then only contains the incomplete tasks. This process could be run at given times during the day or at the point when the status of a task is changed from open to closed, either way each time the process is run it would need to append the rows removed into the archive table. Anyone any ideas on the best way to do this?.
Thanks
View 3 Replies
View Related
May 26, 2008
Hi,
I have this page that upload's PFD's to a table. In principle this works fine.
Until I try to upload large files (3 to 4 MB)I need to even upload larger files than that. (Don't really know as of yet what users are going to come up with) I get TimeOut problems. Now some people say it is not possible to exceed a limit of about 4 MB. But that there is a workaround by changing something to the web.config file.Can somebody give me info about that, (I am quite a novice really)I tried to change it like this, but to no avail:
<system.web><httpRuntime maxRequestLength="102400"enable = "True"requestLengthDiskThreshold="102400" useFullyQualifiedRedirectUrl="True"executionTimeout="102400"/></system.web>
Thanks for any help!
View 2 Replies
View Related
Mar 3, 2004
I have a table of approx 1/2 million rows.
On a nightly basis, this table gets rebuilt in a temporary database. Once the table has been built and scrubbed, i need to move it into our webservers db.
I'd like to do this with minimal interuption to the website.
Possible techniques:
1) I could set up a DTS package to copy the table object overwriting the destination table
2) I could export to a flat file and then bulk import into the live table (after truncating it)
3) I could run a process to update smaller chunks of data at a time running delete queries and insert queries.
Anybody have a thought on the best way to do this so that the web users would be virtually unaware that anything was happening ?
View 4 Replies
View Related
Mar 1, 2002
Hi,
I am absolutely innocent as far as T-SQL is concerned. I need to detect all duplicates (key consists of 5 fields) in the table and delete the duplicates.
I tried different approaches like joins etc but nope.
Any help is appreciated
Thanks
View 2 Replies
View Related
Aug 6, 2004
OK, I imported 680 million records into an unindexed table. That went well.
Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.
It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?
SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN
Help! Thanks in advance!
View 8 Replies
View Related
Nov 14, 2007
Hi folks! I'm looking for advice on partitioning a large table. In the DDL below I've changed names to protect the guilty.
My table has this schema:
CREATE TABLE [dbo].[BigTable]
(
[TimeKey] [int] NOT NULL,
[SegmentID] [int] NOT NULL,
[MyVal] [tinyint] NOT NULL
) ON [BigTablePS1] (TimeKey) -- see below for partition scheme
alter table [dbo].[BigTable] add constraint [PK_BigTable]
primary key (timekey asc, SegmentID asc)
-- will evaluate whether this one is needed, my thinking is yes
-- based on the expected select queries.
create index NCI_SegmentID on BigTable(SegmentID asc)
The TimeKey column is sort of like a unix time. It's the number of minutes since 2001/01/01, but always floored to a 5 minute boundary. so only multiples of 5 are allowed.
Now, this table will be rather big. There are about 20k possible SegmentIDs. For every TimeKey from 2008/01/01 to 2009/01/01 (12 months), I'll have on the order of 20000 rows, one for each SegmentID.
For the 12 month period, there are 365*24*60/5=105120 possible TimeKey values. So the total rowcount is over 2 billion. (20k * 105120)
Select queries are expected to be something like this:
-- fetch just one particular row...
select MyVal from BigTable
where TimeKey=5555 and SegmentID=234234
--fetch for a certain set of SegmentID and a particular time...
select
b.SegmentID
,b.MyVal
from BigTable b
join OtherTable t on t.SegmentID=b.SegmentID
where b.TimeKey=5555
and t.SomeColumn='SomeValue'
Besides selects, also I need to be able to efficiently issue update statements against the table with new values in the MyVal column based on a range of TimeKey values (a contiguous span of a few days) and sets of about 1000 SegmentID. updates would always look like this:
update t
set t.MyVal=p.MyVal
from BigTable t
join #myTempTable p on t.TimeKey=p.TimeKey
and t.SegmentId=p.SegmentId
where #myTempTable would have order of 1000*24*60 rows in it, all with contiguous TimeKey values, and about 1000 different SegmentID values. #myTempTable also has a clustered pk on (timekey asc, SegmentId asc).
After the table is loaded, it would never get any inserts or deletes. only selects and updates.
Given the size, and the nature of the select and update queries, this table seems like a good candidate for partitioning. I'm thinking it makes sense to partition on TimeKey.
So my question is, is it stupid to create a separate partition for each day in the year long span of TimeKeys this table covers? That would mean 365 partitions in the partition function and partition scheme. Something like this:
CREATE PARTITION FUNCTION [BigTableRangePF1] (int)
AS RANGE LEFT FOR VALUES
(
3680640 + 0*1440, -- 3680640 is the number of minutes between 2001/01/01 and 2008/01/01
3680640 + 1*1440,
3680640 + 2*1440,
3680640 + 3*1440,
...snip...
3680640 + 363*1440,
3680640 + 364*1440,
3680640 + 365*1440
);
GO
CREATE PARTITION SCHEME [BigTablePS1]
AS PARTITION [BigTableRangePF1]
TO
(
[PRIMARY],[PRIMARY],[PRIMARY],
...snip...
[PRIMARY],[PRIMARY],[PRIMARY]
);
GO
does anyone have any experience with partitioned tables with so many partitions? Is a few hundred partitions too many? From my understanding of partitions, seems like having so many will be ok. Is it somehow worse than having hundreds of tables in a database?
Even with one partition for each day, I'll still have 24*60*20000/5 ~ 5m rows in each one.
5m seems like a manageable number. 2b does not.
elsasoft.org
View 2 Replies
View Related
Jul 23, 2005
Greetings All, I was wondering what would happen if I were to do a"select * from table" on a table that has about 5 million rows. Wouldmy read block other writers to the same table? Would it block otherreaders? I know SQL uses optimistic lockign by default but I am notsure what this means to other users trying to access the same table?Any advise would be greatly appreciated.TFD
View 3 Replies
View Related
Jul 20, 2005
Quick question:Does SQL do table/schema changes "in place"?I've got a large table (140+ million rows of very widedata) that we want to change the schema on -- basicallyto remove a number of the unused data elements that wedon't use.Anyway, does anyone know if SQL will do an in-placechange, or if it will copy the table to a new table, therebyincreasing my space allocation needs? I'd effectively,temporarily, need space for two tables while the changeis happening if it copies the table first. This is not good asI do not have enough available space at the moment.If you've got pointers to specific MS docs regardingthis issue, please let me have 'em.Thanks in advance.
View 2 Replies
View Related
