CODEWITHSUNDEEP
c#c#-4.0
Goran
Goran

Reputation: 6518

What is a better approach performance wise

Lets say I need to fetch some records from the database, and filter them based on an enumeration type property.

when displaying records, there will be a predefined value for Size filter (ex Medium). In most of the cases, user will select a value from filtered data by predefined value. There is a possibility that a user could also filter to Large, then filter to Medium, then filter to Large again.

I have different situations with same scenario:

What is my best approach here? Should I have a tab that will contain grid for each enum, or should I have one common enum and always filter, or?

Upvotes: 0

Views: 147

Answers (2)

Matthew
Matthew

Reputation: 25743

I would do the filtering right on the database, if those fields are indexed I would suspect having the db filter it would be much faster than filtering with c-sharp after the fact.

Of course you can always cache the filtered database result as to prevent multiple unnescessary database calls.

EDIT: as for storing the information in the database, suppose you had this field setup:

CREATE TABLE Tshirts
(
    id int not null identity(1,1),
    name nvarchar(255) not null,
    tshirtsizeid int not null,
    primary key(id)
)

CREATE TABLE TshirtSizes
(
    id int not null, -- not auto-increment
    name nvarchar(255)
)

INSERT INTO TshirtSizes(id, name) VALUES(1, 'Small')
INSERT INTO TshirtSizes(id, name) VALUES(2, 'Medium')
INSERT INTO TshirtSizes(id, name) VALUES(3, 'Large')

ALTER TABLE Tshirts ADD FOREIGN KEY(tshirtsizeid) REFERENCES tshirtsize(id)

then in your C#

public enum TShirtSizes 
{
    Small = 1,
    Medium = 2,
    Large = 3
}

In this example, the table TshirtSizes is only used for the reader to know what the magic numbers 1, 2, and 3 mean. If you don't care about database read-ability you can omit those tables and just have an indexed column.

Upvotes: 1

MattMacdonald
MattMacdonald

Reputation: 103

Memory is usually cheap. Otherwise you could one-time sort all the values and retrieve based on comparison which would be O(n). You could keep track of the positions of things and retrieve faster that way.

Upvotes: 0

Related Questions