EasyCFM.COM ColdFusion Forums / MySQL Related Issues / MySQL performance under my design

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: MySQL performance under my design

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Orlok
04-29-2008 @ 3:49 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 3
Joined: Apr 2008

Hello All, I'm happy to be here, a n00b on the forum.

I'm having some performance issue w/ a MySQL database that I designed to handle a survey application. I have a table dedicated to storing answers and it's currently at 25,000 records. My users have been reporting timeouts on some cfquery tags so I need to figure out how to either, a. improve my SQL statements, b. improve my table architecture, or c. smile and pretend everything's ok.

Below is what I believe to be one of the offending statements (There are 4 similar):

<!--- Get current AssessmentID --->
     <cfquery name="qSelfRegID" datasource="#sDSN#">
          SELECT           AssessmentID, DateTimeStamp
          FROM          assessment, seauser
          WHERE          AssessmentName = <cfqueryparam
                value="SelfReg"
                cfsqltype="CF_SQL_VARCHAR"
                maxlength="10">
          ORDER BY     DateTimeStamp
          DESC LIMIT     0,1
     </cfquery>

Using this variable, the script then goes on to a series of INSERT INTO statements to populate the answers for this particular survey (there are 4 similar).

As a temporary solution, I've migrated my data to another table and am using a fresh table for the app to write to. I could probably continue in this direction but I'm not sure what the wisest way to go about this would be.

At this point, my biggest priority is scalability. I need to be able to store much more than 20,000 records without performance issues causing errors. This survey is currently only run at one location, in the future, the intention is to run the survey from n sites so I really need to figure out an architecture that will meet that requirement.

Any help, advice, or clues are greatly appreciated. I am not a programmer by training, more by default so I realize I have lot's to learn.

Thank you


Lossed
05-01-2008 @ 12:14 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Welcome aboard, Orlok.

MySQL scales very well so you have nothing to worry about on that front.

What is the relationship between the two tables you are using? It may be a combo of your a and b options that's required Smile



Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

Scarecrow
05-01-2008 @ 12:30 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

a. You will need to post all you sql statements

b. You will need to post your architecture

c. Put index finger of each hand to corner of mouth and push up  Smile

Ken

www.krcaldwell.com

Webmaster
05-01-2008 @ 3:55 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

In this query:

SELECT           AssessmentID, DateTimeStamp
          FROM          assessment, seauser
          WHERE          AssessmentName = <cfqueryparam
                value="SelfReg"
                cfsqltype="CF_SQL_VARCHAR"
                maxlength="10">
          ORDER BY     DateTimeStamp
          DESC LIMIT     0,1


I see where you define what you want to filter by; however I dont see what ties the two tables togehter... something like this in the WHERE:

WHERE assessment.assessmentid = seauser.assessmentid
  and AssessmentName = <cfqueryparam
                value="SelfReg"
                cfsqltype="CF_SQL_VARCHAR"
                maxlength="10">

Does that make sense?

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

Orlok
05-01-2008 @ 4:55 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 3
Joined: Apr 2008

Thanks for the replies.

Here's my initial ERD before I created new storage tables:


And my current architecture with storage tables:
http://dev.dbacademy.org/examples/seaerd2.png

The surveys are stored in the Assessment and Answers tables. There are 4 surveys identified in the AssessmentName as 'SelfReg','Ase', 'Goals', and 'Hope.' The code below shows the sequence of queries used to populate data from one of those surveys. I'm using the same structure for all four surveys and it should be said that this all runs on the same processing page. The reason I'm pasting in just a segment of my code at this point is because the entire processing page is around 500 lines. Let me know if you'd like to see all of it and what the best way I could deliver it would be., ie, I could link to zip, or convert it to a .txt or I could paste the whole enchilada.


<!--- Create record for Efficacy for Self Regulation (SelfReg) assessment --->     
     <!--- Create new assessment object --->
     <cfquery datasource="#sDSN#">
          INSERT INTO Assessment(AssessmentID, StudentID_FK, AssessmentName, DateTimeStamp)
          VALUES           ('#CreateUUID()#', '#FORM.StudentID#', 'SelfReg', #CreateODBCDateTime(Now())#)
     </cfquery>
     
     <!--- Get current AssessmentID --->
     <cfquery name="qSelfRegID" datasource="#sDSN#">
          SELECT           AssessmentID, DateTimeStamp
          FROM          assessment, seauser
          WHERE          AssessmentName = <cfqueryparam
                                                             value="SelfReg"
                                                             cfsqltype="CF_SQL_VARCHAR"
                                                             maxlength="10">
          ORDER BY     DateTimeStamp
          DESC LIMIT     0,1
     </cfquery>

<!--- If exists, create Answers object and insert Answer result --->
<cfif IsDefined ("FORM.SelfReg1")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','1', '#SelfReg1#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg2")>
     <cfquery datasource="#sDSN#" result="result">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES           ('#CreateUUID()#',  '#qSelfRegID.AssessmentID#', '2', '#SelfReg2#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg3")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','3', '#SelfReg3#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg4")>     
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','4', '#SelfReg4#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg5")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','5', '#SelfReg5#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg6")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','6', '#SelfReg6#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg7")>     
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','7', '#SelfReg7#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg8")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','8', '#SelfReg8#')
     </cfquery>
</cfif>
     
<cfif IsDefined("FORM.SelfReg9")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','9', '#SelfReg9#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg10")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','10', '#SelfReg10#')
     </cfquery>
</cfif>

<cfif IsDefined("FORM.SelfReg11")>
     <cfquery datasource="#sDSN#">
          INSERT INTO Answers(AnswersID, AssessmentID_FK, QuestionNumber, Answer)
          VALUES          ('#CreateUUID()#', '#qSelfRegID.AssessmentID#','11', '#SelfReg11#')
     </cfquery>     
</cfif>          
     


Anyways, thanks in advance and I usually do a good job of keeping that frown upside-down. Smile

Orlok
05-02-2008 @ 2:06 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 3
Joined: Apr 2008

Actually, I don't have a join on the two tables in this query. I just realized that I see no reason I'm referencing the seauser table in this query. This may be a relic of something earlier in the application's development.

Thank you for noticing this, I guess I got blind to my own code.

Would this create a performance issue?


Website Designed and Developed by Pablo Varando.