my query is slowing down!

[wengang]

Hi all.
I guess the first thing I need to know is:
if I set the pagesize of my recordset for an Access database, connected by ADO, does the size of the datatable still affect the speed of the query?

My datatable has about 30,000 records and growing. The query retrieves all records, but the pagesize is 100 records. So, I am thinking that, regardless how many records there are, only 100 records are being dealt with whenever the page loads (ASP), and therefore the speed should remain constant. is that wrong?
This page has started taking forever to load. The amount of data in each record is pretty small. Only a few fields and mostly numbers

Okay, besides that, a couple of basic questions. Am I doing this in an inefficient manner? Is pagesize not the way that sites display X records per page?
This is my current system. Please advise if you'd do it differently:

SET rs6=CreateObject("ADODB.Recordset")
sql6 = "select * from tblPageLog"
rs6.Open sql6,conn,1,3
rs6.pagesize=100
rs6.absolutepage=request("page")
for t = 1 to rs6.pagesize
if not rs6.eof then
'display the data in a table......
rs6.movenext
end if
next

And, I also have a SqlServer database on this site. Can anybody tell me definitively if SQLServer is faster in any sense than Access? I've been thinking of moving the whole site to SQLServer.

[The_Duck]

I assume indexing is correclty setup your database. This can make a big difference when looking at a query performance !

[szlamany]

Why are you retrieving 30000 records?

That's an expensive operation regardless of provider...

[Ecniv]

Not sure if this might be an idea, a disconnected recordset, held on the server via a query mechanism of some sort for a time period. Then you only need to hit the db once, but not sure if this would still be initially slow.

The select statement would be slow to build as you are returning the 30k records and getting only 100 of them. Never using pagesize, I am unsure how this will affect it - I will have a browse online shortly

If you are sending to the screen, web page, perhaps some css and put the 100 recs into divs, hiding the newer ones?
Or every 20 or so flush the write buffer to the user so the screen gets updated?

Edit:
MS Site info
Says that you are still returning the 30k records, just that they are grouped page wise by 100 records.

[wengang]

so, if I want to show the records 100 at a time, and I don't want to pull the 30k (and growing), what way is there to do it?
I know I could use Top 100 for the first page, but what about page 2?

[szlamany]

Why not show them A, B, C and so on - so they can choose a letter of the alphabet?

What is the basic layout of the data?

[wengang]

well the data is website activity. My boss wants to know which area of the site visitors spend the most time on. So I log the URL, including parameters, on each page visit. Then this page, located in the admin area, takes the 100 records and rebuilds the specific information for that page visit based on URL and params. The data can also be filtered by users, or by groups of users, or over a time period, so the sql statement is not quite as simple as I wrote in the example. The results are ordered by date/time. Ideally, with access to all record, somebody could click next page until they have gone through the entire recordset.
I tried adding Top 1000 to the SQL statement and it sped the page up quite a bit. That supports the argument that the entire 30k are being retrieved. But as long as I have 30k records, why should I be limited to the recent 1000?
And besides, how does Google return 1,000,000 results in just a few seconds. There must be a way to request records X thru Z in a recordset, the way that Top X works, without retrieving the whole stack.

[wengang]

hey all.
sorry, this time I do have to disagree.

I left out one detail on this page. I did a second query that matched the first query, and used every 100th record's date to fill a dropdown box of page numbers, so that the user could quickly jump to a page that corresponded to a certain date (the records were in time order, newest first)
So it looked like
Page [5 (January 1,2005) ] of 338 Showing 401 to 500 of 33796
the [] is a select box
This query actually was running through the entire recordset.

Then I cleared the second query and loaded the same box with just the numbers from 1 to recordset.pagecount, so that it looked like:
Page [5] of 338

When that page runs, it takes only a second to load, still showing that there are 338 pages of data, 33796 records. but it loads almost instantly

So, thinking about the other query, which was identical, the only difference being that I didn't apply the pagesize to that recordset, deleting that code should only make the page twice as fast (doing half the work). Based on that, I'm concluding that using pagesize does limit the size of data returned to the size of the absolutepage records.

Anyway, a moot point, just wish I didn't have to sacrifice the date in the select box. but it's resolved now.

Thanks all.

[szlamany]

It sounds like you are moving towards my suggestion (just show A, B, C and so on) with your idea of showing the page/date of every hundredth record.

In my opinion, it would have been better to run an aggregate query on the server - returning for example the Month and Year along with the activity count - for example 01/2005 (1500 entries) followed in the drop down box by 02/2005 (1700 entries). These types of aggregate queries run extremely fast in SQL and pass very little data to the user.

But the user still gets a good "idea" of what is available to make a selection against, and when they choose 02/2005 you then go after those 1700 entries - or another drop down box if possible.