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?

rbhargawFounderAsked: 2017-03-21

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2017-03-21

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

LVL 44

ste5anSenior DeveloperCommented: 2017-03-22

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.

rbhargawFounderAuthor Commented: 2017-03-22

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

LVL 44

ste5anSenior DeveloperCommented: 2017-03-23

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.

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-03-23

MongoDB topic removed.

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-03-23

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

rbhargawFounderAuthor Commented: 2017-03-23

I will try the filtered indexs, thank you ste5an
The max number of rows is 33000

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-03-24

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

rbhargawFounderAuthor Commented: 2017-03-24

We are using SQL 2014. How do we put them in memory?

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-03-27

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-04-10

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