Time proccessing in SQL by best performance

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

SQL Server 2008 Forums

Transact-SQL (2008)

Time proccessing in SQL by best performance

Author

Topic

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2009-11-09 : 01:26:29

Hi all,
I have a table that insert records in it about 10 per second!

One of columns is in DateTime format and another is Value that change more and more per second.
--------------------------------------
1.
If I want to get last n records(10 or 100 or 1000) from this table,Which is better? order

DateTime column and get TOP(n); or add Id column that is Identity column and add automatically

and select TOP(n) records by order Id column?
Notice that inserting data and get them perform more and more in a second!
---------------------------------------
2.
I want to sample data from this table by proccess Time section of DateTime column. An example for

explain more:

Id DateTime Value
============================================
512 2009.1.1-05:29:52 2.24
513 2009.1.1-05:29:59 2.53
514 2009.1.1-05:30:01 2.44
515 2009.1.1-05:30:02 2.68
516 2009.1.1-05:30:05 2.86
...
522 2009.1.1-05:59:59 2.44
523 2009.1.1-06:00:00 2.53
524 2009.1.1-06:00:00 4.23
525 2009.1.1-06:00:01 2.68
526 2009.1.1-06:00:02 4.86
...
536 2009.1.1-06:00:58 4.86
537 2009.1.1-06:01:02 4.86

I want to sample first record that insert in per hour, per half an hour, per minute, and ...
In example and run in half an hour, select must be return records:514 and 523.
If run it for per hour, return only record:523.
and If run for per minute, return:514,523,537.

For more, I want to get first record that inserted in defined timeframe(1Min,30Min,60Min,...)

Hope to explain simple to understand all.
-------------------------------------------
3.
Can you help me for more performance and speed in this table? Another definition or indexing or

... that insert and select run by best performance.
-------------------------------------------
4.
Thanks more!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 10:37:51

1. i think former is better
2.something like


CREATE PROC SampleData
@Interval int
AS
SELECT columns
FROM 
(SELECT ROW_NUMBER OVER(PARTITION BY DATEADD(mi,(DATEDIFF(mi,0,DateTime)/@Interval) * @Interval,0) ORDER BY DateTime) AS Seq,*
FROM YourTable
)t
WHERE t.Seq=1
GO

then execute as
EXEC SampleData 1 for minute
EXEC SampleData 30 for halfhour
EXEC SampleData 60 for hour,...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources