| Author |
 Topic  |
|
|
bitwise2000
Starting Member
38 Posts |
 Posted - 24 April 2006 : 09:38:58
|
A month ago I converted Access to MSSQL using the procedure described elsewhere in this forum. Everything works fine, response times are good, with one exception.
I have the Photo Album mod installed and one particular query is taking a very long time to complete. Here it is:
SELECT * FROM FORUM_ALBUM INNER JOIN FORUM_ALBUM_USERS
ON FORUM_ALBUM.Member_id = FORUM_ALBUM_USERS.Member_id WHERE
FORUM_ALBUM.Photo_Status = 1 Order by FORUM_ALBUM.Photo_ID DESC
On Access this took about a second, on MSSQL it's taking 20+ seconds. I have about 8000 records in the Album table and 2500 records in the Users table.
Anyone familiar with this mod? Do I need some kind of index? How do I go about that ...?
Many Thanks |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 April 2006 : 10:20:05
|
Probably you need some indexes... Are there indexes on ALBUM.Member and FORUM_ALBUM_USERS.Member_id? I'd add one for each of these columns, if there aren't. I'd also check the FORUM_ALBUM.Photo_ID column and would add another index to it.
Check if this helps. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
bitwise2000
Starting Member
38 Posts |
Posted - 24 April 2006 : 10:29:14
|
| I have only connection access to the DB, no access to management tools. Can I determine/alter/add indexes programmatically? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
bitwise2000
Starting Member
38 Posts |
Posted - 24 April 2006 : 11:17:15
|
| Thanks for the help... Are there indexes on the base tables? Is there a select statement I can use to view indexes? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 April 2006 : 11:32:22
|
This is the SQL to create the indexes. Now you need an app to execute them, or a .asp script. Someone has posted before an online SQL Server management tool. Try a search to see if you can find it and use it to run the SQL below.
CREATE INDEX FA_MID ON FORUM_ALBUM(Member_ID);
CREATE INDEX FAU_MID On FORUM_ALBUM_USERS(Member_ID);
CREATE INDEX FA_PID ON FORUM_ALBUM(Photo_id);
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
bitwise2000
Starting Member
38 Posts |
Posted - 09 May 2006 : 13:23:19
|
Just to follow up on this... The issue turned out not to be the query itself, but the getrows retrieval of the recordset. The photo album mod pages the display after getting a recordset with the entire contents of the album table. Every time the display page is advanced, the query runs. That's horribly inefficient, but especially so when that query returns 8000 records. So I modified the code to do pseudo paging of the query itself. I say pseudo because MSSQL does not have LIMIT like MySQL, so you have to be creative. Thanks for the help, as always.
This also happened to be the cause of a huge amount of traffic between the web server and the SQL server. With thousands of photo album page views every day, that big recordset was really hammering the network. |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 09 May 2006 : 13:30:06
|
quote:
Originally posted by bitwise2000
MSSQL does not have LIMIT like MySQL
Just out of curiosity, did you try anything with the "TOP" keyword? As I understand it its the functional equivalent of "LIMIT"... |
|
|
| |
Topic |
|
Topic Locked
