jakkku ma
asked on
MYSQL Fast retreival data from Database using c#
string query = string.Format("SELECT Month(P.Payment_date)Month
It takes time to retrieve data from DB and gave 300 Seconds in command timeout but still getting error
MySql.Data.MySqlClient.MyS
HResult=0x80004005
Message=Fatal error encountered during command execution.
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>Inner Exception 1:MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Please tell me how to fast retrieve data from database and displays it in web page.If it is monthly or yearly report takes time for calculations (sum,count) to retrieve data from DB .
It takes time to retrieve data from DB and gave 300 Seconds in command timeout but still getting error
MySql.Data.MySqlClient.MyS
HResult=0x80004005
Message=Fatal error encountered during command execution.
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>Inner Exception 1:MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Please tell me how to fast retrieve data from database and displays it in web page.If it is monthly or yearly report takes time for calculations (sum,count) to retrieve data from DB .
Try below query and see result:
string query = string.Format("SELECT Month(P.Payment_date) MonthName, COUNT(*)Volume, SUM(P.amount) Collection FROM carpark_db.payment P, carpark_db.transaction T WHERE P.transaction_id = T.transaction_id AND P.carpark_id = 'AMYDE5E27D5846D0B3F3A8BA4CA4C127' and year(payment_date) = '2017' GROUP BY MonthName ASC");ASKER
Got the output but takes 5-6 mins to retrieve data from DB and display in the grid view through web page.i want to display it fast retrieval from DB.IS there any way to fast access
You can do Indexing on the columns which you are using in your web page.
ASKER
all the columns have indexing and more than 3-5 lakhs records.
Retrieve the data (sum,count )from the payment table .Is there any way to store the sum and count in temp table for faster access and display it in web page report
Retrieve the data (sum,count )from the payment table .Is there any way to store the sum and count in temp table for faster access and display it in web page report
Kindly let me know the performance after modifying the payment_date column as explained in my earlier comment..
What is your version of MySQL?
ASKER
mysql version 1.2.17
ASKER
changed my columns like that still takes time in MySQL Query Browser, and same query given inside the C# code to fetch the data from DB and Display in to the web page .i want to display it in fast access
MySQL Server Version I want to know.
ASKER
5.0.24-community-nt
You can try with creating the Stored procedure.
Check whether you have indexes on Transaction_id and carpark id columns, if not try adding them to see whether it helps or not.
Okay, if you want to make the query complete faster try executing the query for 1 month and see how it performs so that we can think of alternatives..
Okay, if you want to make the query complete faster try executing the query for 1 month and see how it performs so that we can think of alternatives..
ASKER
All the columns have indexing and more than 3-5 lakhs records.Retrieve the data (sum,count takes time )from the payment table .Even For One month also it takes time to display minimum 3-4 mins.
Check Datatype of fields, use only used fields for the web page, follow foreign key concept (should be indexed)
ASKER
Giving input from the form
CarparkId = lstcpk.SelectedItem.Value;
gridmonthly.DataSource = "";
string str1 = TxtMonthpicker.Text;
DateTime datevalue = (Convert.ToDateTime(str1.T
String dy = datevalue.Day.ToString();
String mn = datevalue.Month.ToString()
String yy = datevalue.Year.ToString();
string query = string.Format("SELECT Day(P.Payment_date)Day,COU
please check ?
CarparkId = lstcpk.SelectedItem.Value;
gridmonthly.DataSource = "";
string str1 = TxtMonthpicker.Text;
DateTime datevalue = (Convert.ToDateTime(str1.T
String dy = datevalue.Day.ToString();
String mn = datevalue.Month.ToString()
String yy = datevalue.Year.ToString();
string query = string.Format("SELECT Day(P.Payment_date)Day,COU
please check ?
Use following query and see result
string query = string.Format("SELECT Day(P.Payment_date)Day,COUNT(*)Volume,SUM(P.amount)Collection FROM carpark_db.payment P INNER JOIN carpark_db.transaction T ON P.transaction_id = T.transaction_id WHERE P.carpark_id = '" + CarparkId + "' and month(Payment_date) = '" + mn + "'and year(payment_date) = '" + yy + "' GROUP BY Day ASC");ASKER
DELIMITER $$
DROP PROCEDURE IF EXISTS `carpark_db`.`SPMonthCalc`
CREATE PROCEDURE `carpark_db`.`SPMonthCalc`
BEGIN
SELECT Day(P.Payment_date)Day,COU
WHERE P.transaction_id = T.transaction_id AND P.carpark_id = carparkId
and month(Payment_date) = MONTHNAME('MonName') and year(payment_date) = EXTRACT(YEAR FROM 'YearName') GROUP BY Day ASC
END $$
getting error
DROP PROCEDURE IF EXISTS `carpark_db`.`SPMonthCalc`
CREATE PROCEDURE `carpark_db`.`SPMonthCalc`
BEGIN
SELECT Day(P.Payment_date)Day,COU
WHERE P.transaction_id = T.transaction_id AND P.carpark_id = carparkId
and month(Payment_date) = MONTHNAME('MonName') and year(payment_date) = EXTRACT(YEAR FROM 'YearName') GROUP BY Day ASC
END $$
getting error
What error are you getting?
ASKER
DELIMITER $$
DROP PROCEDURE IF EXISTS `carpark_db`.`SPMonthCalc`
CREATE PROCEDURE `carpark_db`.`SPMonthCalc`
BEGIN
SELECT Day(P.Payment_date)Day,COU
WHERE P.transaction_id = T.transaction_id AND P.carpark_id = carparkId
and month(Payment_date) = MONTHNAME('MonName') and year(payment_date) = EXTRACT(YEAR FROM 'YearName') GROUP BY Day ASC;
END $$
stored procedure create and called in c# but it s slow in reterieval.Please tell me how to store the stored procedure results int temp table in MYSQL and retreived for fast access
DROP PROCEDURE IF EXISTS `carpark_db`.`SPMonthCalc`
CREATE PROCEDURE `carpark_db`.`SPMonthCalc`
BEGIN
SELECT Day(P.Payment_date)Day,COU
WHERE P.transaction_id = T.transaction_id AND P.carpark_id = carparkId
and month(Payment_date) = MONTHNAME('MonName') and year(payment_date) = EXTRACT(YEAR FROM 'YearName') GROUP BY Day ASC;
END $$
stored procedure create and called in c# but it s slow in reterieval.Please tell me how to store the stored procedure results int temp table in MYSQL and retreived for fast access
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got the solution used temporary table to store the starting date and last date of every month selection .
Also try replacing "and year(payment_date) = '2017'" with
and payment_date >= '2017-01-01' and payment_date < '2018-01-01'
to make use of indexes if any on payment_date column..