Avatar of rbhargaw
rbhargaw
Flag 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?
DatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Mike Eghtebas

- give some sample data
- show output desired
- lastly we talk about performance (after a solution is at hand)
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.
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vitor Montalvão

MongoDB topic removed.
Vitor Montalvão

How big is the table? How many rows it has?
rbhargaw

ASKER
I will try the filtered indexs, thank you ste5an
The max number of rows is 33000
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

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).
rbhargaw

ASKER
We are using SQL 2014. How do we put them in memory?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

rbhargaw, is your issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23