Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
- give some sample data
- show output desired
- lastly we talk about performance (after a solution is at hand)
- show output desired
- lastly we talk about performance (after a solution is at hand)
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.
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
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.
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).
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:
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
CREATE TABLE TableName(
PKColumnName1 int not null primary key,
Description varchar(100) not null)
WITH (MEMORY_OPTIMIZED=ON
Premium Content
You need an Expert Office subscription to comment.Start Free Trial