Home > c# > C# / SQL Process Feels Inefficient and Slow

C# / SQL Process Feels Inefficient and Slow

June 12Hits:7
Advertisement

I am calculating the moving average for 5,534,446 rows in one table. I am using C# as my language and MySQL as the database. Below is the code I am using to gather and calculate an exponential moving average for different sets of days. The program is working perfectly but it is taking forever to calculate the values one at a time. Since I am new to programming, I figure this could be three times more efficient. What can I do to improve this program's speed?

private void CalculateMovingAverage()         {             l.CreateRunningEntry(3, "CalculateMovingAverage", "Beginning to calulate exponential moving averages for companies.");             conn.ConnectionString = r.getMysqlConnection();              //This is the list for the type of exponential averages we want to calculate             decimal[] movingDays = { 5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 100, 200 };              //Go through each type of day moving average so that we calculate for each one             foreach (decimal avgDayLimit in movingDays)             {                 //We'll go ahead and calculate the smoothing constant for each moving average type                 decimal multiplier = CalcEMAMultiplier(avgDayLimit);                  //Run a query to get all of the companies with the required number of records in the hitorical_data table                 List<string> symbolList = CompaniesWithHistoricalDays(conn, avgDayLimit);                  //For each symbol, we need to make sure we have a simple moving average to start out with                 foreach (string companySymbol in symbolList)                 {                     //Check if there are any existing records in the moving average table                     bool maExists = DoesMovingAverageExist(conn, avgDayLimit, companySymbol);                      //We will need to calculate SMA if there are no records in moving average table                     if (maExists == false)                         InsertSMA(conn, avgDayLimit, companySymbol);                      //Obtain a list of companies where a date exists in historical_datathat does not exist in moving average                     DateTime maLastDate = LastMovingAverageDate(conn, avgDayLimit, companySymbol);                      //Pull all of the historic dates and historic closings from dates greater than the one pulled from teh database in the last process                     List<HistoricalClosing> histDateAndClose = GetHistoricalDateAndClosings(conn, companySymbol, maLastDate);                      //Insert the calculated EMA into the moving average table                     foreach (HistoricalClosing close in histDateAndClose)                         GatherEMAComponents(conn, companySymbol, close.historicDate, close.historicClose, multiplier, avgDayLimit);                 }             }         }      public decimal CalcEMAMultiplier(decimal emaDays)     {         decimal emaMultiplier = 2m / (emaDays + 1m);          return emaMultiplier;     }  public List<string> CompaniesWithHistoricalDays(MySqlConnection conn, decimal avgDayLimit) {     conn.Open();     l.CreateRunningEntry(3, "CalculateMovingAverage", "Pulling all companies that have maDays = " + avgDayLimit);     List<string> symbolList = new List<string>();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"select symbol from historical_data group by symbol having count(symbol) >= @maDays;";             cmd.Parameters.AddWithValue("@maDays", avgDayLimit);             cmd.CommandTimeout = 2400;             using (MySqlDataReader rdr = cmd.ExecuteReader())             {                 while (rdr.Read())                 {                     symbolList.Add(Convert.ToString(rdr["symbol"]));                 }                 l.CreateRunningEntry(3, "CalculateMovingAverage", "Pulled " + symbolList.Count + " rows.");             }         }     }     catch (MySqlException mse)     {         l.CreateRunningEntry(1, "CalculateMovingAverage", "Error while obtaining companies with historical days equal to or greater than " + avgDayLimit + ". " + mse);     }     conn.Close();      return symbolList; }  public bool DoesMovingAverageExist(MySqlConnection conn, decimal avgDayLimit, string symbol) {     conn.Open();     string lastDate = string.Empty;     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"select ma_date                                         from moving_average                                         where ma" + avgDayLimit + @" is not null                                         and ma_symbol like @symbol                                         order by ma_date desc limit 1; ";             cmd.Parameters.AddWithValue("@symbol", symbol);             using (MySqlDataReader rdr = cmd.ExecuteReader())             {                 while (rdr.Read())                 {                     lastDate = rdr["ma_date"].ToString();                 }             }         }     }     catch (MySqlException sqlException)     {         l.CreateRunningEntry(1, "CalculateMovingAverage", "Connection Error at figuring out last moving average date for ma" + avgDayLimit + " and symbol = " + symbol + ". " + sqlException);     }     conn.Close();      if (lastDate == string.Empty)         return false;     else         return true; }  public void InsertSMA(MySqlConnection conn, decimal avgDayLimit, string companySymbol) {     l.CreateRunningEntry(3, "CalculateMovingAverage", "There was no moving average for symbol = " + companySymbol + " and days = " + avgDayLimit);     conn.Open();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"insert into moving_average (ma_symbol, ma_date, ma"+avgDayLimit+ @")                                 select @symbol, max(histDate), avg(histClose)                                 from (select histDate, histClose                                       from historical_data                                       where symbol like @symbol                                       order by histDate asc                                       limit @avgDayLimit) temp                                  on duplicate key update ma" + avgDayLimit + @" = values(ma" + avgDayLimit + @");";             cmd.Parameters.AddWithValue("@symbol", companySymbol);             cmd.Parameters.AddWithValue("@avgDayLimit", avgDayLimit);              cmd.ExecuteNonQuery();         }     }     catch (MySqlException mse)     {         l.CreateRunningEntry(1, "CalculateMovingAverage", "Connection Error at calculating SMA. " + mse);     }     conn.Close(); }  public DateTime LastMovingAverageDate(MySqlConnection conn, decimal avgDayLimit, string companySymbol) {     DateTime maLastDate = new DateTime();     conn.Open();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"select ma_date                                 from moving_average                                 where ma" + avgDayLimit + @" is not null                                 and ma_symbol like @symbol                                 order by ma_date desc limit 1; ";             cmd.Parameters.AddWithValue("@symbol", companySymbol);             using (MySqlDataReader rdr = cmd.ExecuteReader())             {                 while (rdr.Read())                 {                     maLastDate = DateTime.Parse(rdr["ma_date"].ToString());                 }             }         }     }     catch (MySqlException mse)     {         l.CreateRunningEntry(2, "CalculateMovingAverage", "Connection Error at gathering last moving average date " + mse);     }     conn.Close();      l.CreateRunningEntry(3, "LastMovingAverageDate", "The last date pulled for " + companySymbol + " is " + maLastDate.ToString("yyyy-MM-dd"));     return maLastDate; }  public List<HistoricalClosing> GetHistoricalDateAndClosings(MySqlConnection conn, string companySymbol, DateTime maLastDate) {     l.CreateRunningEntry(3, "GetHistoricalDateAndClosings", "Getting the list of historical closing data for symbol = " + companySymbol);      List<HistoricalClosing> historicalClosing = new List<HistoricalClosing>();      conn.Open();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"select histDate, histClose                                 from historical_data                                 where histDate > @maLastDate                                 and symbol like @symbol                                 order by histDate asc;";             cmd.Parameters.AddWithValue("@maLastDate", maLastDate);             cmd.Parameters.AddWithValue("@symbol", companySymbol);             using (MySqlDataReader rdr = cmd.ExecuteReader())             {                 while (rdr.Read())                 {                     historicalClosing.Add(new HistoricalClosing                     {                         historicDate = DateTime.Parse(rdr["histDate"].ToString()),                         historicClose = decimal.Parse(rdr["histClose"].ToString())                     });                 }             }         }     }     catch (MySqlException mse)     {         l.CreateRunningEntry(1, "CalculateMovingAverage", "Connection Error at gathering historical_data list " + mse);     }     conn.Close();      return historicalClosing; }  public void GatherEMAComponents(MySqlConnection conn, string companySymbol, DateTime historicDate, decimal historicClose, decimal multiplier, decimal avgDayLimit) {     l.CreateRunningEntry(4, "GatherEMAComponents", "Gathering all data for EMA.");     conn.Open();     decimal lastEMA = new decimal();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"select ma" + avgDayLimit + @" as ema                                 from moving_average                                 where ma_symbol like @symbol                                 and ma" + avgDayLimit + @" is not null                                 order by ma_date desc                                 limit 1; ";             cmd.Parameters.AddWithValue("@symbol", companySymbol);             using (MySqlDataReader rdr = cmd.ExecuteReader())             {                 while (rdr.Read())                     lastEMA = decimal.Parse(rdr["ema"].ToString());             }         }     }     catch (MySqlException sqlException)     {         l.CreateRunningEntry(1, "GatherEMAComponents", "Error while gathering last EMA price to calculate EMA for company " + companySymbol + " and maDays = " + avgDayLimit + ". " + sqlException);     }     conn.Close();      decimal exponMovingAvg = CalculateEMA(historicClose, lastEMA, multiplier);      InsertEMA(conn, companySymbol, historicDate, exponMovingAvg, avgDayLimit); }  public void InsertEMA(MySqlConnection conn, string companySymbol, DateTime historicDate, decimal exponentialMovingAverage, decimal avgDayLimit) {     l.CreateRunningEntry(4, "InsertEMA", "Inserting EMA = " + exponentialMovingAverage + " for company " + companySymbol + " on " + historicDate);     conn.Open();     try     {         using (MySqlCommand cmd = conn.CreateCommand())         {             cmd.CommandText = @"insert into moving_average (ma_symbol, ma_date, ma"+avgDayLimit+ @") values (@symbol, @date, @ema) on duplicate key update ma" + avgDayLimit + @" = @ema;";             cmd.Parameters.AddWithValue("@symbol", companySymbol);             cmd.Parameters.AddWithValue("@date", historicDate);             cmd.Parameters.AddWithValue("@ema", exponentialMovingAverage);             cmd.ExecuteNonQuery();         }     }     catch (MySqlException mse)     {         l.CreateRunningEntry(1, "InsertEMA", "Error while inserting EMA for company " + companySymbol + ". " + mse);     }     conn.Close(); }  public class HistoricalClosing {     public DateTime historicDate { get; set; }     public decimal historicClose { get; set; } } 
Tags:mysql

Related Articles

  • C# / SQL Process Feels Inefficient and SlowJune 12

    I am calculating the moving average for 5,534,446 rows in one table. I am using C# as my language and MySQL as the database. Below is the code I am using to gather and calculate an exponential moving average for different sets of days. The program is

  • sql bulk delete is much slower?July 10

    I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete DELETE TOP (n) syntax, then loop until now more needs to be deleted it seems we co

  • SQL server queries are really slow only on first runJanuary 6

    Somewhat strange problem... when I start my .NET app for the first time after rebooting my machine, the SQL Server queries are really slow... when I pause the debugger, I notice that it's hanging on getting the response from the query. This only happ

  • Sql Agent Jobs executes too slow

    Sql Agent Jobs executes too slowApril 21

    I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** Object: StoredProcedure [dbo].[MARK_ACTIVE] Script Dat

  • SQL Management Studio is painfully slow on 32-bit Windows 7December 28

    I've been having issues running anything in SQL Management Studio on Win 7. Basically, doing anything through the Management Studio interfaces completely freezes it up for a few minutes. Running a query is nearly impossible because it takes nearly 2

  • SQL Server 2008 Underperforming - extreme slowness after migrating to new serverApril 8

    Last weekend we moved our production database to a new server. It's a Windows Server 2008 R2 Datacenter. On it is a brand new installation of SQL Server 2008 Enterprise Edition 64-bit. On Sunday, after the move was finished, everything looked normal.

  • SQL Server "empty table" is slow after deleting all (12 million) records?March 8

    I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a new data set. However, commands that once ran instan

  • Why is SQL Server Managment Studio so slow to open?May 20

    We have a new installation of SQL Server 2005. The server doesn't have access to the internet and we've disabled the checks for certificate revocation but don't want to continue to do this as it is dangerous. But are still having these really slow st

  • How can see what processes makes my server slow?

    How can see what processes makes my server slow?August 4

    All my websites on my server are extremely slow or not loading at all. Even server admin (Plesk) will not load some times. There's been no changes to the sites for the last coupple of months. How can I see what processes is making my server slow? My

  • Why is a Merge process via ArcObjects extremely slow?December 6

    In one of my arcobjects-based GIS tools I have a process which takes an array of shapefile paths and uses them as inputs with the ESRI.ArcGIS.DataManagement.Merge tool. I run it via a Geoprocessor instance. I use this setup for various other gp tasks

  • Fast SQL query with * but extremely slow with specific columns? May 1

    We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER JOIN MyTable b (nolock) ON a.id = b.id AND a.thedate >

  • SQL Server 2008 is suddenly slow as a slothMay 4

    We run an SQL Server 2008 R2 Standard install on a 4core xeon with 8gb ram (of which sqlserver is allowed to use 6gb). I have a VB6-app that runs queries on the data in this database to generate reports on survey-data. These reports usually take 7-10

  • Why is SQL Server IO so much slower than the disk capacity?September 5

    I'm running SQL Server 2012, and doing a simple SELECT * query, I can see that my disk IO in performance monitor is < 10MB/s. If I do a backup of the database, however, I see values > 200 MB/s. Why is it that sql server is so slow at retrieving data

  • Image Processing codes running too slowMay 23

    Situation I've written a simple black and white image filter application with JavaScript using html5 canvas. I have take two approaches, one with websockets and the other normally, the code is hosted on github On my desktop browser and Firefox OS, fo

  • strange process of making a slow networkFebruary 7

    whether this process, making the connection to be slow. how to eliminate it permanently, I have used comment kill -s 9 [PID]. but reappears help me please. tks PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11988 root 20 0 67924 1164 212 S 4 0

  • SQL Query is getting progressively slowerJanuary 4

    We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can. We have a webserver and database server through Rackspace. They are running Windows Server and Sql Se

  • Why is processing a sorted array *slower* than an unsorted array? (Java's ArrayList.indexOf)January 26

    The title is in reference to Why is processing a sorted array faster than an unsorted array? Is this a branch prediction effect, too? Beware: here the processing for the sorted array is slower!! Consider the following code: private static final int L

  • "Processing" in Matlab software slowing the program down

    "Processing" in Matlab software slowing the program downFebruary 2

    Whenever I save, change a folder, or sometimes at seemingly random times, the current folder tile will show a loading icon and say "processing" I consistently have to press cancel or else the Matlab software will slow down or freeze. No one else

  • SQL Server file growth extremely slowAugust 28

    I have a database with a size around 100 GB, and I have serious problems expanding the size of the database. This is my setup: SQL Server 2005, Version 9.0.4035 OS: Windows Server 2003 SP2 x86 Running as a guest on Hyper-V Data file and log file on d

  • SQL Server Query: Inefficient where clauseAugust 23

    I have the following SQL query: select * from NETWORK n inner join vwNETWORK_KEYMSTN km on n.Network_ID = km.Network_ID inner join vwAPPROVAL_LATEST a on n.Network_ID = a.Network_ID inner join APPROVAL_VINTAGE av on a.Approval_ID = av.Approval_ID and

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.781 s.