Use RAM ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use RAM ?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-08-02 : 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

38200 Posts

Posted - 2006-08-02 : 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

260 Posts

Posted - 2006-08-02 : 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

38200 Posts

Posted - 2006-08-02 : 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

260 Posts

Posted - 2006-08-02 : 16:15:21

hi,

How do i get the service pack level?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-02 : 16:19:54

SELECT @@VERSION

Tara Kizer

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-08-02 : 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

260 Posts

Posted - 2006-08-02 : 16:39:10

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-03 : 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

3271 Posts

Posted - 2006-08-03 : 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
  END

These 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"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-03 : 06:18:09

Hm, is there anything wrong with the answers you got here? ->
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69558

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-08-03 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources