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

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?
JSW21Asked:
Who is Participating?
 
jagssiduralaConnect With a Mentor Commented:
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
 
jagssiduralaCommented:
Can you post your sql query?
0
 
JSW21Author Commented:

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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
JSW21Author Commented:
This are samples but they are the same in nature. just different table, db, etc.
They are also in the StorageProvider of subsonic.

0
 
JSW21Author Commented:
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
 
jagssiduralaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.