[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Performance for classifieds site

Hi,

We're working on a classifieds site. It will be running asp.net & SQL Server.
Their current traffic stats are about 2000 visits a day.

For searching those classifieds, we need maximum performance. We came up with the following designs:

1. Load the data into a dataset, this would make the webserver take a big chunk out the memory I think, if the query wasn't really precise. On the other hand, paging and sorting would be very easy.

2. Load the data into a custom collection of objects. This collection implements ISortable to be able to sort, but paging would be a little harder. This seems like most beneficial solution, it doesn't go query the database on each page request and it doesn't use the heavyweight dataset.

3. When a user runs a search query, store the item id's in an intermediate table. Data to pass between pages would only be a queryid, to get the results back, inner joined with the data. This way the webserver is unloaded, but we do alot of database hits.

To specify: the list returned by the search query will most likely only incorporate the id, description, price and a date.

What do you think is the best solution and please comment your ansewer for grading.
0
boeman
Asked:
boeman
  • 4
  • 3
1 Solution
 
GrandSchtroumpfCommented:
> For searching those classifieds, we need maximum performance.
So, let the database do its job.  Databases are optimized for that.
Issue the SQL query and display the results, that's all you need to do.

For the pagination, use the "limit" inside the query.
I don't know the exact syntax for SQL Server, but it should be something like this:
SELECT * FROM CLASSIFIEDS LIMIT 20, 10

Same thing for ordering, include that in the SQL query:
SELECT * FROM CLASSIFIEDS ORDER BY TITLE
0
 
boemanAuthor Commented:
Sure databases are optimized, but executing a query (almost) everytime a user clicks is not only about the query. It's opening the connection, query, transport results and close connection, won't that be worse then taking the data offline?
0
 
GrandSchtroumpfCommented:
It should not be a problem.
I don't know ASP, but i don't think it allows you to manage the cache yourself anyway (at least that's the case for PHP).  I think you can do that with java servlets, but that's a different story.
In PHP CMS what is often done is that complete pages are cached either to the disk or to the database, this way the page does not need to be rebuilt.
As I said before, for best performance, the query should only return the items that will be displayed on the current page, so there is no caching to do on the query result anyway.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
boemanAuthor Commented:
The caching of the results in the intermediate table is to store the results of initial queries, these queries can be expensive because of the search criteria. For paging through the result, you only need the query id and inner join the item id's to the item details.
0
 
boemanAuthor Commented:
BTW: I don't think a function like LIMIT (MySQL?) exists in SQL Server...
0
 
GrandSchtroumpfCommented:
Yes, it's possible to store the ids of the selected records in a temp table.
But then, the database will need to go through all the records.
If you use the limit (i.e. limit 0,10), the query will be much faster because it will return as soon as it found 10 matches.
If you don't use the limit and the query returns 500 records, then it will be much much slower and you'll have 490 records that you don't need anyway.  So, not using the limit is something that should be avoided at all costs.
Using the limit for paging is what is done in all the projects i have ever seen.
0
 
GrandSchtroumpfCommented:
SQL Server uses "TOP" to return the top items, but i don't know if you can specify the offset.
http://docs.codecharge.com/studio/html/index.html?http://docs.codecharge.com/studio/html/Components/Properties/OptimizeSQL.html
It's important to use it for performance, it works well for browsing the items.

BTW, for a search i guess you need to get the complete result, or you won't get the number of hits...  So, I guess that won't help you much.  Sorry for the confusion.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now