?
Solved

ASHX page/Web page pulling data from mysql... slow response from time to time.

Posted on 2011-09-26
6
Medium Priority
?
486 Views
Last Modified: 2012-08-13
Hello
I have a basic .ashx page with comma separate data display around 5-6000 rows.
User will queries with different parameter from the URL field.

My tool are
IIS, MySQL, subSonic, and some vb code behide to calculate data.

I am encounter unknown issue where i query the URL some time it is fast, some times take 1 -2 min. On the very same data.

I disable cache due to the type of data keep changing.

Where could be the first culprit or setting that needed to be done?
0
Comment
Question by:JSW21
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:jagssidurala
ID: 36600896
Can you post your sql query?
0
 

Author Comment

by:JSW21
ID: 36712194

using (var db = new DbManager(_dictionary_of_dbName[dbName]))
                {
                    db.SetCommand(
                        string.Format(
                            @"SELECT * FROM `{0}`
                        WHERE `OATradeDateTime` <= {1} ORDER BY `OATradeDateTime` DESC LIMIT {2}",
                            tableName, endDate, iLimit));
                    iData = db.ExecuteList<DataTable_d>();
                    iData.Reverse();
                    return iData;
                }

Open in new window

0
 
LVL 12

Accepted Solution

by:
jagssidurala earned 2000 total points
ID: 36712226
Changed code highlighted below

using (var db = new DbManager(_dictionary_of_dbName[dbName]))
                {
                    db.SetCommand(
                        string.Format(
                            @"SELECT Column1, Column2, etc... FROM `{0}` WITH(NOLOCK)
                        WHERE `OATradeDateTime` <= {1} ORDER BY `OATradeDateTime` DESC LIMIT {2}",
                            tableName, endDate, iLimit));
                    iData = db.ExecuteList<DataTable_d>();
                    iData.Reverse();
                    return iData;
                }


retrieve only required columns instead of getting all the columns (using *)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:JSW21
ID: 36712232
This are samples but they are the same in nature. just different table, db, etc.
They are also in the StorageProvider of subsonic.

0
 

Author Comment

by:JSW21
ID: 36714947
WITH NOLOCK

Interesting, I guess i have another cheap question, what happened

If i have another thread update MYSQL and another thread select data similar to above no the same table... will it be concurrent operation. Do i need to do something similar to select statement With Nolock?
0
 
LVL 12

Expert Comment

by:jagssidurala
ID: 36718609
We will get the committed records(saved into the DB) if we use no lock.means if any updates are happening at the time retrieval server will give you the records irrepective of updates i.e., with out any locking on the table.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync server 2013 or Skype for business Backup Service Error ID 4049 – After File Share Migration
If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question