| Author |
 Topic  |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
 Posted - 08/02/2006 : 15:35:05
|
Hi,
I wanna know if there is an option (I m using MS SQL Server 2000) to tell the server, run the transaction using RAM?
thanks |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/02/2006 : 15:43:38
|
SQL Server will handle this for you. What is the query that you have in mind?
Tara Kizer |
 |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 08/02/2006 : 15:54:07
|
Hi,
I have a several tables, each have approx. 40000000 row of datas. When i do some select, insert and update (in a single SP) it takes too much time, 3-4 hours. and i think its using hard disk instead of RAM, thats why it takes too long. Sometimes, it gives error, there is no disk space. Is there a way to make it much more faster? Any suggestions...
thanks |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/02/2006 : 15:56:12
|
You'd have to post your queries, SQL Server 2000 service pack level, and your hardware config in order for us to help you make it faster.
Tara Kizer |
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 08/02/2006 : 16:15:21
|
hi,
How do i get the service pack level? |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/02/2006 : 16:19:54
|
SELECT @@VERSION
Tara Kizer |
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 08/02/2006 : 16:38:29
|
Hi,
Here is my SP. What i wanna do here is, i have a huge table as i mentioned before. There is a date time field fTime, but seconds are missing, thats why there are duplicate datas. I wanna add seconds to fTime column. heres my code below;
ALTER Procedure Addseconds
AS
declare @time datetime
declare @SQL_INS varchar(1000)
SELECT @SQL_INS = 'CREATE TABLE [TEMP1] ('
SELECT @SQL_INS = @SQL_INS + '[xID] [int] NULL ,
[fTime] [datetime] NULL
) ON [PRIMARY]'
Exec (@SQL_INS)
DECLARE table_create CURSOR FOR
select DISTINCT(fTime) from [USD/BEF1] order by fTime ASC
DECLARE @ftime datetime
DECLARE @xid int
DECLARE @diff int
DECLARE @interval int
DECLARE @intervalms int
DECLARE @count int
DECLARE @temp int
DECLARE @x int
SET @count = 1
OPEN table_create
FETCH NEXT FROM table_create into @time
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE time_count CURSOR FOR
select fTime,xID from [USD/BEF1] where fTime =@time group by fTime,xID
OPEN time_count
FETCH NEXT FROM time_count
INTO @ftime,@xid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [TEMP1]
VALUES (@xid,@ftime)
FETCH NEXT FROM time_count
INTO @ftime,@xid
END
CLOSE time_count
DEALLOCATE time_count
SET @temp = (SELECT count(xID) FROM [TEMP1])
DECLARE rep_count CURSOR FOR
select fTime,xID from [USD/BEF1] where fTime =@time group by fTime,xID
OPEN rep_count
FETCH NEXT FROM rep_count
INTO @ftime,@xid
SET @diff = @temp - 2
IF (@diff > 0 )
BEGIN
SET @intervalms = 60000/(@temp)
SET @interval = 1
SET @x = @intervalms
END
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@temp=2)
BEGIN
IF(@count = 1)
BEGIN
UPDATE [USD/BEF1]
SET fTime = @ftime
WHERE xID = @xid
END
ELSE IF(@count > 1)
BEGIN
UPDATE [USD/BEF1]
SET fTime = (SELECT DATEADD(ss,59,@ftime))
WHERE xID = @xid
END
SET @count = @count +1
END
IF(@temp>2)
BEGIN
IF(@count = 1)
BEGIN
UPDATE [USD/BEF1]
SET fTime = @ftime
WHERE xID = @xid
END
ELSE IF(@count > 1 AND @count < @temp)
BEGIN
UPDATE [USD/BEF1]
SET fTime = (SELECT DATEADD(ss,@interval,@ftime))
WHERE xID = @xid
UPDATE [USD/BEF1]
SET fTime = (SELECT DATEADD(ms,@intervalms,@ftime))
WHERE xID = @xid
SET @intervalms = @intervalms + @x
END
ELSE IF(@count = @temp)
BEGIN
UPDATE [USD/BEF1]
SET fTime = (SELECT DATEADD(ss,59,@ftime))
WHERE xID = @xid
END
SET @count = @count +1
IF (@intervalms >= 1000)
SET @interval = @interval + 1
END
FETCH NEXT FROM rep_count
INTO @ftime,@xid
END
CLOSE rep_count
DEALLOCATE rep_count
SET @count = 1
DELETE [TEMP1]
FETCH NEXT FROM table_create INTO @time
END
DROP TABLE [TEMP1]
CLOSE table_create
DEALLOCATE table_create |
|
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 08/02/2006 : 16:39:10
|
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
|
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 08/03/2006 : 04:26:44
|
Would you mind wrapping your procedure with [ code ] [ /code ] tags plz...
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 08/03/2006 : 05:01:59
|
Never mind...I'm quite bored at work today so I did it myself. And I must say that I'm not the least bit surprised that this takes 3-4 hours. Cursors by themselves are considered horrible for performance and having several nested cursors will just kill you server running on a table with 40 mill records. You should really read up on set based programming...consider these two pieces of code:UPDATE mytable SET mydatefield = DATEADD(ss, 3, mydatefield)
DECLARE mycursor CURSOR FOR SELECT ID FROM mytable
OPEN mycursor
FETCH NEXT FROM mycursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE mytable SET mydatefield = DATEADD(ss, 3, mydatefield) WHERE ID = @ID
FETCH NEXT FROM mycursor INTO @ID
ENDThese two statements do *exactly* the same thing however the first one performs so much better you wouldn't belive it. The main difference is that the first one does everything in one go at the same time, while the other one updates the table one row at a time. This is a very important consept to grasp and it will take you some time to master it but by spending a few active months here on sqlteam you'll get quite far! We have all been there...some of the pro's here just don't want to admit it.
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand" |
Edited by - Lumbago on 08/03/2006 06:14:23 |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
|
|
raysefo
Constraint Violating Yak Guru
256 Posts |
Posted - 08/03/2006 : 08:25:04
|
Well,
thanks for replies. But what i m trying to do is, distribute the seconds and miliseconds evenly into those duplicate datetime fields based on 1 minute. Shortly, the first datetime value should get 00:000 and the last duplicate datetime should get 59:000 value. Between those 2 values, there gonna be evenly distributed seconds and mili seconds.
sample which was done with the code i sent earlier above.
2005-09-22 07:38:00.000 33.2200 1
2005-09-22 07:38:00.847 33.2000 2
2005-09-22 07:38:01.690 33.2000 3
2005-09-22 07:38:02.537 33.2000 4
2005-09-22 07:38:03.380 33.2100 5
2005-09-22 07:38:04.227 33.2000 6
2005-09-22 07:38:05.070 33.2200 7
2005-09-22 07:38:05.917 33.2100 8
2005-09-22 07:38:06.760 33.2200 9
2005-09-22 07:38:07.607 33.2100 10
2005-09-22 07:38:08.450 33.2100 11
2005-09-22 07:38:09.297 33.2000 12
2005-09-22 07:38:10.140 33.2200 13
2005-09-22 07:38:10.987 33.2100 14
2005-09-22 07:38:11.830 33.1900 15
2005-09-22 07:38:12.677 33.2100 16
2005-09-22 07:38:13.520 33.1900 17
2005-09-22 07:38:14.367 33.2200 18
2005-09-22 07:38:15.210 33.2100 19
2005-09-22 07:38:16.057 33.2200 20
2005-09-22 07:38:16.900 33.2100 21
2005-09-22 07:38:17.747 33.1900 22
2005-09-22 07:38:18.590 33.2100 23
2005-09-22 07:38:19.437 33.2200 24
2005-09-22 07:38:20.280 33.2200 25
2005-09-22 07:38:21.127 33.2100 26
2005-09-22 07:38:21.970 33.2300 27
2005-09-22 07:38:22.817 33.2100 28
2005-09-22 07:38:23.660 33.2200 29
2005-09-22 07:38:24.507 33.2300 30
2005-09-22 07:38:25.350 33.2100 31
2005-09-22 07:38:26.197 33.2000 32
2005-09-22 07:38:27.040 33.2100 33
2005-09-22 07:38:27.887 33.2100 34
2005-09-22 07:38:28.730 33.2200 35
2005-09-22 07:38:29.577 33.2100 36
2005-09-22 07:38:30.420 33.2000 37
2005-09-22 07:38:31.267 33.2200 38
2005-09-22 07:38:32.110 33.2200 39
2005-09-22 07:38:32.957 33.2200 40
2005-09-22 07:38:33.800 33.2100 41
2005-09-22 07:38:34.647 33.2100 42
2005-09-22 07:38:35.490 33.2100 43
2005-09-22 07:38:36.337 33.2000 44
2005-09-22 07:38:37.180 33.1900 45
2005-09-22 07:38:38.027 33.2200 46
2005-09-22 07:38:38.870 33.2000 47
2005-09-22 07:38:39.717 33.2100 48
2005-09-22 07:38:40.560 33.2200 49
2005-09-22 07:38:41.407 33.2100 50
2005-09-22 07:38:42.250 33.2000 51
2005-09-22 07:38:43.097 33.2100 52
2005-09-22 07:38:43.940 33.2000 53
2005-09-22 07:38:44.787 33.2100 54
2005-09-22 07:38:45.630 33.2000 55
2005-09-22 07:38:46.477 33.2100 56
2005-09-22 07:38:47.320 33.2100 57
2005-09-22 07:38:48.167 33.2100 58
2005-09-22 07:38:49.010 33.2300 59
2005-09-22 07:38:49.857 33.2100 60
2005-09-22 07:38:50.700 33.2100 61
2005-09-22 07:38:51.547 33.2100 62
2005-09-22 07:38:52.390 33.2200 63
2005-09-22 07:38:53.237 33.2100 64
2005-09-22 07:38:54.080 33.2100 65
2005-09-22 07:38:54.927 33.2200 66
2005-09-22 07:38:55.770 33.2000 67
2005-09-22 07:38:56.617 33.2000 68
2005-09-22 07:38:57.460 33.2200 69
2005-09-22 07:38:58.307 33.2000 70
2005-09-22 07:38:59.000 33.2000 71
As you see, there were 71 duplicate datetime datas. I exactly wanna do this algorithm much more faster.
|
|
|
| |
Topic |
|
Use RAM ?
