Array processing in SQL

[Peter Huang]

Hi,

I frequently work with arrays sent in from components to stored procedures
as a way of minimising network calls and increasing performance. But
working with arrays in SQL is quiet a stumbling block.

Are there any native T-SQL commands for array processing? The cheapy
User-Defined Function I created to read array elements in the form of comma
separated string isn't heavy duty enough.

Suggestion please?

Thanking in advance,
Pete

[Narayana Vyas Kondreddi]

Not native support available. Please search through this archive, as very recently, people discussed some workarounds.
--
HTH,
Vyas
http://vyaskn.tripod.com/
SQL Server 7.0 Replication FAQ and commonly encountered problems:
http://vyaskn.tripod.com/repl_ques.htm
Interesting code samples (SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm
Preparing for a DBA/DB Developer interview? Check out the interview questions:
http://vyaskn.tripod.com/iq.htm

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Joe Celko]

The short answer, is SQL has no arrays; you have to uses tables. If you are really doing array processing, you can model an array A[i,j] with a table that has a column for the subscripts and the element value. It is not pretty.

Instead of trying to write a user defined function to handle one row at a time, you can fill a whole table with rows that are each a list of parmeters that your stored procedure can be use. Let's assume that you have a whole table full of such comma delimited parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '32,54,97,896');
...

This will be the table that gets the outputs, in the form of the original key column and one parameter per row and an ordering number within that parameter list.

CREATE TABLE Parmlist
(keycol CHAR(5) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL,
sort_col INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end with a comma. Now, the answer, which could be a VIEW instead of an INSERT INTO statement:

INSERT INTO ParmList (keycol, parmlist, sort_col)
SELECT keycol,
CAST (SUBSTRING (',' || I1.input_string || ','
FROM S1.seq +1
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER),
S1.seq
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) = ','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) = ','
AND S1.seq < S2.seq
AND S2.seq <= CHARLENGTH (I1.inpuststring) + 1
GROUP BY I1.keycol, I1.input_string, S1.seq
ORDER BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of commas, and the *entire set* of substrings located between them is extracts and cast as integers in one non-procedural step. The trick is to be sure that the right hand comma of the bracketing pair is the closest one to the first comma.

You can then write code that will take a table, Foobar, and join it to the parameter lists using their keys. The comma position will act as a sort key:

SELECT F1.*, P1.*
FROM Foobar AS F1
INNER JOIN
Parmlist AS P1
ON F1.keycol = P1.keycol
WHERE ...
ORDER BY P1.keycol, P1.sort_col

--CELKO--

SQL guru at Trilogy
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

[Peter Huang]

Is table operations faster than string operations?

I'm working with multi-dimensional arrays which looks something like this:
4&6&7&|2,3&4&7&|1,5|2,1&2&|2,2|3,1&13&14&|1,5&6&|1,1|1,1|1,4&10&|1,1:2:3:4|1
,1:2:3:4|1,1:2:3:4|1,1:2:3:4|1,4:5|1,8:10|1,3:5|1,1:2:3:4|1,6:7|1,1|2,1|1,1|
1,1:2|2,2|2,1:2|2,3|2,2:3|2,3|2,1:2|2,1:2|1,4|2,2|2,2|1,2|2,2:3|2,1:2|2,1:2|
2,1:2|2,1:2:4|2,2:3|2,1:2|2,1:2|2,2|3,1|3,1|3,2|3,2|3,3|3,3|3

The stored procedure uses information supplied in the array to search for
records, and generates an accurate percentage representing of how accurately
the data matched the search.

It proves to be a challenge storing all that into tables. Is it worth doing
performance wise? It takes about 2 seconds to scan through 40 records, but
the table is expected to have thousands if not tens of thousands of records.
:-( Should I redesign the entire structure of the DB?

Pete,

[Erland Sommarskog]

Possibly. Possibly not. It is difficult to tell with the scarce information
you have provided.

Basically I can think of three ways of getting the data into the database:

1) Call an SP that inserts row-by-now. There will be many procedure calls.
2) Pass the values in a string to an SP that unpacks and insert.
3) Use the bulk-copy operations to load the data.

The latter is quite extreme to use in an application for daily use,
but there are occassion where it makes sense. The bulk-copy can be
activated through command-line BCP, from an API. (Although I'm not
fully certain that ODBC and OLEDB offer this; I know that DB-Library
does.)

--
Erland Sommarskog, Stockholm, som...@algonet.se