Solved

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

Posted on 2011-09-26
6
428 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 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

930 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now