?
Solved

Which Method is More Efficient?

Posted on 2014-07-29
8
Medium Priority
?
223 Views
Last Modified: 2016-02-16
Hello,

I'm trying to decide which of two methods I should use for a project I am working on. The project involves retrieving data to populate 14 different drop-downs, but what you choose in one drop-down could change what you see in another drop down.

I am contemplating two different ways to do it.

1) Do a SQL Server query every time a drop-down changes. This obviously would get the data as I need it, but I could hit the database many times depending on how often someone changes the values they choose in any given drop down.

2) Do one big query that returns all the data I may possibly need (could return over 1500 records), store that data in the .NET Application Cache, and then use LINQ to filter out that cached data and return the data I need based off user selection.

Which would be more efficient? Is there another method that's even better that I haven't thought of?

Thanks!
Jeremy
0
Comment
Question by:infotechelg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40227287
For only 1,500 rows, you'll probably find that you like the performance a lot better if you read them all into your application and do the filtering there.
0
 

Author Comment

by:infotechelg
ID: 40227307
Kdo,

Thanks for the response! Interesting. I've always heard LINQ is really expensive, so I'm surprised by your answer. Perhaps I heard incorrectly.

I'll wait a bit before accepting as solution in case anyone else wants to ring in.

Thanks again!!
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 40227343
The idea behind ADO.NET is to get only what you need as much as possible. Less traffic on the network, more efficient use of the memory on the client. Even more so if it is a browser based application. Why do you think that most transactional web sites display the results of a search in chunks of 10 or so items? It's for efficiency.

Holding 1500 pieces of data in memory while you need only 14 is the most inefficient use of resources. And you have to think for the future. This might end up being 15000 pieces of data in a few months or a few years.

Hitting the database multiple time might be a problem when you are in a loop, but your thing is driven by the user, so the number of hits will be very small considering what most modern databases are capable of.
0
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40227493
You might consider a hybrid approach also. You could put your most-frequently accessed items into the cache, and then the lesser-queried objects would be retrieved from the database. You'd want to design this carefully, though, because it could get hairy if you don't strike the right balance.
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 40227517
Another issue that might influence your choice here is with changes in the base data.   Cached data will reflect the data at the time it was cached, not necessarily the current data....so you might be sticking your users with out-of-date data.
0
 

Author Comment

by:infotechelg
ID: 40227698
Thanks, everyone, for the thoughts.

Jens: Regarding cached data, this data won't change that frequently. And if it does, our admin tool resets the app pool in IIS, so the cache would be released and regenerated with new data anyway.

kaufmed: that is a good idea, but in this particular instance, the items will be used equally. I can totally see that solution in a different application however.

james: This is what I was thinking. Storing 1500 records when only a handful of them will be used per drop-down seems to be wasteful. I just didn't know if it was more "wasteful" to do it that way or to hit the database frequently that would return small data sets.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40227752
..but in this particular instance, the items will be used equally
I'd wager to say that the items in the very first drop-down will be used more than the rest  ; )
0
 

Author Comment

by:infotechelg
ID: 40227775
kauf,

You're right. The first drop down will always be the same depending on which of the 8 products you pick. Unfortunately, in this case, the first drop down will only 2-3 options so I don't know how much of a benefit it will be to cache that.

In fact, the most options one would see in any given drop down is 7. There could be 1500+ records (SKUs) for one product because the 14 different product configuration option possibilities, but when you start filtering the options down, the number of possibilities drops quickly.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question