How Do I Save To Database Without Cause The Cpu Usage To 100%

Question

0.00/5 (No votes)

See more:

here i've an code for accepting the data from the server socket, the data is very realtime and it will save every data that come in,

here is my code,
please help me with the saving to database, so it can't cause cpu usage over 50%,

C#

using System;
using System.Net.Sockets;
using System.Threading;
using System.Net;
using System.IO;
using Npgsql;
using System.Text;
using System.Linq;
using System.Web;
using System.Security.Cryptography.X509Certificates;
using System.Net.Security;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using Program;

namespace program
{
    class Program
    {
        static void Main(string[] args)
        {
            TcpListener serverSocket = new TcpListener(IPAddress.Any,12001);
            TcpClient clientSocket = default(TcpClient);
            int counter = 0;

            serverSocket.Start();
            Console.WriteLine(" >> " + "Server Started");

            counter = 0;
            while (true)
            {
                counter += 1;
                clientSocket = serverSocket.AcceptTcpClient();
                Console.WriteLine(" >> " + "Client No:" + Convert.ToString(counter) + " started!");
                handleClinet client = new handleClinet();
                client.startClient(clientSocket, Convert.ToString(counter));
            }

            clientSocket.Close();
            serverSocket.Stop();
            Console.WriteLine(" >> " + "exit");
            Console.ReadLine();
        }
    }

    public class handleClinet
    {
        static int port = Convert.ToInt32(System.Configuration.ConfigurationSettings.AppSettings.Get("port"));
        string dbserver = System.Configuration.ConfigurationSettings.AppSettings.Get("dbserver").ToString();
        string dbuser = System.Configuration.ConfigurationSettings.AppSettings.Get("dbuser").ToString();
        string dbpassword = System.Configuration.ConfigurationSettings.AppSettings.Get("dbpassword").ToString();
        string dbdatabase = System.Configuration.ConfigurationSettings.AppSettings.Get("dbdatabase").ToString();
        string destdirectory = System.Configuration.ConfigurationSettings.AppSettings.Get("pathEODDirectory").ToString();
     
        TcpClient clientSocket;
        string clNo;
        public void startClient(TcpClient inClientSocket, string clineNo)
        {
            this.clientSocket = inClientSocket;
            this.clNo = clineNo;
            Thread ctThread = new Thread(doChat);
            ctThread.Start();
        }

        private void doChat()
        {
            int requestCount = 0;
            byte[] bytesFrom = new byte[clientSocket.ReceiveBufferSize];
            string dataFromClient = null;
            Byte[] sendBytes = null;
            string serverResponse = null;
            string rCount = null;
            requestCount = 0;

            NetworkStream ns = clientSocket.GetStream();

            serverResponse = "Username : ";
            sendBytes = Encoding.ASCII.GetBytes(serverResponse);
            ns.Write(sendBytes, 0, sendBytes.Length);

            System.IO.StreamReader streamReader1 = new System.IO.StreamReader(ns);
            if (streamReader1.ReadLine().Equals("afat") == true)
            {
                if(streamReader1.ReadLine().Equals("worldwar") == true)
                {
                    while ((true))
                    {
                        try
                        {
                            Thread.Sleep(10);
                            //requestCount = requestCount + 1;
                            NetworkStream networkStream = clientSocket.GetStream();
                            networkStream.Read(bytesFrom, 0, (int)clientSocket.ReceiveBufferSize);
                            dataFromClient = System.Text.Encoding.ASCII.GetString(bytesFrom);

                            System.IO.StreamWriter streamWriter = new System.IO.StreamWriter(networkStream);
                            System.IO.StreamReader streamReader = new System.IO.StreamReader(networkStream);

                            Console.WriteLine(">>" + streamReader.ReadLine());
                            LogFile log = new LogFile();
                            log.createLog(">> " + streamReader.ReadLine() + "\n");
                            string data = streamReader.ReadLine();
                            
                            //startTheThread(streamReader.ReadLine() + "\n");
                            //doSave(data);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(" >> " + ex.ToString());
                            break;
                        }
                    }
                }
            }
        }

        private Thread startTheThread( string input )
        {
            //Thread.Sleep(100);
            var t = new Thread(() => doSave(input));
            t.Start();
            return t;
        }

        private void doSave(string input)
        {
            Thread.Sleep(5);
            NpgsqlConnection conn = new NpgsqlConnection("Server=" + dbserver + ";Port=5432;User Id=" + dbuser + ";Password=" + dbpassword + ";Database=" + dbdatabase + "; Pooling=false;MinPoolSize=1;MaxPoolSize=20;ConnectionLifeTime=15;");
            conn.Open();
            string[] lines = input.Split(';');

            //////////////////////////////////////////////stocks
            if (lines[0] == "s" || lines[0] == "S")
            {
                NpgsqlCommand cmdPerTick = new NpgsqlCommand("insert into tbtstockrealtimepertick(cdstock,previouscloseprice,openprice,closeprice,highprice,lowprice,volume,value,freq,bestbid,bestbidvolume,bestoffer,bestoffervolume,lastupdate) values (@cdstock,@previouscloseprice,@openprice,@closeprice,@highprice,@lowprice,@volume,@valuetotal,@freq,@bestbid,@bestbidvolume,@bestoffer,@bestoffervolume,@lastupdate)", conn);

                cmdPerTick.Parameters.AddWithValue("@cdstock", lines[1]);
                cmdPerTick.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                cmdPerTick.Parameters.AddWithValue("@openprice", lines[3]);
                cmdPerTick.Parameters.AddWithValue("@closeprice", lines[4]);
                cmdPerTick.Parameters.AddWithValue("@highprice", lines[5]);
                cmdPerTick.Parameters.AddWithValue("@lowprice", lines[6]);
                cmdPerTick.Parameters.AddWithValue("@volume", lines[7]);
                cmdPerTick.Parameters.AddWithValue("@valuetotal", lines[8]);
                cmdPerTick.Parameters.AddWithValue("@freq", lines[9]);
                cmdPerTick.Parameters.AddWithValue("@bestbid", lines[10]);
                cmdPerTick.Parameters.AddWithValue("@bestbidvolume", lines[11]);
                cmdPerTick.Parameters.AddWithValue("@bestoffer", lines[12]);
                cmdPerTick.Parameters.AddWithValue("@bestoffervolume", lines[13]);
                cmdPerTick.Parameters.AddWithValue("@lastupdate", lines[14]);

                try
                {
                    int rowsAffected = cmdPerTick.ExecuteNonQuery();
                    Console.WriteLine("It Insert {0} lines in table tbtstockrealtimepertick", rowsAffected);
                }
                catch (Exception ex)
                {
                    //log.createLog("Inserting Error : " + ex.Message.ToString());
                }

                //update
                //Console.WriteLine("Error : " + ex.Message);
                NpgsqlCommand cmd = new NpgsqlCommand("update tbtstockrealtime set previouscloseprice = @previouscloseprice, openprice = @openprice, closeprice = @closeprice, highprice = @highprice, lowprice = @lowprice, volume = @volume, valuetotal = @valuetotal , freq = @freq , bestbid = @bestbid ,bestbidvolume = @bestbidvolume ,bestoffer = @bestoffer, bestoffervolume = @bestoffervolume, lastupdate = @lastupdate  where cdstock = @cdstock", conn);
                cmd.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                cmd.Parameters.AddWithValue("@openprice", lines[3]);
                cmd.Parameters.AddWithValue("@closeprice", lines[4]);
                cmd.Parameters.AddWithValue("@highprice", lines[5]);
                cmd.Parameters.AddWithValue("@lowprice", lines[6]);
                cmd.Parameters.AddWithValue("@volume", lines[7]);
                cmd.Parameters.AddWithValue("@valuetotal", lines[8]);
                cmd.Parameters.AddWithValue("@freq", lines[9]);
                cmd.Parameters.AddWithValue("@bestbid", lines[10]);
                cmd.Parameters.AddWithValue("@bestbidvolume", lines[11]);
                cmd.Parameters.AddWithValue("@bestoffer", lines[12]);
                cmd.Parameters.AddWithValue("@bestoffervolume", lines[13]);
                cmd.Parameters.AddWithValue("@lastupdate", lines[14]);
                cmd.Parameters.AddWithValue("@cdstock", lines[1]);
                try
                {
                    int rowsaffected = cmd.ExecuteNonQuery();
                    Console.WriteLine("It update {0} lines in table tbtstockrealtime", rowsaffected);
                    if (rowsaffected == 0)
                    {
                        NpgsqlCommand cmd1 = new NpgsqlCommand("insert into tbtstockrealtime(cdstock,previouscloseprice,openprice,closeprice,highprice,lowprice,volume,valuetotal,freq,bestbid,bestbidvolume,bestoffer,bestoffervolume,lastupdate) values (@cdstock,@previouscloseprice,@openprice,@closeprice,@highprice,@lowprice,@volume,@valuetotal,@freq,@bestbid,@bestbidvolume,@bestoffer,@bestoffervolume,@lastupdate)", conn);

                        cmd1.Parameters.AddWithValue("@cdstock", lines[1]);
                        cmd1.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                        cmd1.Parameters.AddWithValue("@openprice", lines[3]);
                        cmd1.Parameters.AddWithValue("@closeprice", lines[4]);
                        cmd1.Parameters.AddWithValue("@highprice", lines[5]);
                        cmd1.Parameters.AddWithValue("@lowprice", lines[6]);
                        cmd1.Parameters.AddWithValue("@volume", lines[7]);
                        cmd1.Parameters.AddWithValue("@valuetotal", lines[8]);
                        cmd1.Parameters.AddWithValue("@freq", lines[9]);
                        cmd1.Parameters.AddWithValue("@bestbid", lines[10]);
                        cmd1.Parameters.AddWithValue("@bestbidvolume", lines[11]);
                        cmd1.Parameters.AddWithValue("@bestoffer", lines[12]);
                        cmd1.Parameters.AddWithValue("@bestoffervolume", lines[13]);
                        cmd1.Parameters.AddWithValue("@lastupdate", lines[14]);

                        try
                        {
                            int rowsAffected = cmd1.ExecuteNonQuery();
                            Console.WriteLine("It Insert {0} lines in table tbtstockrealtime", rowsAffected);
                            //conn.Close();
                        }
                        catch (Exception ex)
                        {
                            //log.createLog("Inserting Error : " + ex.Message.ToString());
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.Write("Error : " + e.Message + "\n");
                    //log.createLog("Error : " + e.Message + "\n");
                }
            }
            else if (lines[0] == "i" || lines[0] == "I")
            {
                NpgsqlCommand cmdPertick = new NpgsqlCommand("insert into tbtindexrealtime(cdstock,previouscloseprice,openprice,closeprice,highprice,lowprice,volume,valuetotal,freq,lastupdate) values (@cdstock,@previouscloseprice,@openprice,@closeprice,@highprice,@lowprice,@volume,@valuetotal,@freq,@lastupdate)", conn);

                cmdPertick.Parameters.AddWithValue("@cdstock", lines[1]);
                cmdPertick.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                cmdPertick.Parameters.AddWithValue("@openprice", lines[3]);
                cmdPertick.Parameters.AddWithValue("@closeprice", lines[4]);
                cmdPertick.Parameters.AddWithValue("@highprice", lines[5]);
                cmdPertick.Parameters.AddWithValue("@lowprice", lines[6]);
                cmdPertick.Parameters.AddWithValue("@volume", lines[7]);
                cmdPertick.Parameters.AddWithValue("@valuetotal", lines[8]);
                cmdPertick.Parameters.AddWithValue("@freq", lines[9]);
                cmdPertick.Parameters.AddWithValue("@lastupdate", lines[10]);

                try
                {
                    int rowsAffected = cmdPertick.ExecuteNonQuery();
                    Console.WriteLine("It Insert {0} lines in table tbtstockrealtime", rowsAffected);
                }
                catch (Exception ex)
                {
                    //log.createLog("Update Error : " + ex.Message.ToString());
                }

                //update
                NpgsqlCommand cmd = new NpgsqlCommand("update tbtindexrealtime set previouscloseprice = @previouscloseprice, openprice = @openprice, closeprice = @closeprice, highprice = @highprice, lowprice = @lowprice, volume = @volume, valuetotal = @valuetotal , freq = @freq, lastupdate = @lastupdate  where cdstock = @cdstock", conn);
                cmd.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                cmd.Parameters.AddWithValue("@openprice", lines[3]);
                cmd.Parameters.AddWithValue("@closeprice", lines[4]);
                cmd.Parameters.AddWithValue("@highprice", lines[5]);
                cmd.Parameters.AddWithValue("@lowprice", lines[6]);
                cmd.Parameters.AddWithValue("@volume", lines[7]);
                cmd.Parameters.AddWithValue("@valuetotal", lines[8]);
                cmd.Parameters.AddWithValue("@freq", lines[9]);
                cmd.Parameters.AddWithValue("@lastupdate", lines[10]);
                cmd.Parameters.AddWithValue("@cdstock", lines[1]);
                try
                {
                    int rowsaffected = cmd.ExecuteNonQuery();
                    Console.WriteLine("It update {0} lines in table tbtindexrealtime", rowsaffected);
                    if (rowsaffected == 0)
                    {
                        NpgsqlCommand cmd1 = new NpgsqlCommand("insert into tbtindexrealtime(cdstock,previouscloseprice,openprice,closeprice,highprice,lowprice,volume,valuetotal,freq,lastupdate) values (@cdstock,@previouscloseprice,@openprice,@closeprice,@highprice,@lowprice,@volume,@valuetotal,@freq,@lastupdate)", conn);

                        cmd1.Parameters.AddWithValue("@cdstock", lines[1]);
                        cmd1.Parameters.AddWithValue("@previouscloseprice", lines[2]);
                        cmd1.Parameters.AddWithValue("@openprice", lines[3]);
                        cmd1.Parameters.AddWithValue("@closeprice", lines[4]);
                        cmd1.Parameters.AddWithValue("@highprice", lines[5]);
                        cmd1.Parameters.AddWithValue("@lowprice", lines[6]);
                        cmd1.Parameters.AddWithValue("@volume", lines[7]);
                        cmd1.Parameters.AddWithValue("@valuetotal", lines[8]);
                        cmd1.Parameters.AddWithValue("@freq", lines[9]);
                        cmd1.Parameters.AddWithValue("@lastupdate", lines[10]);

                        try
                        {
                            int rowsAffected = cmd1.ExecuteNonQuery();
                            Console.WriteLine("It Insert {0} lines in table tbtindexrealtime", rowsAffected);
                        }
                        catch (Exception ex)
                        {
                            //log.createLog("Update Error : " + ex.Message.ToString());
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.Write("Error : " + e.Message + "\n");
                    //log.createLog("Error Update Path : " + e.Message.ToString());
                }
            }
            //End
        }
    }
}

Posted 27-Aug-14 5:51am

Andrew Budiman

Add a Solution

Comments

PhilLenoir 27-Aug-14 12:09pm

You don't say which CPU, client application or database server? Have you checked which process is CPU bound?

Andrew Budiman 27-Aug-14 12:10pm

this is the client one, and the client is at the database server.
the one that cause CPU Bound is at the thread for saving to database.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajiv Gogoi · Answer 1 · 2014-08-27T07:03:00

Which version of Visual Studio you are using. VS 2013 has this tool.
http://blogs.msdn.com/b/visualstudioalm/archive/2014/02/28/new-cpu-usage-tool-in-the-performance-and-diagnostics-hub-in-visual-studio-2013.aspx[^]
I guess VS 2012 had a similar tool too to see CPU performance.
Please use one of these tools. Often in high CPU, tool analysis is faster than code review.

vikinghunter · Answer 2 · 2014-08-27T16:26:00

Solution 2

Your tables in your DB need indexes for you operations. It seems, it's not your app's fault.

Posted 27-Aug-14 16:26pm

vikinghunter

Comments

Andrew Budiman 4-Sep-14 11:28am

i've index my table, but it's also cause the cpu usage up to 100%, if using single core.
i've tested it just accepting, the cpu usage doesn't over 5%, but if i tested it with save to database it cause over 50%.

how can i fixed it ?
thx.