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-05-28 : 03:31:58
|
| I have a situation where I have a 2 tables one that contains a list of items called "images" and one that contains a list of users called users.There is a field in the users table called "Donor". I would like to Display all images in the image table associated with users that have the Donor bit set in the user table. At present I am using a LEFT JOIN between the 2 tables on the user_ID field as they both have them in common and then WHERE (Donor = 1) to get the all the donors. I am getting the data I need however it takes longer and longer to page through the table the further I get into it. When I get to the last page it takes upward of about 7 seconds to retrieve the data.There are about 340,000 images of which only approx 96,000 are tied to donors.I suppose that I could make a temp table once of all the image_id that are associated to donors and step through the 96,000 instead of paging through the full 340,000 by the time I get to the last page. Is this efficient if there are hundreds of users per minute accessing the site? Or should I perhaps make a real table of image_ids and update it periodically as donors and their images are added and removed?I am using a stored procedure to return the rows I need.Looking for ideas on this one. Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-05-28 : 04:40:23
|
| While waiting for help, I generated a table containing the image_ID and User_ID. I then used an INNER JOIN of this table to the image table to limit the number of records to 96,000 instead of 340,000 and got rid of the WHERE (Donor = 1) portion as that has already been taken care of by the INNER JOIN. The time went from between 7 and 8 seconds to return the last page to between 2 and 3 seconds. A pretty good improvement although still seems a bit slow but is probably usable. My question now is that to create the table I used this Select Into statement in the Query Analyzer.SELECT UF_rt_images.image_ID, UF_rt_images.user_ID INTO UF2_Donor_Images_1 FROM (UF_rt_Images INNER JOIN UF2_rt_Users ON (UF_rt_Images.user_id = UF2_rt_users.user_id)) WHERE ((UF_rt_Images.Image_Active = 1) AND (UF_rt_Images.Image_Section LIKE 'critique') AND (UF2_rt_users.user_Donor = 1))The question I have now is there a way to make this into a stored procedure where it would create the table "UF2_Donor_Images_1" then once the table is generated, drop the live table "UF2_Donor_Images" and rename the newly created table "UF2_Donor_Images_1" to "UF2_Donor_Images"? Then I can run this procedure from a trigger when a new Donor is added/deleted or a donor adds or deletes an image.Thanks for any help here, I am still pretty new at stored procedures and any help would be appreciated.Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-05-28 : 14:27:15
|
| These articles might help...[url]http://sqlteam.com/searchresults.asp?SearchTerms=paging&SUBMITs1=Search[/url] |
|
|
|
|
|
|
|
|
A more efficient query approach needed