Solved

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

Posted on 2011-09-26
6
460 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
[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
  • 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 500 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 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