Query Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Query Optimization

Author

Topic

alockrem
Starting Member

18 Posts

Posted - 2009-07-30 : 19:47:27

I need help optimizing a few tables.

Concept - My application allows users to upload Excel spreadsheets and I create a SQL data source. Since I won't know the number of columns or rows ahead of time I only know of 2 options to make this work.

Option 1 - Create a table that imports all data from the excel spreadsheet each time a spreadsheet is uploaded. This could result in 1000 tables which seem difficult to manage.

Option 2 - Create a "Columns" table, a "Rows" table, and a "Values" table. When a spreadsheet is uploaded it creates a record for each row and loops through the data from the spreadsheet creating "row" records and "value" records.

I chose option 2. I'm not sure it's the most efficient choice, but I would rather manage 4 optimized tables than 1000 user-defined tables.

Any suggestions of how I should setup the indexes to optimize these 4 tables?

DataSources:
DataSourceID (int) pk
DataSourceName (varchar(250))

Columns:
ColumnID (int) pk
DataSourceID (int)
ColumnName (varchar(250))

Rows:
RowID (int) pk
DataSourceID (int)

Values:
ValueID (int) pk
ColumnID (int)
RowID (int)
Value (varchar(250))

Thank you for any help.

alockrem
Starting Member

18 Posts

Posted - 2009-07-30 : 21:05:22

I am using a pivot command to pull the tables together. Viewing the data is working perfect.

Is there a way to submit multiple records using an @@identity as part of the insert statements? I have to get the column IDs and row IDs before submitting the values. As of right now I have it using seperate SQL statements but a table with 10 columns and 100 rows takes a while to complete.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-30 : 22:52:10

on your Columns table, why don't you use the column and row from excel rather than identity ?

Columns:
ColumnID (varchar(2)) pk
DataSourceID (int)
ColumnName (varchar(250))

the ColumnID will have value like 'A', 'B' .. . 'IV'

and for the RowID just use integer (not identity) with the row value from excel. And you don't need the Rows table at all.


DataSources:
DataSourceID (int) pk
DataSourceName (varchar(250))

Columns:
DataSourceID (int) pk
ColumnID (varchar(2)) pk
ColumnName (varchar(250))

Rows:
RowID (int) pk
DataSourceID (int)

Values:
DataSourceID (int) pk
ValueID (int) pk
ColumnID (varchar(2)) pk
RowID (int) pk
Value (varchar(250))

KH
[spoiler]Time is always against us[/spoiler]

alockrem
Starting Member

18 Posts

Posted - 2009-07-31 : 08:57:34

Thank you for the response.

My only concern is about updating the data source. What if 100 records are created referencing the data source and then the user decides to update the data source and move the columns around. In my current design I can adjust for that. I'm not seeing an easy way to support that without the rows table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources