SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advice for array oflarge arrays


Advice for array oflarge arrays

Author
Message
Tom Brown
Tom  Brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4882 Visits: 1494
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! Unsure ) - 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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)

Group: General Forum Members
Points: 336362 Visits: 42591
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Brown
Tom  Brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4882 Visits: 1494
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.
Attachments
ascm2180_cutdown.txt (24 views, 273.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)

Group: General Forum Members
Points: 336362 Visits: 42591
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)

Group: General Forum Members
Points: 336362 Visits: 42591
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Brown
Tom  Brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4882 Visits: 1494
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.
Tom Brown
Tom  Brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4882 Visits: 1494
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.
Tom Brown
Tom  Brown
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4882 Visits: 1494
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)SSC Guru (336K reputation)

Group: General Forum Members
Points: 336362 Visits: 42591
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. Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51482 Visits: 8790
This sounds like an opportunity for CLR to me.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search