By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

How to store big Arrays

P: n/a
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.......

Oct 24 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <ji**********@gm.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.......
Oct 24 '08 #2

P: n/a
On Thu, 23 Oct 2008 19:26:52 -0700, Tom van Stiphout
<to*************@cox.netwrote:
>On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <ji**********@gm.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
Oct 24 '08 #3

P: n/a
On Fri, 24 Oct 2008 07:29:37 -0400, "Roy Harvey (SQL Server MVP)"
<ro********@snet.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
<to*************@cox.netwrote:
>>On Thu, 23 Oct 2008 19:21:21 -0700 (PDT), jc <ji**********@gm.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
Oct 24 '08 #4

P: n/a
On Fri, 24 Oct 2008 05:55:41 -0700, Tom van Stiphout
<to*************@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
Oct 24 '08 #5

P: n/a
jc (ji**********@gm.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_single:
http://www.sommarskog.se/arrays-in-s...l#fixed-length

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

Oct 24 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.