Link to home
Get AccessLog in
Avatar of rbhargaw
rbhargawFlag for United States of America

asked on

Table where row act as column

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?
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

- give some sample data
- show output desired
- lastly we talk about performance (after a solution is at hand)
Avatar of ste5an
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.
Avatar of rbhargaw

ASKER

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
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
MongoDB topic removed.
How big is the table? How many rows it has?
I will try the filtered indexs, thank you ste5an
The max number of rows is 33000
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).
We are using SQL 2014. How do we put them in memory?
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
rbhargaw, is your issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers