Create tabular data from table (long post)

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 2000 Forums

Transact-SQL (2000)

Create tabular data from table (long post)

Author

Topic

kevnewzealand
Starting Member

1 Post

Posted - 2006-03-09 : 21:03:24

Hi, I'm sure there is better way to do this but I can't see it ...

Given the following tables:

Name: PARAMETERDETAILS
Fields: parameterID (integer),parametername (varchar(25)
Example1: 1,'First Parameter'
Example2: 2,'Another Parameter'

Name: PARAMETERVALUES
Fields: parameterID (integer),dt (datetime),value (float)
Example1: 1,'2005-10-01 09:45',12.34
Example2: 2,'2005-11-03 13:43',10.45
Example3: 2,'2005-11-05 11:08',1346.1

I need to generate a result set which lists dt and then the values at that time for each of a specified set of parameterID's.

For example, I would like to generate a table of all the values for parameterID's 1,4,5,10 for June last year. But I want them in rows like this:

dt,ParameterID1,ParameterID4,ParameterUD5,ParameterID10
'2005-06-01 00:14',12.4,NULL,13.8,NULL,NULL

Not every parameter will necessarily have a value at the same time, hence there will be NULLs where a value is not available.

This is a legacy table and in a typical production system has thousands of parameterID's and many millions of values in the table.

Why do I want to get the data in this format? Because I have to for the project (customer core requirement) :-)

I can do it by building a temporary table, adding enough columns to handle the various parameters I want to display. Then I iterate the list of reaquired parameterIDs, adding or updating rows in the results table as necessary.

The problem is that it is so slow. Takes about 20 seconds to generate a table covering 1 day or so, with 12 parameters and roughly 1 value per second for each parameter.

Can I do it a more efficient way? Is this actually quite fast for what I'm trying to achieve? Any suggestions? I need to get this down to approaching 0.1 seconds per day if possible. I can split it into smaller time periods, build lots of little tables and then dump them all out sequentially, but this doesn't really gain me much. Potentially I may need to generate tables for up to 1 year of values! If I'm askinbg for the impossible then I'll just have to go back to the drawing board and find a completely different approach to the problem, but given the existing system that's not going to be easy (backward compatibility issues etc).

For the values table I have indices on dt, parameterID and dt + parameterID. Here is the SQL to generate the temp table (#results). The parameter list would be passed in as a variable in reality, but for testing here its 'hard-coded' to 1,4,5,10. Also the date range is hard-coded to the last three days for testing.

create table #results (dt datetime primary key clustered)
declare @dt as datetime
declare @parameterID as integer
declare @sql as nvarchar(200)
declare @value float

declare c_parameterIDs cursor for (select parameterID from parametervalues where parameterID in (1,4,5,10))
declare c_columns cursor for (select parametername from parameterdetails where parameterID in (1,4,5,10))

open c_columns
fetch next from c_columns into @name
while (@@fetch_status=0)
begin
set @sql = 'alter table #results add [' + @name + '] float null'
exec sp_executesql @sql
fetch next from c_columns into @name
end
close c_columns
deallocate c_columns

open c_points

fetch next from c_parameterIDs into @parameterID,@name

while (@@fetch_status = 0)
begin
declare c_dt cursor for (select distinct dt from parametervalues where parameterID = @parameterID and dt > dateadd(day,-3,getdate()))
open c_dt
fetch next from c_dt into @dt
while (@@fetch_status = 0)
begin
set @value = (select max(value) from parametervalues where parameterID=@parameterID and dt=@dt)
if not exists (select dt from #results where dt=@dt)
begin
set @sql = 'insert into #results(dt,[' + @name + ']) values(''' + cast(@dt as varchar(30)) + ''',' + cast(@value as varchar(30)) + ')'
exec sp_executesql @sql
end
else
begin
set @sql = 'update #results set [' + @name + ']=' + cast(@value as varchar(30)) + 'where dt=''' + cast(@dt as varchar(30)) + ''''
exec sp_executesql @sql
end
fetch next from c_dt into @dt
end
close c_dt
deallocate c_dt
fetch next from c_parameterIDs into @parameterID,@name
end

close c_parameterIDs

deallocate c_parameterIDs

select * from #results order by dt asc

I have tried also to generate the dt result set first and bulk insert it into the results table. This eliminates the need for the if not exists ... clause, as I can assume that the row already exists and I therefore just have to update it with each parameter value. This gains me a few percent in performance but not enough. I'm thinking (read hoping) that there is a much simpler way of doing all this!

Using SQL Server 2000. Any comments gratefully received.

Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources