Table where row act as column

rbhargaw
rbhargaw used Ask the Experts™
on
I have a table where rows of the table have the column information. As there is limit on number of columns in the table so the table is designed this way where the column is abc and value is 1 but they are in a row. I was wondering what is the best strategy for adding indexes to improve the performance?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
- give some sample data
- show output desired
- lastly we talk about performance (after a solution is at hand)
ste5anSenior Developer

Commented:
First of all: You've tagged this post MongoDB? Is this correct?

Just a guess: You're working with an EAV-model (enitity-attribute-value). These models have their use-case. But as you already said: How do you index these or improve speed? Well, that is on point of this model: You use these model where entities have possibly a large amount of attributes, which are sparsely filled. Here the performance is simply achieved by only covering the entity.

So for your question: index the entity key column(s).

Then when you're using SQL Server, you can additionally try using filtered indices (using the WHERE clause when creating the index).

But: This kind of problem raises a question.. What is your concrete use-case? Cause it smells like reporting, Thus using an appropriate DW may make sense. Here you would "denormalize" your model to fit into a star-schema.
rbhargawFounder

Author

Commented:
Sorry, Its related with SQL Server, nothing to do with Mongo DB. It is Entity–attribute–value model. Its more like I have pivot the rows and I am just trying to see if indexes can help or not in this case
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer
Commented:
When it is an EAV and the model was chosen correctly for its use case, than you check your indices for covering ones over the entity selector. And you can create filtered indices for your most used attributes.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
MongoDB topic removed.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
How big is the table? How many rows it has?
rbhargawFounder

Author

Commented:
I will try the filtered indexs, thank you ste5an
The max number of rows is 33000
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Because of the design of the table I'm not sure an index will help.
Which version of SQL Server do you have? If MSSQL 2014 then you can put all table in memory for very fast access (like 100x faster).
rbhargawFounder

Author

Commented:
We are using SQL 2014. How do we put them in memory?
IT Engineer
Distinguished Expert 2017
Commented:
Good. In-Memory Optimized Tables were introduced in SQL Server 2014.
Read the article I just linked above to learn about how to use it. There's no big science since it's works as any regular table in SQL Server. You just need to prepare your database to support In-Memory Optimized Tables by adding a File Group to contains that kind of data:
ALTER DATABASE databasename ADD FILEGROUP newFileGroupName CONTAINS MEMORY_OPTIMIZED_DATA 
ALTER DATABASE databasename ADD FILE (name='newFileName', filename='<path\filename>') TO FILEGROUP newFileGroupName

Open in new window

Then create a new table to be stored in the just created filegroup:
CREATE TABLE TableName( 
   PKColumnName1 int not null primary key, 
   Description varchar(100) not null) 
 WITH (MEMORY_OPTIMIZED=ON

Open in new window

Now you just need to copy the data from the original table into the new In-Memory Optimized Table.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
rbhargaw, is your issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial