Need memory saving idea

[ralphtrent]

Hi
I have a program that reads a file line by line. The line contains a date time stamp, a stored procedure name, all ot its parameters and then some additional details.

I need to be able to see how many stored procedures with the same exact parameters where executed. What i currently do is create two data tables.

The idea of the first DT is to hold the name of the SP and the parameters. The way he gets populated is as follows. I read the line of the file, I check to see if the sp_name and parameters combonation exists in the database using the Datatable.Rows.Find method. If the row exists I move on, if not I add the row and go on to populate the second database with the SP name and the other details. I finish going through the file. At the end, I take my second datatable and I dump the results to a file. When I write the row to the file (using a foreach on database.rows) I DataTable.Select against my first Datatable for rows where the SP_NAME = the current row's SP_NAME. This throws those rows into an array. I then say the length of the array is the number of unique SP executions.

Now for the problem. I am working with a 340 meg file, the memory usage of the program has peeked over 400k. What can I do to limit the amount of memory this program uses.

One thing I have tried was instead of holding onto the text of the parameters was to turn the parameter string into a number (using string.GetHashCode()), but the rarity happend where the hash number was the same for two different strings and that threw my numbers off.

Any idea's are GREATLY Appreciated.

Thanks.

 

[Zathras]

As you are reading the file line by line, insert a row into a temporary database table (one row per line) Then query the table to get your counts.

For example, if the maximum number of parameters for any SP is 3 (scale up as neeed) you should be able to populate a table like this:

INSERT INTO MyWorkTable (Name, P1, P2, P3)
VALUES('sp00001','42','1/1/06','12/31/06')

SELECT Name,P1,P2,P3,Count(*) FROM MyWorkTable
GROUP BY Name,P1,P2,P3
ORDER BY 5 DESC

 

[ralphtrent]

You are on the right track. I am not working with a database, but rather a DataTable that is held in memory.

 

[dEVooXiAm]

First, I dont really see a reason for datatable here.
Define a class

public class SPUsageData
{
    public string spName;
    public string[] spParams;
    public int spUseCount;
}

public ArrayList spData;

then simply run though the file reading each line. For each line, extract sp_name and params, and loop through the existing records in spData to check it that stored procedure is already there. If you found it, just do spUseCount++, otherwise add the new record into spData with the read sp_name, params and 1 for spUseCount.

This way you'll only occupy space for different stored procedures, and won't need to do that SQL stuff.
OR, if you want to use DataTable (maybe faster searching), then just define 1 table with stucture same as my proposed class and fill it only. Those two tables are really not necessary... think about it - you're making almost a copy of source text file into memory, plus creating another table to list the differences..

------------------
When you do it, do it right.

 

[ralphtrent]

that is very similar to what I am doing. I am only adding the unique sp/parm combo's to the first database. But in this 340 meg file, the same sp gets executing 57,312 time, each with different parms, so it is always unique. Is an array list, less memory heavy then a datatable?

Thanks

 

[chrissie1]

Yep an arraylist is less heavy and perhaps you could use int16 instead of int wich is 32 bit

i thought this should work.

public class SPUsageData
{
    public string spName;
    public string[] spParams;
    public static int spUseCount;

    public SPUsageData()
    {
      spUseCount++;
    }
}

Christiaan Baes
Belgium

"My new site" - Me

 

[JurkMonkey]

Can't you use a hashtable that uses a string key and your SPUsageData bean as a value?

HashTable spDataHash = new HashTable();

spDataHash.Add("param1;param2;param3", new SPUsageData(param1, param2, param3));


Then you can use

if (spDataHash.Contains("param1;param2;param3")
{
//The same parameters already exist.
}


This would save you from looping through an arraylist and casting.

 

[ralphtrent]

I am unfamiliar with the hashtable, but if I just add the parameters values to the hashtable, like you show JurkMoneky, won't that increase the memory anyway? Or does the value get conerted to a hash number when you add the value? Also, the examples seem to me like you are only counting the number of times an SP is executed, that part I got, i just need to know each different execution. So I have two counts, a total execution count and a unique execution count.

Would it help if I posted my entire code with comment?

Thanks

 

[JurkMonkey]

oops!

You are right that it will probably take up more memory. I was thinking about speed!



I guess that's what happens when you wake up too early!

 

[ralphtrent]

I hear yea, you need to get some Dunkin Donuts Coffee

 

[JurkMonkey]

We have Tim Hortons where I come from

 

[ralphtrent]

ahhh, your from the north? Are they any good, I saw one a few years ago in Niagra, but I did not go in. Although, I did use their parking lot to make a U-Turn.

 

[JurkMonkey]

Tim Hortons = GOOD

Tim Hortons Parking Lot = GOOD