Writing double arrays to tables
hi,
i writing data database through c# using sqlbulkcopyreader. have double arrays written table column comma separated text. inefficient because size of text larger , increase io time considerabely.
i need speed reads , write as possible. size of double array constant. can do. looking @ binary data type couldnt find article shows how go writing arrays table.
help appreciated. thanks.
storage space:
it's not quite fair compare storage space arrays numbers have 1 digit did in sample (generatearray method, retval[ i] = random.next(10)
. if use random int value (just replace retval[ i] = random.next(10);
with
retval[ i] = random.next(int.maxvalue);
we'll see 3 times more storage space required comma-separated list, expected.
storage space results:
name rows reserved data index_size unused
csvdata 10000 63120 kb 62880 kb 160 kb 80 kb
binarydata 10000 23752 kb 23608 kb 96 kb 48 kb
bugs:
i have found bugs both in , code
. my bug used sqlbytes.buffer property input array of bytes, turned out buffer re-used when several calls made function within single query (multiple calls within single query can appear when array data stored in table, , not use case designed for), buffer may contain more data needed. i've replaced sqlbytes.buffer sqlbytes.value , works expected.
your bug tvf function specifies string parameter mapped nvarchar(4000) truncation occurs if data larger 4000 chars, updated method use sqlchars instead of string.
performance:
your code doesn't convert strings integer values i've created similar function it.
performance results:
(each query returns 4981059 rows)
binaryints - cpu time = 5834 ms, elapsed time = 31988 ms.
varchar csv without int conversion - cpu time = 8127 ms, elapsed time = 47723 ms.
varchar csv sql int conversion - cpu time = 8362 ms, elapsed time = 38951 ms.
varchar csv clr int conversion - cpu time = 10623 ms, elapsed time = 40446 ms.
code:
sqlclr assembly:
using system;
using system.collections;
using system.data.sqltypes;
using microsoft.sqlserver.server;
public class storedprocedures
{
[sqlfunction(fillrowmethodname = "splitstringintsfillrow", tabledefinition = "intvalue int")]
public static ienumerable splitstringints(sqlchars input, char delimiter)
{
return new string(input.value).split(delimiter);
}
public static void splitstringintsfillrow(object datapart, out int intvalue)
{
intvalue = int.parse((string)datapart);
}
[sqlfunction(fillrowmethodname = "splitstringfillrow", tabledefinition = "stringvalue nvarchar(100)")]
public static ienumerable splitstring(sqlchars input, char delimiter)
{
return new string(input.value).split(delimiter);
}
public static void splitstringfillrow(object datapart, out string stringvalue)
{
stringvalue = (string)datapart;
}
[sqlfunction(name = "fn_common_getints", fillrowmethodname = "fn_common_getints_fillrow", tabledefinition = "id int",
isdeterministic = true, dataaccess = dataaccesskind.none)]
public static ienumerable fn_common_getints(sqlbytes array)
{
byte[] bytes = array.value;
(int = 0; + sizeof(int) <= bytes.length; += sizeof(int))
yield return bitconverter.toint32(bytes, i);
}
public static void fn_common_getints_fillrow(object obj, out int id)
{
id = (int)obj;
}
}
console application:
using system;
using system.collections.generic;
using system.data.sqltypes;
using system.text;
using system.io;
using system.data.sqlclient;
using system.data;
namespace consoleapplication1
{
public class program
{
private static readonly random random = new random();
static void main()
{
using (sqlconnection conn = new sqlconnection("server=almaz;database=sqlclr_examples;trusted_connection=yes;"))
using (sqlcommand cmdvarbinary = conn.createcommand())
using (sqlcommand cmdvarchar = conn.createcommand())
{
cmdvarbinary.commandtext = "insert dbo.binarydata (binaryints) values (@binaryints)";
cmdvarbinary.commandtype = commandtype.text;
sqlparameter paramvarbinary = new sqlparameter("@binaryints", sqldbtype.varbinary);
cmdvarbinary.parameters.add(paramvarbinary);
cmdvarchar.commandtext = "insert dbo.csvdata (csvints) values (@csvints)";
cmdvarchar.commandtype = commandtype.text;
sqlparameter paramvarchar = new sqlparameter("@csvints", sqldbtype.text);
cmdvarchar.parameters.add(paramvarchar);
conn.open();
(int = 0; < 10000; i++)
{
int num = random.next(1000) + 1;
int[] ints = generatearray(num);
byte[] bytes = tobytearray(ints);
paramvarbinary.value = bytes;
cmdvarbinary.executenonquery();
string csv = getcsvstring(ints);
paramvarchar.value = new sqlchars(csv);
cmdvarchar.executenonquery();
}
conn.close();
}
}
public static byte[] tobytearray(ienumerable<int> ints)
{
using (memorystream stream = new memorystream())
{
foreach (int value in ints)
{
byte[] bytes = bitconverter.getbytes(value);
stream.write(bytes, 0, bytes.length);
}
return stream.toarray();
}
}
public static int[] generatearray(int length)
{
int[] retval = new int[length];
(int = 0; < length; i++)
retval[ i] = random.next(int.maxvalue);
return retval;
}
public static string getcsvstring(ienumerable<int> ints)
{
stringbuilder sb = new stringbuilder();
foreach (int in ints)
sb.append(i + ",");
return sb.tostring(0, sb.length - 1);
}
}
}
performance test:
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache('all')
print ''
print 'binaryints'
set statistics time on
select c.rowid, ss.id
from binarydata c
cross apply fn_common_getints(binaryints) ss
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache('all')
print 'varchar csv without int conversion'
set statistics time on
select c.rowid, id=ss.stringvalue
from csvdata c
cross apply splitstring(csvints, ',') ss
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache('all')
print 'varchar csv sql int conversion'
set statistics time on
select c.rowid, id=convert(int, ss.stringvalue)
from csvdata c
cross apply splitstring(csvints, ',') ss
set statistics time off
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache('all')
print ''
print 'varchar csv clr int conversion'
set statistics time on
select c.rowid, id=ss.intvalue
from csvdata c
cross apply splitstringints(csvints, ',') ss
set statistics time off
SQL Server > .NET Framework inside SQL Server
Comments
Post a Comment