Binary fields efficiency

Hi there,
I have a record where associated with it are upto 1000 floating point
values, would it be more efficient to have a variable sized binary field to
hold an array of floating point values, or is it fine to create another tabl
e
to hold these floating points required of the records. I'll have thousands
of the records, although they wont be accessed often - ie, once during an
import.
Thanks
K.Create the table. You are using a Relational database, which works
really well when the data is structured relationally. Use the
relational approach and you probably won't have to ask for help here
very often. Use the non-relational approach and you will generally
get a lot of heartburn when SQL Server doesn't have the tools to work
with the data. And when you end up asking for help here, you will
mostly get criticism of your database design!
So create the table. SQL Server handles thousands of rows very
efficiently, and the SQL you will need to manipulate them will be
clear and straightforward.
Roy Harvey
Beacon Falls, CT
On Thu, 2 Mar 2006 02:45:28 -0800, "Katie Viola"
<KatieViola@.discussions.microsoft.com> wrote:

>Hi there,
>I have a record where associated with it are upto 1000 floating point
>values, would it be more efficient to have a variable sized binary field t
o
>hold an array of floating point values, or is it fine to create another tab
le
>to hold these floating points required of the records. I'll have thousands
>of the records, although they wont be accessed often - ie, once during an
>import.
>Thanks
>K.

Best way to split data in a table

I am working on a stored procedure that ultimately puts data into an excel report. What I'm looking for is the most efficient way to "split" the data in the table if the table has more than 65535 rows. I have already coded for more than one report if this happens, but I still need to split the data. I could do "SELECT TOP 65535..." for the first report, but that leaves me with the problem of getting the next 65535 into the next report, etc. Also, I thought about using a cursor, but as far as I know a SQL Server cursor can only return 1 row at a time. This is the code I have so far for this part of the stored procedure:

DECLARE @.RecordCount FLOAT, @.RowCap FLOAT, @.Counter INT, @.NumberOfReports INT

DECLARE @.AttachmentList NVARCHAR(MAX)

SET @.RowCap = 65535

SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable

SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)

SET @.Counter = 1

WHILE @.Counter <= @.NumberOfReports

BEGIN

SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'

SET @.rptName = Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+

CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))

SET @.rptPath = '\\Reports\'

SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'

IF @.Counter = 1

SET @.AttachmentList = @.rptOutputFile

ELSE

SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile

EXEC sp_makewebtask

@.htmlheader = 3,

@.outputfile = @.rptOutputFile,

@.query = 'SELECT * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.

@.resultstitle = @.rptLongDesc,

@.webpagetitle = @.rptName

SET @.Counter = @.Counter + 1

END

As you can see, the @.query parameter for sp_makewebtask needs to be fixed. I would appreciate any suggestions. Thanks.

Dave

DECLARE
@.RecordCount FLOAT,
@.RowCap FLOAT,
@.Counter INT,
@.NumberOfReports INT

DECLARE @.AttachmentList NVARCHAR(MAX)

SET @.RowCap = 65535

SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable
SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)
SET @.Counter = 1
WHILE @.Counter <= @.NumberOfReports
BEGIN
SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'
SET @.rptName = 'Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+
CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))
SET @.rptPath = '\\Reports\'
SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'
IF @.Counter = 1
SET @.AttachmentList = @.rptOutputFile
ELSE
SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile
EXEC sp_makewebtask
@.htmlheader = 3,
@.outputfile = @.rptOutputFile,
@.query = ' SELECT TOP (@.cnt*65535) * FROM ##MyTempTable
EXCEPT
SELECT TOP ((@.Counter-1) * 65535) * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.
@.resultstitle = @.rptLongDesc,
@.webpagetitle = @.rptName
SET @.Counter = @.Counter + 1
END

Note: If you think of performance issues, I would suggest to create a identity column on your temptable and split the data based on that.|||

Hi,

Another possibility is using partioned tables. You set an identity column in the table and create a partition function on that column with intervals of 65535.

Reference: http://msdn2.microsoft.com/en-us/library/ms188730.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog