ecommerce solution with huge data

[ketankshah]

I am developing an ecommerce solution using ASP and SQL Server. I have around half a million items in my items table. The search results is displaying only 25 records on a single page with the facility to go to previous or next page. Since the data is huge, what method should I use to retrieve the data fast since I am showing only few records at a time. I have given search criteria to filter number of records but still on some criteria the results can be huge.

What is the best method to achieve this with optimum performance?

Ketan

 

[foxdev]

This will probably end up being more of a SQL Server question, but I'll take a shot:

Getting the first 25 matching rows is not a problem:

[tt]select top 25 SKU, Description, Price
from MyProducts
where Category = MyCategory
order by Price [/tt]

The problem would probably be getting a specific block of 25; IOW, you've already shown the first 25, how do you get the next 25?

I'm really wingin' it, but assuming that each row in your products table has a unique key (such as a SKU or Identity column), you could try this for page 2:

[tt]select top 25 SKU, Description, Price
from MyProducts
where Category = MyCategory
and MyKeyValue > MyLastValue
order by Price [/tt]
where MyLastValue is the key value for the last displayed item on Page 1.
--------------
School supplies for the needy:

http://www.agrainofhope.org/classzoom.shtml

 

[fhlee]

The best way is actually having a sequential key like what foxDev mentioed, but chances are this won't happen in a table where data changes from time to time (deletion, addition, modification, etc).

Suggestions:
1. Open the recordset using ADODB instead of ODBC as they are faster.
2. Is the 'delay' occurring during the opening of the recordset? or you searching for the record. If it is occuring during the 'opening' stage, you may want to try opening the recordset with a Server cursor instead of a Client cursor. I know that really help. I have a recordset of over 10 million records ... Opening it using a client cursor takes about 45 seconds ... about 5 seconds using the Server cursor.
3. Sorry if I seems like insulting but I'm not, just know that some programmers tend to 'loop' until a certain record using recordset.moveNext instead of using the recordset.PageSize and recorset.AbsolutePage properties. This really help to do the paging (of 25 records) part.

Net-net, 500k records should not be a problem with SQL and ADODB provided that they are all configured correctly. This is only an average sized recordset. If it is a stored procedure, you may want to try to isolate if this is a StoreProcedure/View optimization problem instead of an ASP/ADODB/ODBC problem. Good luck.


regards,
- Joseph
================ ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[ketankshah]

Thanks both of you.

Thanks for suggestions. I will try both. Suggestion of foxdev want help much if the order by clause is Description. But I can use it in a different way. I can use the Description column in combination with SKU which is a unique key.

select top 25 SKU, Description, Price
from MyProducts
where Category = MyCategory
and Description + cast(SKU as varchar(10)) > MyLastDesc + MyLastSKU
order by Description

I can use it this way but will it make any effect of performance since I am using cast in the where clause?

I will also try the optimisation tips which Joseph has suggested.

Ketan