Viewing 14 posts - 1 through 14 (of 14 total)
The issue that has caught me off guard about the "noSQL revolution" is its associated religious fervor. Throw in the O/R Mapping trends and you practically have a holy war
There...
June 24, 2010 at 3:07 pm
I would like to confirm that I got my problem resolved. Copying data from the master database took around 2 hours. Another 8 hours - shrinking the database, and then...
May 9, 2010 at 7:16 pm
Jeff Moden (5/6/2010)
How many indexes do you have on this bad boy? Also, do you have any Foreign Keys or triggers associated with this table?
Out of 180GB occupied by...
May 6, 2010 at 8:01 pm
Ok I think I solved my problem as it happes frequently in a way that could not have been suggested on a forum because it's not possible to present complete...
May 6, 2010 at 7:53 pm
And here the first million with the new script:
13122010-05-07 12:32:35.697
13012010-05-07 12:01:39.113
If I have 1 mil deleted every half an hour I'll need >70 more hours to finish the delete process...
May 6, 2010 at 6:36 pm
These are the stats from the first run of the script (yesterday):
24242010-05-07 09:50:04.993
23232010-05-07 07:59:53.770
22222010-05-07 06:51:33.177
21212010-05-07 06:01:14.253
20202010-05-07 05:15:18.837
19192010-05-07 04:32:22.620
18182010-05-07 03:54:23.123
17172010-05-07 03:11:45.967
16162010-05-07 02:25:01.717
15152010-05-07 01:47:50.197
14142010-05-07 01:10:34.580
13132010-05-07 00:31:06.697
12122010-05-06 23:51:33.583
11112010-05-06 23:15:31.950
10102010-05-06 22:40:30.957
992010-05-06 22:05:49.300
882010-05-06 21:30:16.277
772010-05-06 20:57:32.897
662010-05-06...
May 6, 2010 at 6:23 pm
Leo.Miller (5/6/2010)
How big is the table you are testing against?Also are the table layouts the same?
I'm testing against the real table.
May 6, 2010 at 6:12 pm
Sql Server Books online http://msdn.microsoft.com/en-us/library/ms188774.aspx says:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use...
May 6, 2010 at 6:11 pm
Shrink for some reason didn't work. Althpugh I can see that BigTable has 9GB of unused space, shrink didn't shrink the database file at all....
So I have rewritten my query...
May 6, 2010 at 6:07 pm
Leo.Miller (5/6/2010)[/bTry running the queries below with the SHOW QUERY PLAN ON and after setting
SET STATISTICS IO ON
SELECT TOP 1 (*) FROM Big_Table where [DATE] < '1 Jan 2010'...
May 6, 2010 at 5:57 pm
Leo.Miller (5/6/2010)The only way to find the TOP record is to look at them all, then sort them in the required order, then take the top one off the top....
May 6, 2010 at 5:22 pm
Leo.Miller (5/6/2010)
A further comment is to try use a clustered index/primary key in the WHERE clause. You don't say if the table is clustered on [DATE].
It is not. It is...
May 6, 2010 at 5:08 pm
David Webb-200187 (5/6/2010)
create another table to hold the 30 million rows you want to keep
copy the 30 million rows to the new table in batches...
May 6, 2010 at 5:05 pm
Leo.Miller (5/6/2010)
2nd Problem:
DELETE TOP (1000000)..
To find the TOP (1000000) sql must check all the records.
Thank you for your feedback. Could you please expand on the above? Why are you saying...
May 6, 2010 at 4:55 pm
Viewing 14 posts - 1 through 14 (of 14 total)