C# Discussion Boards - CodeProject

3-Apr-07 22:40

Hi,

I have an application which runs without any prob but takes 5 hours to run.

I am readin 112*8 dBase tables an write in 8 sql tables.

Please help me.

Regards
Sigh | :sigh:

Ruwandi

rkherath

Colin Angus Mackay3-Apr-07 22:46

Colin Angus Mackay

3-Apr-07 22:46

Have you made any attempt to find out where the applicaction's bottlenecks are? To find out where the application is inefficiently?

Since you have provided no useful information for us to help you, you will have to do some work in order to narrow down the location of the code that is not performing well.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

rkherath3-Apr-07 22:55

3-Apr-07 22:55

Hi,

ya its like this i have created oledb connection to create connection to the dbase tales.

I have 112 folders containing 6tdbase tables in each.

coding:

//reading from dbase
public void PluFill(string DBFpath)
{
try
{
oledbConnectionMethod(DBFpath);
oledbselctString = "SELECT PLU_NUM AS SKU,DESC AS Description,O_CP*Q_OPEN AS OpeningCP,Q_OPEN AS OpeningQty,Cost_Price*Qty AS ClosingCP,Qty AS ClosingQty FROM PLU";
oledbSelectMethod(oledbselctString);
oledbDataAdapterMethod();
oledbDataadapter.Fill(this.dsPlu);
}
catch (Exception Exp)
{
AppendToFile(DBFpath, Exp.ToString() + oledbselctString.ToString());
}

}

//writing to sql:

public void PluUpdate(string locationNam,string Year,string Month)
{
//opening the sql connection
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
}

//passing the dataset values to the data table
DataTable dt = dsPlu.Tables[0];

SqlCommand sqlInsertCommand=new SqlCommand();
sqlInsertCommand.Connection = sqlConnection;
//reading from the data table and wrinting to the sql table
foreach (DataRow dr in dt.Rows)
{

try
{
if (dr["OpeningCP"].ToString() == "")
dr["OpeningCP"] = 0;

if (dr["OpeningQty"].ToString() == "")
dr["OpeningQty"] = 0;

if (dr["ClosingCP"].ToString() == "")
dr["ClosingCP"] = 0;

if (dr["ClosingQty"].ToString() == "")
dr["ClosingQty"] = 0;

sqlInsertCommand.CommandText = "Insert into tblBalance_Monthly(cYear,cMonth,cLocationCode,cCategoryCode,cSubCategoryCode,cSKU,cDescription,nO_CostPrice,nO_Qty,nC_CostPrice,nC_QTY) values(\'" + Year + "\',\'" + Month + "\',\'" + locationNam + "\',\'" + dr["SKU"].ToString().Substring(0, 2) + "\',\'" + dr["SKU"].ToString().Substring(0, 3) + "\',\'" + dr["SKU"] + "\',\'" +
dr["Description"].ToString() + "\'," + dr["OpeningCP"] + "," + dr["OpeningQty"] + "," + dr["ClosingCP"] + "," + dr["ClosingQty"] + ")";

sqlDataAdapter.InsertCommand =sqlInsertCommand;

sqlInsertCommand.ExecuteNonQuery();
}
catch (Exception Exp)
{
AppendToFile(locationNam, Exp.ToString() + sqlInsertCommand.ToString());
}

}

}

//and i read the locations of the dbase tables through a class and for each location i perform writing and reding

void callTranfer(string Year, string Month, DateTime TXNDATE, string User_ID, string DriverLoc)
{
//object []retArray=new object[2];
DBCon dbcon = new DBCon();

DataTable dt = dbcon.LocationsFill().Tables[0];
Transfer.TransferClass tran = new Transfer.TransferClass();

int value=0;

foreach (DataRow dr in dt.Rows)
{
string folNam = (dr["Dir"].ToString());
DirectoryInfo forlderPath = new DirectoryInfo(string.Concat(DriverLoc, folNam));
//trnsName = forlderPath.ToString();
tran.PluFill(forlderPath.ToString());
tran.PluUpdate(folNam,Year,Month);
tran.Stk_tranRecieptFill(forlderPath.ToString());
tran.Stk_tranRecieptUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.Stk_tranIssuesFill(forlderPath.ToString());
tran.Stk_tranIssuesUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.salesFill(forlderPath.ToString());
tran.SalesUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.adjustmentsFill(forlderPath.ToString());
tran.adjustmentsUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.reclassificationsFromFill(forlderPath.ToString());
tran.reclassificationsFromUpdate(Year, Month, folNam, TXNDATE, User_ID);
tran.reclassificationsToFill(forlderPath.ToString());
tran.reclassificationsToUpdate(Year, Month, folNam, TXNDATE, User_ID);

DisplayText(folNam);
value = value + 1;
backgroundWorker1.ReportProgress(value);
}
}

plz help me

Regards
Ruwandi

rkherath

V.3-Apr-07 23:37

3-Apr-07 23:37

How many rows are you updating?
If it's a lot you might want to concatenate like 100 or more inserts before executing (delimit with ";") (INSERT INTO ... (...) VALUES (...);INSERT INTO ...

You also might want to try the StringBuilder object instead of concatenating strings.

hope this helps

V.
I found a living worth working for, but haven't found work worth living for.

rkherath3-Apr-07 23:58

3-Apr-07 23:58

Well in one table i ahve to update more than 25000 for sure

also can you explain the answer bit more.

I am really in need of this.

Regards
Ruwandi

rkherath

Christian Graus4-Apr-07 0:04

4-Apr-07 0:04

You need to not make a full round trip for each row. Instead, create SQL that updates several records for each round trip. This is your bottleneck.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

V.4-Apr-07 0:09

4-Apr-07 0:09

well instead of concatening string like this:

string test = "Test " + var + "\n'testmore'" + anothervar;

do this:

StringBuilder test = new StringBuilder("Test ");
test = test.Append(var);
test = test.Append("\n'testmore'");
test = test.Append(anothervar);

if you pass more than one query into the ExecuteNonQuery function (for example 100 queries) you save 99 calls to the ExecuteNonQuery function (which itself goes through enough other functions)
I'm not sure how this is done with SQL-Server but for Ms-Acces eg, the query will pass through the ExecuteNonQuery through the OleDb layer, through the mdac layer to the database.

Hope this helps.

V.

Stop smoking so you can: Enjoy longer the money you save.
Moviereview Archive

joon _vh.3-Apr-07 22:48

3-Apr-07 22:48

You're gonna have to give us some (a lot) more information before we can help you. Have you stepped through your code, or analyzed it in some other way? Do you know what takes the most time? Have you timed individual lines of code or methods?

Please do so and provide us with the details.

I don't know much about code analysis, but System.Diagnostics has a really cool stopwatch Smile | :)

gluck!

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

rkherath3-Apr-07 22:59

SQL Server 2005, Usage % all databases using performance counters!!

3-Apr-07 22:59

Hi Joon,

thanks for replying.

I already send the code to view. hope you can see. if you can have a look i can send the entire thing.

the longest time is taken for writing to sql. I dnt know why.

Plz help me.

I dnt know how what to do.

Regards
Ruwandi

rkherath

Adeel Chaudhry3-Apr-07 21:26

Adeel Chaudhry

3-Apr-07 21:26

Hello to all,

can anybody help me finding the right "Performance Counter" in visual studio 2005 with the help of which we can get the percentage measure of the usage of databases of SQL Server 2005!

tried several counters but wasnt able to find any which have the usage measure of databases!!!

Thanks in advance Smile | :)

Adeel

add to above...

Adeel Chaudhry3-Apr-07 21:27

Adeel Chaudhry

3-Apr-07 21:27

posted this in C# because i am trying to use it in VC#!! Smile | :)

Query [modified]

Nekshan3-Apr-07 21:09

Nekshan

3-Apr-07 21:09

i have these data in table 'dealer':

dealerid@@@campaignid@@@keyvalue
@1@@@@@@@@3@@@@@@@6
@2@@@@@@@@4@@@@@@@3
@3@@@@@@@@2@@@@@@@5
@4@@@@@@@@1@@@@@@@4
@5@@@@@@@@1@@@@@@@2
@6@@@@@@@@2@@@@@@@1
@7@@@@@@@@3@@@@<@NULL@> = NULL is null value in database without the '@'sign between brackets '<','>' and 'NULL'

'@' = spacebar,to separate the data.

Now i want the dealerid where campaignid = 3 n keyvalue = NULL
So, i queried in queryanalyzer of sqlserver2005:

SELECT dealerid FROM dealer WHERE (keyvalue = NULL) AND (campaignid = 3)

I must get output as 7.
But i dont get any output in its output window, neither i am getting any error.

Hope to get a reply.
regards Nekshan.

-- modified at 3:17 Wednesday 4th April, 2007

Re: Query

Christian Graus3-Apr-07 21:53

3-Apr-07 21:53

Your post is illegible.

Try keyvalue is null instead of keyvalue = NULL. It's a vagary of SQL, two instances of NULL will not have the same value, so nothing every = NULL.

Re: Query

joon _vh.3-Apr-07 22:33

3-Apr-07 22:33

Christian Graus wrote:
Your post is illegible.

I second

Christian Graus wrote:
Try keyvalue is null instead of keyvalue = NULL

This one I also second.

WHERE keyvalue IS null

should fix your problem.

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

grid control

Enter the Dragon3-Apr-07 20:34

Enter the Dragon

3-Apr-07 20:34

In the grid control when we scroll the sheet the header must still appear.It must not scroll up. Please give me a solution.

Regards,
Rocky.

Re: grid control

Christian Graus3-Apr-07 20:46

3-Apr-07 20:46

I don't think any grid that's built in does this by default. You'd have to render a header as a seperate control, then render a grid with no headers.

Re: grid control

joon _vh.3-Apr-07 22:38

Is it possible to use visual effect to show data from different sources?

3-Apr-07 22:38

I think a datagridview does. Doesn't it?
Yes it does (tested)

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Re: grid control

sudhakar_vaddepalli3-Apr-07 23:05

sudhakar_vaddepalli

3-Apr-07 23:05

hi,

There is no option in asp.net2.0 to control header alone.But using custom control, we can solve this.It's better to search for Custom control,

with regrads,
sudhakar.vaddepalli,

Khoramdin3-Apr-07 20:28

Khoramdin

3-Apr-07 20:28

Hello everyone,

I have a ListView control which is populated by data from two different data sources. Is there anyway that I can use color or similar sort of visual effect to show the user the data source of each row?

Thank you very much and have a great day.

Khoramdin

Re: Is it possible to use visual effect to show data from different sources?

Christian Graus3-Apr-07 20:32

Re: Is it possible to use visual effect to show data from different sources?

3-Apr-07 20:32

You can do this with an icon or text. How are you passing the data in ?

Khoramdin3-Apr-07 20:47

Khoramdin

3-Apr-07 20:47

Hello Christian,

Thanx for the respond. I am reading the data from two different CSV files and as I read each line, seperate the values and then populate the correct column in each row.

The following code is for populating one of the CSV files into the ListView:

StreamReader streamReader = new StreamReader("MyTempList.txt");<br />
        while ((line = streamReader.ReadLine()) != null)<br />
        {<br />
            //MessageBox.Show(line);<br />
            split = line.Split(delimiterChars);<br />
            //MessageBox.Show(split[0]);<br />
            //MessageBox.Show(split[1]);<br />
            //MessageBox.Show(split[2]);<br />
<br />
            // Call FindItemWithText, sending output to MessageBox.<br />
            ListViewItem foundItem = listView4.FindItemWithText(split[1]);<br />
            if (foundItem != null)<br />
            {<br />
                //MessageBox.Show("Calling FindItemWithText: " + foundItem.ToString());<br />
                // Do nothing.<br />
            }<br />
            else<br />
            {<br />
                //MessageBox.Show("Calling FindItemWithText: null");<br />
                ListViewItem item4 = new ListViewItem(split[0]);<br />
                item4.SubItems.Add(split[1]);<br />
                //item4.SubItems.Add(split[2]);<br />
                listView4.Items.Add(item4);<br />
            }<br />
        }

Thank you for taking the time and helping out, mate.

Khoramdin

-- modified at 3:02 Wednesday 4th April, 2007

Re: Is it possible to use visual effect to show data from different sources?

Christian Graus3-Apr-07 21:52

Re: Is it possible to use visual effect to show data from different sources?

3-Apr-07 21:52

OK, so as you add each item, you set properties on the ListViewITem instance, such as the icon or the color.

joon _vh.3-Apr-07 22:46