| Author |
 Topic Search  Topic Options
|
WebWiz-Bruce 
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
 Post Options
 Thanks(0)
 Quote  Reply
 Posted: 06 March 2006 at 2:50pm |
|
I've spent days on it so far, so if you could have a look at the query around line 303 of forum_topic.asp I would be very grateful.
From tests I have done adding, removing, and re-ordering, the SQL in the query the only thing that would speed it up is to have less joins, but then it's a question of getting the required data without the joins.
|
 |
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:55pm |
Hi Borg
I'll have a look and see what I can find. Just a simple silly question, do you have a test DB that you run against? If so, how many records etc, so that I can try and get a similar result set. If you could throw this Test DB my way, I would greatly appreciate it, as then I can compare apples to apples. If not, then no worry, I will generate some data to test against.
Thanks
DM
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 2:59pm |
|
Not really I just use a backup copy of the database from this site to test on as it has quite a bit of data in now.
The only thing I find that the more pages you have the topics subjects across the slower it gets, being around 0.2 to 0.3 process time.
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2006 at 3:28pm |
Hi Borg
Ok I will have a look for you, and see what I can get and come up with.
Thanks
DM
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 March 2006 at 6:30am |
Hi Borg
Sorry mate, but i only have bad news. I have run through the script again and again, and I am unable to find any other way to make this SQL run faster. Sorry mate, but I tried everything that I could think of to cut down on the joins to get it to play ball better, but to no avail.
On a really silly note, does the SQL time taken to run change when the number of pages is increased? Is this increase in time taken related to the SQL, or to the ASP code. The reason I ask, is that i had a page that did a lot of work, and had a bundle of loop statements in it to handle displays across multiple pages, and it ran like a dog. When I changed the code to hit the database a bit more for things like record counts etc, and simplified my multipage processing the page speed increased. Just something silly I know but stranger things have happened to me before.
Let me know what you think, or if you need any help on anything else. Sorry I was not able to speed up the SQL for you on this one, but I will continue to look for ways to help.
Thanks
DM
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 March 2006 at 1:08pm |
|
Many thanks for looking, I've probably spent around 8 hours on that query over the last 6 months, and another 4 hours reading a book on SQL performance tuning.
I was hoping by having another pair of eyes looking at it, you may have seen something I didn't.
I have done quite a few tests on the page and it is the SQL Query that uses the most processing time.
At least by de-normalising the database and using the new query it is much faster than in version 7 and uses allot less resources. I was just hoping to improve it further.
Because I am immediately using GetRows() to place the recordset in to an array the amount of time the rest of the code takes to process is so fast that the system is unable to return a processing time for it.
|
|
DeadMeat
Newbie
Joined: 24 January 2006
Status: Offline
Points: 32
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 March 2006 at 1:28pm |
Hi Borg
Yeah I know wht you mean, you sometimes spend so much time working on something, that you can't see the wood for the trees. Sorry I was not able to find something, but not too worry, I will continue to look at it, and see during my travels whether I can find something that will make it perform better and improove performance.
Thanks
DM
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 March 2006 at 2:02pm |
|
Thanks DeadMeat
|
|
