How to store big Arrays

[jc]

Hi.
I am in a situation with an engineering application involving
monitoring of press operations. This involves storage of numbers for
both an X and Y arrays. The number of element within the arrays
varies slightly but should be identical for a singular press
operation.

One approach is to store an element in a row. This would be 6000 rows
(3000 elements/axis * 2). This seems excessive for a single
operation. Also, I have no intention of every reading rows that would
be a subset of an operation.

The other approach is to pack an array of data into a single varbinary
data type.

Questions;
1) Is my thinking here correct for DB usage?
2) Can people provide some techniques for doing the later (an array
into a single varbinary value)?

In particular for point 2 how would I cast arrays that vary from
2500 .. 3500 elements with integers to a single varbinary and of
course retrieve?

Regards JC.......

[Tom van Stiphout]

Re: How to store big Arrays

On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <jim.clifford@g m.com>
wrote:

I haven't used them yet, but SQL 2008 has support for spatial data
types.

-Tom.
Microsoft Access MVP

>Hi.
>I am in a situation with an engineering application involving
>monitoring of press operations. This involves storage of numbers for
>both an X and Y arrays. The number of element within the arrays
>varies slightly but should be identical for a singular press
>operation.
>
>One approach is to store an element in a row. This would be 6000 rows
>(3000 elements/axis * 2). This seems excessive for a single
>operation. Also, I have no intention of every reading rows that would
>be a subset of an operation.
>
>The other approach is to pack an array of data into a single varbinary
>data type.
>
>Questions;
>1) Is my thinking here correct for DB usage?
>2) Can people provide some techniques for doing the later (an array
>into a single varbinary value)?
>
>In particular for point 2 how would I cast arrays that vary from
>2500 .. 3500 elements with integers to a single varbinary and of
>course retrieve?
>
>Regards JC.......

[Roy Harvey (SQL Server MVP)]

Re: How to store big Arrays

On Thu, 23 Oct 2008 19:26:52 -0700, Tom van Stiphout
<tom7744.no.spa m@cox.netwrote:

>On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <jim.clifford@g m.com>
>wrote:
>
>I haven't used them yet, but SQL 2008 has support for spatial data
>types.

I think you meant sparse arrays. It wasn't clear to me that the
arrays in this case were going to be sparse.

Roy Harvey
Beacon Falls, CT

[Tom van Stiphout]

Re: How to store big Arrays

On Fri, 24 Oct 2008 07:29:37 -0400, "Roy Harvey (SQL Server MVP)"
<roy_harvey@sne t.netwrote:

No, I meant spatial data:

http://msdn.microsoft.com/en-us/library/bb964711.aspx

-Tom.

>On Thu, 23 Oct 2008 19:26:52 -0700, Tom van Stiphout
><tom7744.no.sp am@cox.netwrote :
>

>>On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <jim.clifford@g m.com>
>>wrote:
>>
>>I haven't used them yet, but SQL 2008 has support for spatial data
>>types.

>
>I think you meant sparse arrays. It wasn't clear to me that the
>arrays in this case were going to be sparse.
>
>Roy Harvey
>Beacon Falls, CT

[Roy Harvey (SQL Server MVP)]

Re: How to store big Arrays

On Fri, 24 Oct 2008 05:55:41 -0700, Tom van Stiphout
<tom7744.no.spa m@cox.netwrote:

>No, I meant spatial data:
>http://msdn.microsoft.com/en-us/library/bb964711.aspx

So you are thinking the X by Y array is representing spacial
coordinates. Possible, I guess, but I tend to think of spacial data
as the data itself being the location, while with an array I think of
the subscript (x,y) representing the location. But then I have not
worked with the new spatial support.

Roy Harvey
Beacon Falls, CT

[Erland Sommarskog]

Re: How to store big Arrays

jc (jim.clifford@g m.com) writes:

I am in a situation with an engineering application involving
monitoring of press operations. This involves storage of numbers for
both an X and Y arrays. The number of element within the arrays
varies slightly but should be identical for a singular press
operation.
>
One approach is to store an element in a row. This would be 6000 rows
(3000 elements/axis * 2). This seems excessive for a single
operation. Also, I have no intention of every reading rows that would
be a subset of an operation.
>
The other approach is to pack an array of data into a single varbinary
data type.

The latter sounds like a repeating group to me, and that is usually
a gross violation of the fundamentals of a relational database.

However, in the end, it boils down to what you will use this data for.
If you don't plan to use all these values in the database, but only
outside it, storing it as a blob could make sense.

But if you plan to analyse it to find max, mins, average and so on,
storing it in a normalised data model is the way to go.

2) Can people provide some techniques for doing the later (an array
into a single varbinary value)?

Again that depends on how the values would enter the database. It
sounds to me that the easiest would be if the client composed the
binary arrays.

In particular for point 2 how would I cast arrays that vary from
2500 .. 3500 elements with integers to a single varbinary and of
course retrieve?

In which layer? In SQL Server you would have to write a user-defined
aggregate to build the varbinary(MAX). In SQL 2005 you would have to
workaround the restriction that a UDA cannot return more than 8000
bytes.

As for retrieving, look at fixbinary_singl e:

http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-length

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx