SideStandings page extremely slow

[andygb4]

Hey Everyone,

I now have over 50 users that placed their Week 1 pick in my survivor pool, and when I load the Side Standings page, it takes between 35-45 seconds to load.

Does anyone else have many users and have trouble with loading that page?

The way the SidePicks table is set up seems like there will be 1 record for each User x Pick, so if the page is already slow at 50+ picks, imagine when we enter week 10 or something.
Also, the sidePicks table doesn't have a Primary Key, so when the page is searching for everyone's Picks, it has to search for Usernames and Weeks.
I even tried adding Indexes on those 2 columns, but it didn't seem to help nearly as much as I needed it to.

Last season, I was still using Access, and the page would literally crash if too many people were on it at the same time. That happened quite often, especially on Sundays!!
This season, I converted to SQL Server, which is working well, but the page is still taking ridiculously long to load

So if anyone has a modification that they made to speed up the process, or maybe just has an idea I can try to quicken all the database queries, I'm all ears!

[andygb4]

I've been making some changes to the page for the last 2 days.
I noticed that the code could be a bit more efficient, so I'm trying to cut down on the number of SQL queries.
It's a lot of work, but so far it seems like it's helping.
We'll see what it's like when I'm done.

[andygb4]

Yes!! Fixed it!

With about 300 players in the pool, the standings page was taking way over 60 seconds to load!
But I changed some code, so instead of querying the database for each player, it would only query it once,
get all the info, store them in arrays, and go pick up wtv info u need for each player from the arrays.

And this brought the load time from 60+ seconds to 2 seconds. what a difference!

So if ever anybody would like a more detail explanation of what I did, just let me know!

[philwojo]

Thanks for posting, I think a lot of people would be interested in your changed code and probably even more so in the SQL version of the entire pool if you have that available.

Phil

[PAUDASH]

I second this....

My Survivor pool has over 300 people and its is painfully slow to parse the standings page...

Would love a copy of your modified code to speed it up... Or as Phil said a copy of SQL setup...

I've made some mods to my ASP football pool over the years as well...

PM me and I'd be happy to share...

Integrated Apple Touch Icons that change depending on the skin you have set...
Registration lockout based on date... (Closes pool to new registrations after set date and time)
Predictive type on Username entry (Using apple devices is painfully slow to pick your name out of 300 names)

[andygb4]

Hey PAUDASH, I replied to your PM, hope that helps

Ive made some other changes to the page like using mini logos instead of abbreviations.
Also added a red box when the pick is incorrect.

I even added big background images to match a user's "favorite" team.

On the login page, I also removed the dropdown list, and put a regular textbox so players can just type in their username.

Anyways, here's a Zip file of my entire website. I used the SQL version files that philwojo originally put up.
But I had to make many modifications to some of the database queries, and bit of other code.

Here's the link to download it: https://www.dropbox.com/s/luzab06m3zouo ... r.zip?dl=0

[andygb4]

I forgot to mention that I stripped down the menu to only show what I needed, like the Survivor pool.

Just a heads up if any of you are using the other pool modes as well!