Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2005-04-15 : 00:49:31
|
| I have been reading everything that I can find in regards to paging through large recordsets. I am dealing with tables that are about 500,000 records and grow at a rate of about 1000 records a day.I wrote a stored procedure which with some luck and help actually worked. This however was a fluke in that it worked because I was doing an order by using the primary key ID field. Changing it to sort on other columns would not work. After reading now I understand why.I have been reading a lot about the temp table approach to paging where a temp table is created containing the ID of each record that is returned from the query. You then use this temp table to join the full info from the main table. You then page through the temp recordset as it is ordered in the proper order as it was stuffed.A few questions regarding this method. Is the creation of a temp table wasteful as far as time or memory? When a temp table is created do I need to worry assigning it a unique name so that each query has its own temp table, or is this taken into account in the sql system, assigning the same name but relating it to the connection etc...?Do I need to create this table each time I put in a request for a new page? in other words sort through the db, return the record ID to pupulate the temp table then join the temp table with the records and suck up the next group of records using the offset rowcount technique. Is there a way to keep the table from going away so I dont have to do this each time, or only do it when i get to the end of the temp table and then suck up the next N number of records?The web site that this is to be used on gets approximately 1.5 million page requests a day where it will need to make use of this stored procedure. Is this type of temp table paging method able to keep up with that?I have found a stored procedure on Jeff's Blog that allows dynamic SQL queries using the SP. I would like to use this if possible, does anyone have any experience with this one? Any other suggestions would be greatly appreciated. Reading through these forums there is a ton of info on paging through recordsets, but they always seem to leave things in the air, many written several years ago. By now there should be something that works I would think. Thanks, PhilI used to be a rocket scientist. Now I am just a space cadet... |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-04-15 : 10:48:04
|
| Hmm.I just read some stuff on indexed views. that might be better for you than a temp table. Of course the gurus will really know.if you are using asp, I would really look at getstring to retrieve your records if possible, its the fastest way to get data on the page. getrows is 2nd, and its more flexible.In addition, have you put any additional indexes on the tables being searched? it might help on the most searched fields.________________________________________________As the only Republican that likes the Green Party. I am a contradiction. |
 |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-04-15 : 16:10:15
|
| I am not sure if I can use indexed views as the data that is displayed varies either by content or by the user selecting the various sort and order parameters. The use is to pull up thumbnails in a gallery of X thumbs per page along with the technical info for each image. There are between 30 and 90 thumbs per page being diplayed. So each time I advance to the next page another query needs to be made to get the next group of image info. At present there are about 500,000 images in the database. Obviously I have no need to get all 500,000 images, I really only want to get 30 to 90 of them. ADO retrieves the entire DB which is out of the question, the method I have been trying is very fast and only gets the 30 - 90 rows I need, but not sure it is scalebale.Here is part of the original stored procedure. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48211This was used to get a total count so that I knew how many pages there were. It works quite well however I doubt it is very efficient in that I have to run it each time a person clicks on a page to first get the total count and then run a similar one that contains all of the joins and actually returns the data. So that is 2 stored procedures that need to be run every time someone clicks on a page. There must be a better way. The other problem is that with this method, I cannot order the data using any other rows other then the PK image_id row. I have about 6 order options that I need to use. There are far too many other WHERE options to create a fixed table for each variation hence looking at the temp table approach. That is why I am asking if it is efficient and if it can persists so that I don't have to create a new temp table each time a person clicks on a page, just advance through it like a normal table.All this because MSSQL does not have an offset and limit call like MySQL. Although it would probably have to look through the entire DB each time as well.-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
|
|
|
|
|
|
Temp table vs getrow for paging large table