2

I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I have a couple hundred entries and the CSV files are about 25 megabytes each.

Each of these rows contain a row label, a data type label, a start position, and an end position; so about 4 fields per row.

The row label id has over a million potential values (nor do I have a complete list of potential values), and will not appear in the same order in each record nor necessarily even appear.

The data type label is categorical.

The start and end position fields are large integers.

My thoughts are to create a table with Record names and supporting data, another with the categorical label of the data, but I am being challenged on how to deal with the 750,000 columns per entry and the start and end positions.

I was thinking of big data table with all 750,000 rows per entry. but it seems like it would be very inefficient to approach it this way; the table looking something like this.

  • RecordID
  • RowLabel
  • CatLabelID
  • StartPosition
  • EndPosition

picture of a whiteboard

I am running SQL Server 2012 standard edition.

What is the best practice schema to address this type of large data set?

  • What do you mean 750k rows per entry? Does that mean that entering one record into the database will generate 750k more records somehow or are you using "entry" instead of "table"? – Erik Mar 19 '16 at 1:35
  • Yes , each test entry has 750,000 rows of data associated with it, so if i did a simple insert for two entries into a flat table that would mean 1.5 million rows. 3 entires is 2.25 million rows. – CRSouser Mar 19 '16 at 1:42
  • 1
    So 200 entries * 750k associated rows = 150 million total rows. What kinds of queries are going to be performed? – Erik Mar 19 '16 at 1:46
  • Yes, huge number of rows. Queries: Primarily attempting to determine counts of row labels and determine their ranges/concentrations as well as determine overlapping positions between different entries in attempts to determine potential relationship based on label and position correlation. – CRSouser Mar 19 '16 at 1:51
  • 150 million rows - SMALL. REALLY SMALL. 1 million was small 30+ years ago (literally when MS bought Foxpro to integrate them into MS Access because they could handle 1 million rows easily).. Today - we are WAY better than that. – TomTom Feb 10 at 19:22
0

I would just go with a bigint with Identity column. Just remember to start it at the negative seed value since sql server does not support unsigned ints. Something like this...

CREATE TABLE Records
(
 RecordID bigint IDENTITY(-9,223,372,036,854,775,808,1),
 RowLabel varchar(255) NOT NULL,
 CatLabelID INT,
 StartPosition varchar(255),
 EndPosition varchar(255)
)
  • Why start with negative? You worried that they'll reach the 8 Exa (8 billion of billions) rows limit? – ypercubeᵀᴹ Apr 20 '16 at 18:06
  • Just best practice in my opinion, why use the same amount of space to store the value per row but eliminate half of the range? I would rather have the available space and not need it rather than needing it and not having it. – James Rhoat Apr 20 '16 at 18:28
  • 1
    You'll have to take out all those commas in the seed value for the identity. The identity only expects 2 parameters, the seed and the increment. As you have it, that is 8 parameters. – John Jun 2 '17 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.