Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Advice for array oflarge arrays Expand / Collapse
Author
Message
Posted Sunday, November 23, 2008 11:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
I have an old data structure, currently held in files,

In C its something like this
struct block {
int blockNumber;
double blockData[1020]; // C double will be SQL float
};

each file has 256 blocks of data and there are around 50 files.
The filename also forms part of the indexing system, and are of the form FILE

To use the data the existing application passes fileNumber and the blockNumber, and gets a block of 1020 values returned.

The program that reads the data is in Fortran (gulp! ) - and we want to replace this somehow - I'd like to put the data into a Database. My goal would be something like a stored procedure recieving the fileNumber and blockNumber - and passing back the array of 1020 float values

My question is how to represent the the array of float values in an SQL record structure?




Post #607216
Posted Sunday, November 23, 2008 6:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
I'm pretty sure that there is... attach one of the files and a record layout and let's see what we can do...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607257
Posted Monday, November 24, 2008 2:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
Thanks Jeff,

the file is ascii, record structure is
line 1 [Block Number] [No Values in Block]
lines 2...341 [value] [value] [value]

e.g. the first 4 lines are
1 1018
0.251726450000000000E+07 0.251729650000000000E+07 0.149568940363419000E+08
0.129347882411886640E+08 -0.194328028922087426E+06 -0.294282022326385668E+05
-0.383682118344389664E+03 -0.145789684500091479E+02 0.583642027748376771E-01

There follows a further 337 lines containing 3 float values each, giving a total of 1020 float values per block, however the initial line of the block gives 1018 as the No Values in Block - the last two values in the block are zero and would be ignored.

A sample is attached. I have cut down this file so there are only 10 blocks of data, the original file has around 250 blocks and is over 6Mb

My first idea has been rejected - it was simply to store each block of data in a TEXT field. - The user rejected this because they might want to query individual values or search for value ranges at some point in the future.

Then I mused on the idea of Pivoting the data so each row represents a position in the block and each column represents an entire block - I assume SQL 2005 no longer has the 8060 record size limit - so I can have 256(blocks) times 50(files) worth of float values in a row. It would still be awkward to reference the data as column names would be File1Block1 File1Block2 ... FileNBlockN.

I Look forward to anyone elses suggestions.


  Post Attachments 
ascm2180_cutdown.txt (4 views, 273.07 KB)
Post #607371
Posted Monday, November 24, 2008 6:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
I'm getting ready to go to work, so I can't solve it all right this instant.

I do have a question, though... are all of these values for a single column even though the data brings them in 3 at a time? In other words, would it be convenient to bring them into a table that looks like this?

BlockNum Data

And, if so, what should the column names actually be?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607469
Posted Monday, November 24, 2008 6:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Heh... never mind... not enough coffee... this is a matrix they want and they want to be able to address the data by the individual elements using j,k coorinates. Can do...

... can you wait until tonight for me to do this?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607484
Posted Monday, November 24, 2008 8:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
I think the only significance of 3 values per line is so it fits on an old 80-character green screen.

I have been investigating the code further and it seems the 1018 values can be broken up into 13 unequal groups, each group can be named GROUP0 ... GROUP12, as GROUP is another input value, however it was hidden from my view of the application, as the data retrieval part just gets the whole block, and leaves it up to the application to drill down into the group.

The following explanation of the structure is for information only. I only need to return an array of 1018 float values to the user application - but if it helps devise a better SQL storage method, then here goes: -

Of the 13 unequal groups, each group contains between 1 and 14 sub-groups of values and a sub-group can be between 2 and or 24 values

The first data line of each block (2nd actual line) contains some checking data, so actual data starts on data line 2 (actual line 3)
Example Group0 contains 14 sets of 12 values = 168 numbers
Data starts at position 4, and ends at position 172,
Then Group1 contains 10 sets of 6 values = 60 values
Data starts at position 173 and ends at position 232

I have worked out the start and end positions of each data group.

The number of values in a sub-group are pre-solved values of some mathematical transform formula on empiracle data (as far as I can tell this whole data table is like old-fashioned log tables -you just look up the values you want) the user calls it pcd or pcv ('positional calibration data/vector') I think I would need a degree in higher mathematics to fully understand what the data actually means.

So the full query is: query the positional calibration co-ordinates/vectors (x,y,z, vx,vy,vz) or (x,y, vx,vy) for the Nth transform of Group A, Block B, File C

However as I said earlier, I the application itself takes care of the co-ordiantes and the transform and the Group - I can get at the required Group if I want, but I still have to return an array of 1018 float values - all the non-required group values could be set to zero, only populating the required group.

Post #607558
Posted Monday, November 24, 2008 8:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
Hi Jeff

I really appreciate you looking at this. Posting this makes me think more about what I'm doing, and your replies meant I had to ask the user some more meaninful questions. I've had the problem for about 3 weeks - the user is in no rush as the old file system works - but they have no Fortran support, so they want it done eventually.

Now that I've got the base level of data out of the user, I can start devising a structure

something like

CREATE TABLE Transform(
pKey int identity,
FK_SubGroupID int
FK_GroupID int,
FK_BlockID int
FK_FileID int
x float,
y float,
z float,
vx float,
vy float
vz float )

Then Tables to link to the SubGroups, Groups, Blocks and Files

... it probably needs a bit more work - and some indexes.

Post #607583
Posted Monday, November 24, 2008 12:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
I've gone for simplicity, and abandoned any attempt at making sense of the data's internal structure. Its just a Block of float values this way
CREATE TABLE BlockRef (
pKey int identity(1,1) PRIMARY KEY,
FileNo int not null,
BlockNo int not null,
itemCount int not null )

CREATE TABLE Block (
pKey int identity(1,1),
fk_BlockRef int not null FOREIGN KEY REFERENCES BlockRef(pKey),
sequence int not null -- sequence of the float value in the original file block,
fValue float )

-- Then to select an array
CREATE PROC GetDataBlock
@FileNo int,
@BlockNo int
AS
SELECT fValue
FROM Block
WHERE fk_BlockRef = (
SELECT pKey FROM BlockRef
WHERE FileNo = @FileNo
AND BlockNo = @BlockNo )
ORDER BY sequence

I think this will work and I've started on the C program to populate the data structure
I'll put some indexes on it too.
Post #607804
Posted Monday, November 24, 2008 10:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
Ok... I've got the split done. I just need to "smear" the Group number, SubGroup number, and a couple of other things throughout the "matrix". Since this isn't a rush and I actually have to get up in 5 hours, I'll continue on this bad boy tomorrow.

Write the C code if you want... I'm still gonna do it in SQL because it's all just data and it's fun, besides. ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #608054
Posted Tuesday, November 25, 2008 8:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
This sounds like an opportunity for CLR to me.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #608403
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse