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
Last Comment
Vitor Montalvão
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).
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.
Just a guess: You're working with an EAV-model (enitity-attribute-value).
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.
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
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
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
MongoDB topic removed.
How big is the table? How many rows it has?
ASKER
I will try the filtered indexs, thank you ste5an
The max number of rows is 33000
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.
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).
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).
ASKER
We are using SQL 2014. How do we put them in memory?
ASKER CERTIFIED SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rbhargaw, is your issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers
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
- show output desired
- lastly we talk about performance (after a solution is at hand)