February 13, 2014 at 1:23 pm
I'd like to find the best option for saving a boatload (100K +) of csv lists to my SQL DB.
Normally I know it'd be appropriate to create a table and expand out each csv list to fill a table (each list has ~500-1000 elements, the length pre-defined). But considering that I have thousands of them, that doesn't seem all that appropriate. Especially given that the data in each list satisfies the following conditions:
1 - the list is a list of numeric values, they can be mapped to integers if that makes it easier
2 - the list will only ever be accessed as 1 unit - you either need all the elements of a list or none of them at any given time
3 - the list elements are always accessed and used in the same order
4 - the list elements are static. Once loaded into the db they will not change, but more lists will be added at later times.
Would an XML data type be appropriate for this kind of problem? If so, could someone provide an example of what structure that might take? Or does someone have another good idea of what might work in this scenario?
February 13, 2014 at 1:40 pm
I'd still use a table. You just need to add some type of csv list id and a sequence #.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 13, 2014 at 1:44 pm
By using a table (and adding a csv list id, and a sequence number) won't I be tripling the size of the data that is required to be stored? My concern is as this keeps being added to it might become unwieldy in a single table.
February 13, 2014 at 1:53 pm
bkmooney (2/13/2014)
By using a table (and adding a csv list id, and a sequence number) won't I be tripling the size of the data that is required to be stored?
Perhaps. But XML will add much more overhead than that. The overall total size of complete list is still relatively very small.
My concern is as this keeps being added to it might become unwieldy in a single table.
Cluster the table on ( csv list id, sequence# ); list id could even be an identity column. Since lists are static once created, you shouldn't see any performance issues reading any list.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 13, 2014 at 2:05 pm
Perhaps. But XML will add much more overhead than that. The overall total size of complete list is still relatively very small.
good to know. Thanks so much - I will give this a shot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply