Customizing data access

Question

Customizing data access

v_krishnan

Say Hey Kid

Points: 676
More actions
August 5, 2003 at 9:21 am

#82198

Hi,
Is there a way via which I can 'modify' how the database tables are presented to external clients? Basically, what I want to achieve is that -
I have data stored in the a table with the columns - TIME, VALUE1, VALUE2,... . I put data into this table say every minute. Now, when the user queries this table, he should be able to specify an 'interval' field using which I would like to return data back to the user with pseudo rows generated as per the interval. That is, if the user specifies the interval as say, 1 hour then I would like to discard the intermediate values in the table. If he says 0.5 seconds, then I would have to generate values for in-between the minute records.
Is there any customizing feature available in SQL server (and MSDE)
Or, can I build a middle-tier application through my ODBC/OLEDB clients can get access to the database In this case, I can then put the above logic into the app which sort of acts like a gateway to the clients.
Thanks,
Krishnan
August 8, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Not sure I fully understand, but middle tier logic is always an option, especially when you're doing something that goes beyond what you can reasonably do in TSQL.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

v_krishnan Say Hey Kid Points: 676 More actions · Answer 2

quote:
Not sure I fully understand, but middle tier logic is always an option, especially when you're doing something that goes beyond what you can reasonably do in TSQL.

When you say middle tier logic how do you suggest that to be implemented? What kind of options are available for that?

What I want is to "fit" in an application between the database and its clients (sort of a gateway). This application will look into the queries fired by the clients and modify them suitably as required and then return the result.

Thanks,

Krishnan

jpipes SSChampion Points: 12230 More actions · Answer 3

This interval field -- is it able to be of a unit smaller than the smallest time interval stored in the DB? If so, you might be heading for trouble, and should think about adjusting your smallest time interval storage. I, like Andy it seems, am a bit confused as to the overall objective of your queries, and also, the application. What are the users doing with this data? How do your queries need to return data, and what data is being returned, and how often? I don't really see the need for an "intermediate application" unless you are running some sort of queueing or polling server in between the database and the client...Please specify some more...

--

Thanks,

Jay

v_krishnan Say Hey Kid Points: 676 More actions · Answer 4

quote:
This interval field -- is it able to be of a unit smaller than the smallest time interval stored in the DB? If so, you might be heading for trouble, and should think about adjusting your smallest time interval storage. I, like Andy it seems, am a bit confused as to the overall objective of your queries, and also, the application. What are the users doing with this data? How do your queries need to return data, and what data is being returned, and how often? I don't really see the need for an "intermediate application" unless you are running some sort of queueing or polling server in between the database and the client...Please specify some more...

The interval field can be at most 1 millisecond, which SQLServer supports. I will try to explain the issue I am trying to solve a bit more -

I have values stored on the basis of time in a table. Let's say I have perfomance monitoring application, which monitors the CPU usage of an app, and logs it into this table. It logs the value and the time whenever it detects a suitable change in the CPU usage of the app. So I would be having data of this sort in the table -

TIME CPU_USAGE

--------------------------------------

11:00:01.000 10.1

11:00:01.900 15.4

11:00:02.550 0.0

11:05:04.789 2.3

Now, what I want to acheive is that when the user queries this table for data and specifies an interval value, (say 500ms) then I want the result to be like this -

TIMECPU_USAGE

-------------------------

11:00:01.00010.1

11:00:01.50010.1

11:00:02.00015.4

11:00:02.50015.4

11:00:03.0000.0

11:00:03.5000.0

11:00:04.0000.0

11:00:04.5000.0

11:00:05.0002.3

As you can see I have interpolated the data as per the interval value specified. I want to embed this logic in such a way that when the user requests data from the table like, SELECT TIME, CPU_USAGE FROM MYTABLE WHERE INTERVAL = 500, I would like that logic to get executed and return the result set as shown above.

Hope this clears the confusion!

Thanks,

Krishnan

jpipes SSChampion Points: 12230 More actions · Answer 5

How do you know how to filter the data being returned? For instance, you showed a subset of the data in your table. How does the query know the entire range of times to filter on? Also, why repeat the data like that? Why not just plot the actual data against a standard grid based on the time interval specified?

v_krishnan Say Hey Kid Points: 676 More actions · Answer 6

quote:
How do you know how to filter the data being returned? ...

The time band would specified by the user as part of the query. I have to interpolate/repeat the data within this period/band.

quote:
Also, why repeat the data like that? Why not just plot the actual data against a standard grid based on the time interval specified?

I agree that such a thing is fine when plotting it against a grid. But, let's say I want to generate reports out this data, which should indicate the value (CPU_USAGE here) at each time interval specified. I could have done so at the report application level, but what if it is third-party?

Thanks,

Krishnan

jpipes SSChampion Points: 12230 More actions · Answer 7

Krishnan,

This script I wrote demonstrates how to create a temporary table to do interval timing, match a stored value using an INNER JOIN with a BETWEEN condition, and shows a general test case for this type of scenario. I hope this meets your needs:



SET NOCOUNT ON

--

-- Create test data table

--

DECLARE @i INT

SET @i = 0

CREATE TABLE #Test (GUID uniqueidentifier, MyTime DATETIME, Value INT)

WHILE @i < 50 BEGIN

INSERT INTO #Test

VALUES (NEWID(), DATEADD(millisecond, RAND() * 2000, '2003-08-13 15:00:00.000'), RAND() * 20000)

SET @i = @i + 1

END

--

-- Create Timing Interval Table

--

DECLARE 

  @Interval INT -- Number of milliseconds in interval

, @StartTimeDATETIME-- Actual start of entire range

, @EndTimeDATETIME-- Actual end of entire range

, @LoopTimeDATETIME-- For tracking in INSERT Loop

--

SET @StartTime = '2003-08-13 15:00:00.000'

SET @EndTime = DATEADD(millisecond, 2000, @StartTime)

SET @Interval = 50

-- Initialize @LoopTime to same as @StartTime

-- Loop will increment @LoopTime by @Interval

-- until @LoopTime is greater than @EndTime

SET @LoopTime = @StartTime

--

CREATE TABLE #TimeInterval (StartTime DATETIME NOT NULL, EndTime DATETIME NOT NULL)

-- Add the timing intervals to the temp table

WHILE @LoopTime < @EndTime BEGIN

INSERT INTO #TimeInterval (StartTime, EndTime)

VALUES (@LoopTime, DATEADD(millisecond, @Interval, @LoopTime))

-- Increment LoopTime

SET @LoopTime = DATEADD(millisecond, @Interval, @LoopTime)

END

--

SET NOCOUNT OFF

--

PRINT 'ALL VALUES IN #TimeInterval...'

PRINT ''

SELECT ti.* FROM #TimeInterval ti

--

PRINT 'MATCHING VALUES FROM #Test...'

PRINT ''

SELECT 

  ti.StartTime AS "Start Interval"

, ti.EndTime AS "End Interval" 

, t.Value AS "Value"

, t.MyTIme AS "TimeCompared"

FROM #Test t

INNER JOIN #TimeInterval ti

ON t.MyTime BETWEEN ti.StartTime AND ti.EndTime

ORDER BY ti.StartTime

--

DROP TABLE #TimeInterval

DROP TABLE #Test