We help IT Professionals succeed at work.

Table where row act as column

159 Views
Last Modified: 2017-04-10
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

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
CERTIFIED EXPERT

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
ste5anSenior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
MongoDB topic removed.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions