March 4, 2010 at 6:52 am
I am working on an asp.net application where I have to upload 2 excel files which contain millions of rows and different columns in first file have foreign key relation ships within them. The only way to create these relationship is by string matching. In the 2nd file the number of columns vary depending on the survey results. The maximum number of columns I have noticed so far is 21000. I am not sure if there is a way to some how dump these file directly into the tables and then normalize them using SP or anything else or weither we can create this many column in a table in SQL Server 200 or not ?
At the moment I am uploading these files (file physical size ranging from few k to 300mb) to web server, read the establish relationships between the rows and then save them. But it is very slow. Each survey could result in more than 10 million of insertions in the DB.
As you can imagine the database grows very quickly as more and more surveys are uploaded. After uploading 6 surveys I had more than 70 million record in my survey results table. Running any analysis queries such a huge data takes very long time.
Can someone please suggest me anything that could help in this scenario. any helpful suggestion will be appreciated.
Thanks
March 4, 2010 at 7:52 am
arslan_ch (3/4/2010)
In the 2nd file the number of columns vary depending on the survey results. The maximum number of columns I have noticed so far is 21000.weither we can create this many column in a table in SQL Server 200 or not ?
about the number of columns (21000):
sql 2000 and sql 2005: no
sql 2008: it depends if you have a lot of null values, if you dont have much null values and 21000 columns ... then it's also no
Columns per nonwide table: 1,024
Columns per wide table: 30,000 (requires a lot of nulls)
more info:
limits inside sql server:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
"wide table" / sparse columns
http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Sparse-columns-and-XML-COLUMN_SET.aspx
March 4, 2010 at 10:29 am
Thanks for your reply Eduard.
Actually each column in these 21000 columns is a row for another normalized table.
I am looking for a way to dump these file into the database and create all the columns dynamically. So that the upload process could be made robust. Secondly I am looking for any way to improve speed of analysis queries.
Do you know anything that can be used to dump this whole data into the database and dunamically creating the table, column names based on the first row of the file ?? ( Not sure it is possible using SSIS , DTS or anything else )
March 8, 2010 at 2:20 am
Sorry, i'm having difficulty comprehending 21000 columns which are referring to other tables. I have no idea without on site info what do with that. I would try not to start which such a datamodel to begin with.
I'm not experienced with SSIS and that sort of things so i'm sorry but i cant help you with that part.
The only advice i could give you based on how the situation looks to me:
make it manageble chunks: "You cant eat things which dont fit in your mouth" (this is not meant as childish).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply