Community Server

I have uploaded a new version to my server

http://downloads.computertechman.net/sqlpool_4.0.zip

I also added a zip file that contains the sql to create the keys and indexes on the database.

 http://downloads.computertechman.net/createindexes.zip 

Trent

NOTE:  The following pertains to the Survivor (side) pool only.  We do not use standard weekly pool. 

So after much testing / trial and error, we are bailing on the statistics page.  As for standings, we are just going to manually export from database and post pdf around 1:30 ish on Sundays.  Not ideal, but we are not taking any chances this year.  Last year was a mess to say the least;  here are problems and solutions that might help you until we can develop a query for survivor standings page to support a large user base (Our count now is at 297, projected to break 300 users before kickoff!!!!):

Problem:

- Users not able to make picks when large number of users are trying to pick at the same time

Solution:

- MS SQL of course!!!  Thanks to mrpolaris, todhend, and everyone on this thread, we are now running the MS SQL version.

Problem: 

- Standings page does not load due to size of user base and number of queries

Solution:

We encountered a problem after users make pick.  The standard entry page redirects users to the "standings" page once user submits pick.  This is bad news for our pool, as the standings page does not work 99% of the time. 

As a work around, we created a "confirmation" page for users to go to instead of the standings page.  This page is just a static page that tells user he or she has successfully submitted pick. 

- User clicks "entry form"
- User makes pick and clicks "update"
- Pick is logged in database, then user is directed to static confirmation page stating pick was successful

 Our next goal is to make a page where user can click and view their current pick for the week.  I wrote working script to output username, current week, but need help pulling the actual pick from the data base table for the current week to display.  Yes, I am a .net newbie, so please do not laugh. 

Problem:

Since standings page does not load, no one knows anyone's picks after kickoff

Solution:

Very unrefined, but it works.  After all picks are locked, we load up database in MS SQL Server Management Studio (free program).  Once in, we export all rows from the table "SidePicks" in CSV format.  Using OpenOffice.org software, we sort data and include title, etc.  Once data is good to go, we export to pdf file.  In place of the standings page, we created a new page to where we post pdf of all picks.  Each week, we will continue posting pdf's, so all weeks will be included with downloadable links in an unordered list. 

Now if we can get a working standings page, the pdf method will not be needed.  This is where I am stumped, especially given we have around 300 users.   The current standings query is simply too large to work with this many users.  I am thinking if we could create a smaller query, maybe to just pull for the current week instead of every single week, we may be able to have something the works. 

Sorry to let you down progressive, hopefully these ghetto fabulous solutions work in the meantime!

 not sure if this has already been looked at but when auto updating scores and send email is checked I get this error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'true'.

/nflpicks/includes/email.asp, line 20

hortonia

Thanks for the update.  I did not respond earlier, because I knew we were not going to make any modifications before the first week of games.  We have 144 people in our pool and have seen very similiar issues that you have outlined.

Although our standings page is probably around the 50% range, I really like your idea of a confirmation page because the most important item for the end user is getting a quick response that their pick(s) were registered.  Can you please point me in the direction to make such a change so that they are directed to a static confirmation page -- once they know that their pick is registered, I can then have a link to allow them to take a look at the statnding (even if it does load slow).

I do have another question for you.  I noticed over the weekend  mrpolaris uploaded to his server neew files.  Have you tried using these and if so did they show any imporvement.  Like I mentioned, I did not want to mess around in week 1, but would be willing to try to index tables and make updates to the files if it improved performance.

Thanks again for the comment and look forward to your answers ...

progressive

todhend:

 not sure if this has already been looked at but when auto updating scores and send email is checked I get this error

Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'true'.

/nflpicks/includes/email.asp, line 20

todhend, funny you should mention this error screen.  I encountered it two days from sunday kickoff and had yet another panic moment.  Fortunately, I was able to fix and am more than happy to share solution.  Also, it feels good to be able to do you a favor this time!  The following will fix the issue:

- Boolean expressions in MS SQL are read through 1's and 0's.  1 = true, 0=false.   The previous file had "false" in various places, which was causing the error

- Load up source code of "updateScores.asp"

- Find and Replace for the following:  find "false", replace with "0" for entire document

- Save and upload to server

-  That's it.  Seriously!

Thanks again for your help with conversion to MS SQL, we would be nowhere without the help of the community.  This is why I love being a dev!

progressive:  

Step one, cut a hole in the box.  Step two, put your.....wait, we are talking about the football pool.  scratch that.

1)  I don't remember the exact page I used, but basically pick a page from the website.  Any will do.  Remove all the commands, scripts, etc. so all you are left with is a bare bones version of the site:  the header, body and footer. 

2)  Create a <p>YOUR PICK IS CONFIRMED!</p> message on said page. 

3)  Name page something like "confirmation.asp" and upload to server. 

4)  Go to page "sideEntry.asp" and edit following line of code, changing "sideStandings.asp" to "confirmation.asp" to match your static confirmation page:

line 290: 

                'Updates complete, redirect to the side pool standings page.
                Response.Redirect("confirmation.asp")
            end if
        end if
    end if

5)  If all went well, you have yourself a static confirmation page.  

6)  Pick beverage of choice. I recommend Lagunita's "Undercover Shutdown ale" or New Holland's "Dragon's Milk."

7)  Enjoy said beverage of choice. 

NOTE:  I have been watching football all day and am a little bit tipsy.....

If you want my example page, shoot me a message and I'll email it to you.  

Love to the community!!!!!!

progressive, just realized I only answered your first question.  Indexes helped correct statistics page, but standings page is still a train wreck.   Indexes make for quicker queries and thus less crashes. 

mrpolaris's updates are awesome, but will likely not fix all the woes with having a large user base.  We have 326 total this year, which equals crashes galore.  The programming of this site is simply not designed for this many users.  I believe Mike Hall stated himself that the asp football pool is meant for a user base of no more than 50. 

To avoid headaches, we are doing a lot of manual posting of data.  Once more users get eliminated from the survivor pool, we will delete their accounts and thus be able to use the standard pages.  Until then, it's static html and pdfs!

hortonia

Just wanted to let you know, I took your suggestion for the static pick confirmation page and it seems to be functioning as expected.

I think I have gotten spoiled in the fact that Mike's app worked flawless in the first year and then to no fault of his -- our pool became more popular and then resulted in these types of issues --

I am going to tinker on this end to see what if anything can be done regarding the standings page and will let you know ---

Thanks for everying.

progressive 

I have been running some profile tools again the SQL statements. I added some indexes and keys and that helped but the Stats pages will make like 50 sql calls to gather the data..

I will be looking into it futher and aslo thinking about migrating the sql to be stored sql procs. 

 Mrpolaris