Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).
Course Of The Month
3 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Optimizing MySQL query execution time with C# ASP.NET
Posted on 2008-06-24
I'm doing a website that hosts surveys. For some reason, the execution time of the insert/update to the mySQL database for a single survey response is nearly a half a second. This seems like an aweful long time for a small query to execute.
Below is the entire query of entering a single survey page response. I thought keeping a running total of the number of responses in the row that stores the answer would save a lot of time when it was time to generate reports. However this seems to double the execution time.
Even without the totals=totals+1 lines, the execution time is still around .265 seconds. That would be fine if only one person was taking a survey at a time, but my goal is to host lots of surveys.
Elapsed Time: 0 seconds, or 453.125 milliseconds per Survey save.
Last SQL Statement:
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=1;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=6;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=7;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=17;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=18;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=23;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=28;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=30;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=34;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=41;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=47;
UPDATE surveys SET lastactivity_date='2008-06
This is the method that generates and executes the statement:
public void StoreResults(int iPid)
{
sSQL="";
string dateFormat = "yyyy-MM-dd HH:mm:ss";
string surveyDateTime = DateTime.Now.ToString(date
string sSurveyID = iSurveyID.ToString();
string sPid = iPid.ToString();
for (int iQuestion = 0; iQuestion < myQuestions.Length; iQuestion++)
{
if (myQuestions[iQuestion].An
{
for (int iAnswer = 0; iAnswer < myQuestions[iQuestion].Ans
{
if (myQuestions[iQuestion].An
{
//insert results table row into database
sSQL += "INSERT INTO results(sid,qid,aid,abody,
sSQL += "VALUES (" + sSurveyID + "," + myQuestions[iQuestion].Que
sSQL += "," + myQuestions[iQuestion].Ans
sSQL += SQLFix(myQuestions[iQuesti
sSQL += sPid + ",'" + surveyDateTime + "');";
sSQL += "UPDATE answers SET totals=totals+1 WHERE aid=";
sSQL += myQuestions[iQuestion].Ans
}
}
}
}
//update survey lastactivity_date and totals in database
sSQL += "UPDATE surveys SET lastactivity_date='" + surveyDateTime;
sSQL += "',totals=totals+1 WHERE sid=" + iSurveyID.ToString() + ";";
dsPollsDatabase.UpdateComm
dsPollsDatabase.Update();
}
Below is the entire query of entering a single survey page response. I thought keeping a running total of the number of responses in the row that stores the answer would save a lot of time when it was time to generate reports. However this seems to double the execution time.
Even without the totals=totals+1 lines, the execution time is still around .265 seconds. That would be fine if only one person was taking a survey at a time, but my goal is to host lots of surveys.
Elapsed Time: 0 seconds, or 453.125 milliseconds per Survey save.
Last SQL Statement:
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=1;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=6;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=7;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=17;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=18;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=23;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=28;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=30;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=34;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=41;
INSERT INTO results(sid,qid,aid,abody,
UPDATE answers SET totals=totals+1 WHERE aid=47;
UPDATE surveys SET lastactivity_date='2008-06
This is the method that generates and executes the statement:
public void StoreResults(int iPid)
{
sSQL="";
string dateFormat = "yyyy-MM-dd HH:mm:ss";
string surveyDateTime = DateTime.Now.ToString(date
string sSurveyID = iSurveyID.ToString();
string sPid = iPid.ToString();
for (int iQuestion = 0; iQuestion < myQuestions.Length; iQuestion++)
{
if (myQuestions[iQuestion].An
{
for (int iAnswer = 0; iAnswer < myQuestions[iQuestion].Ans
{
if (myQuestions[iQuestion].An
{
//insert results table row into database
sSQL += "INSERT INTO results(sid,qid,aid,abody,
sSQL += "VALUES (" + sSurveyID + "," + myQuestions[iQuestion].Que
sSQL += "," + myQuestions[iQuestion].Ans
sSQL += SQLFix(myQuestions[iQuesti
sSQL += sPid + ",'" + surveyDateTime + "');";
sSQL += "UPDATE answers SET totals=totals+1 WHERE aid=";
sSQL += myQuestions[iQuestion].Ans
}
}
}
}
//update survey lastactivity_date and totals in database
sSQL += "UPDATE surveys SET lastactivity_date='" + surveyDateTime;
sSQL += "',totals=totals+1 WHERE sid=" + iSurveyID.ToString() + ";";
dsPollsDatabase.UpdateComm
dsPollsDatabase.Update();
}
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
4
11 Comments
How often are reports generated? If insert is more frequent than report generation, actually you don't need to keep on adding the total as you can just compute the total when you are about to generate report. You can do that by using "select count(*)" based on certain "where" condition.
Yeah. I may have to do that.
I have a logging application that keeps track of my daily web hits. Every day I get around 30,000 hits, but most are from search engines going through my site (each word in a dictionary has it's own web page, and there are 200,000 words). It takes a long time to generate the totals for a whole month, which charts how many unique ip addresses visit per day. Since that's my experience with reporting, I figured it would save a lot of time to keep a running tally in advance.
Even if I exclude those statements that set totals=totals+1 (which cuts the time in half), the rest of the inserts still take a quarter of a second. I certainly want to be able to support more than 4 users/second on my web app.
I have a logging application that keeps track of my daily web hits. Every day I get around 30,000 hits, but most are from search engines going through my site (each word in a dictionary has it's own web page, and there are 200,000 words). It takes a long time to generate the totals for a whole month, which charts how many unique ip addresses visit per day. Since that's my experience with reporting, I figured it would save a lot of time to keep a running tally in advance.
Even if I exclude those statements that set totals=totals+1 (which cuts the time in half), the rest of the inserts still take a quarter of a second. I certainly want to be able to support more than 4 users/second on my web app.
Any index in results table? What are the data type of those columns that you index? Index causes insert to be smaller, especially with bigger data type
CREATE TABLE `answers` (
`aid` int(11) NOT NULL auto_increment,
`sid` int(11) NOT NULL default '0',
`qid` int(11) NOT NULL default '0',
`anumber` int(11) NOT NULL default '0',
`abody` text,
`atype` int(11) NOT NULL,
`width` smallint(6) NOT NULL default '0',
`height` smallint(6) NOT NULL default '0',
`totals` int(11) NOT NULL default '0',
PRIMARY KEY (`aid`),
KEY `questionid` (`qid`),
KEY `surveyid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2749 ;
CREATE TABLE `surveys` (
`sid` int(11) NOT NULL auto_increment,
`guid` varchar(16) NOT NULL COMMENT 'This is the survey unique identifier',
`title` varchar(128) NOT NULL,
`ownerid` int(11) NOT NULL,
`creation_date` datetime NOT NULL,
`modified_date` datetime NOT NULL,
`open_date` datetime default NULL,
`close_date` datetime default NULL,
`lastactivity_date` datetime default NULL,
`active` tinyint(1) NOT NULL default '1',
`shared` tinyint(1) NOT NULL default '0',
`questions` smallint(6) NOT NULL default '0',
`description` text,
`totals` int(11) NOT NULL default '0',
PRIMARY KEY (`sid`),
KEY `GUID` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;
`aid` int(11) NOT NULL auto_increment,
`sid` int(11) NOT NULL default '0',
`qid` int(11) NOT NULL default '0',
`anumber` int(11) NOT NULL default '0',
`abody` text,
`atype` int(11) NOT NULL,
`width` smallint(6) NOT NULL default '0',
`height` smallint(6) NOT NULL default '0',
`totals` int(11) NOT NULL default '0',
PRIMARY KEY (`aid`),
KEY `questionid` (`qid`),
KEY `surveyid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2749 ;
CREATE TABLE `surveys` (
`sid` int(11) NOT NULL auto_increment,
`guid` varchar(16) NOT NULL COMMENT 'This is the survey unique identifier',
`title` varchar(128) NOT NULL,
`ownerid` int(11) NOT NULL,
`creation_date` datetime NOT NULL,
`modified_date` datetime NOT NULL,
`open_date` datetime default NULL,
`close_date` datetime default NULL,
`lastactivity_date` datetime default NULL,
`active` tinyint(1) NOT NULL default '1',
`shared` tinyint(1) NOT NULL default '0',
`questions` smallint(6) NOT NULL default '0',
`description` text,
`totals` int(11) NOT NULL default '0',
PRIMARY KEY (`sid`),
KEY `GUID` (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;
CREATE TABLE `results` (
`rid` int(11) NOT NULL auto_increment,
`sid` int(11) NOT NULL default '0',
`qid` int(11) default NULL,
`aid` int(11) NOT NULL default '0',
`abody` text,
`pid` int(11) NOT NULL default '0',
`date_taken` datetime NOT NULL,
PRIMARY KEY (`rid`),
KEY `pid` (`pid`),
KEY `qid` (`qid`),
KEY `aid` (`aid`),
KEY `sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18947 ;
`rid` int(11) NOT NULL auto_increment,
`sid` int(11) NOT NULL default '0',
`qid` int(11) default NULL,
`aid` int(11) NOT NULL default '0',
`abody` text,
`pid` int(11) NOT NULL default '0',
`date_taken` datetime NOT NULL,
PRIMARY KEY (`rid`),
KEY `pid` (`pid`),
KEY `qid` (`qid`),
KEY `aid` (`aid`),
KEY `sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18947 ;
pid is participant id. we want to be able to lookup responses by participant.
qid is question id. We need to look up answers based on questionid.
aid is answer id. We used to need that to look up total number of a given answer, but I'm not using it now since I've got the totals column.
sid is surveyid. We need to look up based on surveyid.
qid is question id. We need to look up answers based on questionid.
aid is answer id. We used to need that to look up total number of a given answer, but I'm not using it now since I've got the totals column.
sid is surveyid. We need to look up based on surveyid.
Well, the only thing i can think of is surveys.guid, as your survey table gets bigger, it sure will slow down writes to that table.
We only write the guid to the surveys table once. That's just so when someone's taking a survey we are not puting the actual surveyid in the request URL.
Well, after some further testing, it appears that it's a problem with my test machine.
On the godaddy server that's hosting the future website, it only takes around 17ms for each survey save. So I've got some work to do on my development machine.
On the godaddy server that's hosting the future website, it only takes around 17ms for each survey save. So I've got some work to do on my development machine.
I would recommend switching to the multiple insert format and then using the SELECT COUNT(*) methods.
With a multiple insert, the INDEX is only rebuilt once for the INSERT rather than one time for each row with your current code.
Sure, your current server is running a small data set quickly -- but just wait until the data set gets bigger. The time to rebuild the INDEXes can become a meaningful component.
With a multiple insert, the INDEX is only rebuilt once for the INSERT rather than one time for each row with your current code.
Sure, your current server is running a small data set quickly -- but just wait until the data set gets bigger. The time to rebuild the INDEXes can become a meaningful component.
Featured Post
Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Course of the Month3 days, 19 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.