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

 

[snowboardr]

I would have default records per page: 10 then give the user the choice to kick it up to 25 per page. Most won't change it so it will compinsate for those who do. Im pretty sure SQL can handle it though. Oh, and idea just came to me, You could port your ASP script to a visual basic DLL, this would be much faster because its compiled. Its pretty easy to do.

Check out my site which has a tutorial on creating custom dll's

http://www.vzio.com/

www.vzio.com
ASP WEB DEVELOPMENT

 

[ketankshah]

But since the query fetches a recordset of huge number of rows say 10,000 but the page only wants 25 records, how the SQL will support this kind of request? In case I use DLL then also it will call the database everytime it is executed on the page and query the same 10,000 records. So it will be helpful if you can explain how this can be done.

Ketan

 

[snowboardr]

Try this:

<%

Dim strRecordsToShow, CurrentPage, NumPerPage

strRecordsToShow = Request.QueryString(&quot;p&quot;)
NumPerPage = &quot;15&quot; '# Default

If strRecordsToShow = &quot;&quot; then
	NumPerPage = strRecordsToShow
End If


'Retrieve what page we're currently on
If Request.QueryString(&quot;page&quot;) = &quot;&quot; then
    CurrentPage = 1 'We're on the first page
		Else
	CurrentPage = CInt(Request.QueryString(&quot;page&quot;))
End If


mySQL = &quot;SELECT * FROM table_name.....&quot;
rs.Open mySQL, conn, 1, 1   'Opened as Read-Only

If Not rs.EOF Then
 	rs.MoveFirst
	rs.PageSize = NumPerPage
    TotalPages = rs.PageCount

    'Set the absolute (current) page
    rs.AbsolutePage = CurrentPage
End If

Dim Count

'Loop to display data on current page.

Do While Not rs.EOF and Count < rs.PageSize


'# Display records here
  
  
rs.MoveNext
Count = Count + 1
Loop

rs.close
set conn = nothing
%>
                 
<table width=&quot;15%&quot; border=&quot;0&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot; align=&quot;right&quot;>
     <tr> 
   <td width=&quot;42%&quot;> <font size=&quot;2&quot; face=&quot;Verdana, Arial, Helvetica, sans-serif&quot;> 
   <% If Not CurrentPage = 1 Then
Response.Write &quot;<a href='&quot; & &quot;?page=&quot; & CurrentPage - 1  & &quot;'><b>Prev</b></a> | &quot; & &quot;</center>&quot;
Else
Response.Write &quot;Prev | &quot;
End If
 %>
                      </font></td>
                    <td width=&quot;29%&quot;> <font size=&quot;2&quot; face=&quot;Verdana, Arial, Helvetica, sans-serif&quot;> 
                      <% If Not CurrentPage = TotalPages Then
						Response.Write &quot;<a href='&quot; & ScriptName & &quot;?page=&quot; & CurrentPage + 1  & &quot;'><b>Next</b></a>&quot;
						Else
						Response.Write &quot;Next&quot;
						End If
					  %>
                      </font></td>
                  </tr>
                </table>

www.vzio.com
ASP WEB DEVELOPMENT

 

[ketankshah]

But in the above example, the sql query will be fired on each page and the result will have around 10,000 records. For paging I am using the same concept but I am worried that to show only 15 records I have to retrieve 10,000 records which is unnecessarily making the site slow.

Any suggestions?

Ketan

 

[snowboardr]

I dont understand why it is getting 10,000 records? That is the point of paging is to only show so many number of records at a time.

www.vzio.com
ASP WEB DEVELOPMENT

 

[ketankshah]

Yes I agree that paging is to show 15 records at a time. But this is used only for display.

The statement
mySQL = "SELECT * FROM table_name....."
rs.Open mySQL, conn, 1, 1 'Opened as Read-Only

will fetch 10,000 records in the rs recordset first than with the help of paging we are restrict the logic to display on a nubmer of records per page. Then if we go to the next page again the same query will be fired

mySQL = "SELECT * FROM table_name....."
rs.Open mySQL, conn, 1, 1 'Opened as Read-Only

and again it will retrieve 10,000 record into the recordset.

Am I right?

 

[snowboardr]

This is a good question, I am not sure if it does pull all records, I thought it only pulls the ones that are shown to the page, but I just don't know.. hmmm

- Jason www.vzio.com
ASP WEB DEVELOPMENT

 

[MichaelRed]

I'm reasonably sure that the behaviour is data base engine dependent. Ms. Access PROCESSES all resords, regardless of the request.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dianal]

It always pull all you asked for in your SELECT statement.
Use something like this:
//select top 25 will return only 25 records and it will be quicker!

//the code is metacode - you'll have to adjust the syntax
pageSize = 25
//next page
myCommand = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
"WHERE CustomerID > '" + lastVisibleCustomer + "' ORDER BY CustomerID";
//prev page
//myCommand = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
&quot;WHERE CustomerID < '&quot; + firstVisibleCustomer + &quot;' ORDER BY CustomerID DESC&quot;;
//default:
//myCommand = &quot;SELECT TOP &quot; + pageSize + &quot; CustomerID, CompanyName FROM Customers ORDER BY CustomerID&quot;;

Figure out how to set lastVisibileCustomer and build a function to get data for next or previous page using myCommand to get the recordset you need.

 

[MichaelRed]

I don't think the issus is the RETURNING records, but the PROCESSING. If the 'procedure' processes 10K records to return 10, 15, or 25, the only potential savings is in the net traffic, not the processing. While the net traffic savings are considerable, I believe the question is in regard to (perhaps) ALSO not processing the 10K records in the db transaction on each 'page' presentation. In the case of Ms. A, the procesure would -in theory- ALWAY Process and transfer the entire 10K recordset. I practice, it appears that Ms. A is quite efficient in cacheing the records so the transfer appears to only move the entire recordset on the first request, and some occassional updates thereafter.

Since SQL is &quot;set' orientated and by deffinition would always process the cross-product of the possible recordset, and then remove records not matching the criteria, I so not see any useful approach to avioding the db PROCESSING, but the network transfer can be minimmized. This is, however, dependent on the dbengine. As previously stated, Ms. A supposedly ALWAYS returns the cross product recordset to the requestor, while some other products (SQL Server | Oracle ... may process the entire recordset but return only the (filtered) results set to the requestor. Then, again, Ms. A APPEARS to have an excellent cacheing processm so that the entire recordset seems to be transferes in an initial request, while only sub sets (updates) seem to be processed on subsquent calls.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over