Memory usage issue with SQL toolkit

[LAVA 1.0 Content]

Hi all,

I developped a relatively small application in LV 7.1.1 that saves data every 5 sec to a DataBase (MSSQL) using SQL toolkit.

This is the first time I use this toolkit and my knowlegde regarding DataBase is really poor so I might just be doing a beginner's mistake.

Here is the issue :

If I disable the "write to DataBase" functions my application can run forever without any trouble an its memory usage in thet task manager is 34120K and will not move of a byte.

If I enable the "write to DataBase" functions, the memory usage will slowly increase, then the system starts using Virtual Memory, and after 15 days (approximately), the max amount of virutal memory allowed is met, the PC crashes down and has to be restarted.

Obviously I must be making a bad use of SQL VIs...

So, is there any "common silly mistake" to avoid when writing data to MSSQL DataBase ?

If needed I can upload my code...

Thanks for any help.

[Chris Davis]

For these kinds of errors, the first thing I reccomend is start looking for references that you aren't closing. If you have multiple writes to your database in your code, you could add them in one at a time to see which one causes the memory leak, that would help you figure out where to debug.

Start probing references and make sure the reference number isn't changing each time the code is called, that indicates a new reference being made each time the code is called.

[LAVA 1.0 Content]

CITATION(chrisdavis @ Mar 2 2007, 03:18 PM)

For these kinds of errors, the first thing I reccomend is start looking for references that you aren't closing. If you have multiple writes to your database in your code, you could add them in one at a time to see which one causes the memory leak, that would help you figure out where to debug.

Start probing references and make sure the reference number isn't changing each time the code is called, that indicates a new reference being made each time the code is called.

I indeed started by drawing a single VI that opens, writes, closes the reference at each call (every 5sec as I said). The VI runs like a small state machine :

- first the "open" case runs,

- then one of the write cases (there are a couple of different things than can be written),

- and the "close" case

Would it be better to open the reference once when the application starts, hold the ref in a shift register and close it only when the application is shut down ?

Here are 3 screenshots of the VI call ed to write data to DataBase (open case, write case and close case).

[xtal]

QUOTE(TiT @ Mar 2 2007, 10:59 AM)

Would it be better to open the reference once when the application starts, hold the ref in a shift register and close it only when the application is shut down ?

Typically, that's how I write my database applications - open the connection at the beginning, write and read as needed, close once at the end.

Another place to look is related to the driver you are using to communicate with the database. It appears you are using a UDL file to connect. For a quick check, you can change the drivers by using ODBC for the connection and see if that changes the memory usage. Normally, I don't recommend you use ODBC, but this is just a test to see if the driver has a memory leak. Otherwise, I'd make sure you had the most recent drivers for SQL Server. I remember an issue about a year or two ago where the MS driver had a memory leak and updating the version fixed the problem. You didn't mention what version of the drivers or MSSQL you are using.

Otherwise, I don't see anything in your code that stands out as a problem.

[LAVA 1.0 Content]

CITATION(xtaldaz @ Mar 2 2007, 09:46 PM)

Typically, that's how I write my database applications - open the connection at the beginning, write and read as needed, close once at the end.

Another place to look is related to the driver you are using to communicate with the database. It appears you are using a UDL file to connect. For a quick check, you can change the drivers by using ODBC for the connection and see if that changes the memory usage. Normally, I don't recommend you use ODBC, but this is just a test to see if the driver has a memory leak. Otherwise, I'd make sure you had the most recent drivers for SQL Server. I remember an issue about a year or two ago where the MS driver had a memory leak and updating the version fixed the problem. You didn't mention what version of the drivers or MSSQL you are using.

Otherwise, I don't see anything in your code that stands out as a problem.

Thanks a lot for sharing your experience !

I know my SQL toolkit is up to date, I'll check my MSSQL driver and update it if needed, if this doesn't solve the issue I'll try the OBDC solution.

I'll let you know the result

[xtal]

QUOTE(TiT @ Mar 2 2007, 05:04 PM)

Thanks a lot for sharing your experience !

I know my SQL toolkit is up to date, I'll check my MSSQL driver and update it if needed, if this doesn't solve the issue I'll try the OBDC solution.

I'll let you know the result

I knew the Database Connectivity Toolkit was current because there's only one version. {By the way, when you say SQL Toolkit, it makes me think you are using the old toolkit obsoleted 5 years ago; so I was confused until you included the screen captures.} In this case I do think there is a driver issue. Please keep me updated and I'd be happy to look into this further if you still have problems and can send me your code.

[Omar Mussa]

QUOTE(xtaldaz @ Mar 2 2007, 12:46 PM)

Typically, that's how I write my database applications - open the connection at the beginning, write and read as needed, close once at the end.

Another place to look is related to the driver you are using to communicate with the database. It appears you are using a UDL file to connect. For a quick check, you can change the drivers by using ODBC for the connection and see if that changes the memory usage. Normally, I don't recommend you use ODBC, but this is just a test to see if the driver has a memory leak. Otherwise, I'd make sure you had the most recent drivers for SQL Server. I remember an issue about a year or two ago where the MS driver had a memory leak and updating the version fixed the problem. You didn't mention what version of the drivers or MSSQL you are using.

Otherwise, I don't see anything in your code that stands out as a problem.

It is really important to use good error handling techniques as well to trap exceptions. For example, if your connection is open for a long time without any activity, the database server will generally close the connection. So, if this app will run for a long time, you'll need to check that the connection is alive (and potentially repoen the connection) whenever you do a query.

[Yair]

A method I like would work like this - change your state machine to obtain the reference to the DB - it should have an Open, Get and Close cases. You use the Open case to feed it the basic settings (path, passwords, etc.) which are then kept in SRs (or you can use your method of globals) and in the Get case you check the validity of the reference. If it is invalid, the VI closes it and calls the Open case by itself.

You can then create many VIs which will perform specific functions (List XXX, Update XXX, etc.). Each of these functions starts by calling the "Get" VI and you then use the reference you get with the DB VIs. Personally, I have some additional wrappers to the DB VIs (like an Update Table VI or an extended SQL query VI which fills in some stuff automatically).

The key is to use typedefs and have the specific function VIs translate the DB data to your typedefs and vice-versa, so that you are "decoupled" from your DB, to use some jargon . The result of all this is that you can simply place these functions whereever you want and not have to worry about how they get their data.

This entire thing, of course, is only really relevant if you have a single DB. If you have more than one, this can still be adapted, but it would be less ideal.

In any case, you should avoid opening and closing the connection all the time. It's slower (although if you only need it every 5 seconds that would matter less) and would make debugging harder (if you want to see if the connection reference changed).

[LAVA 1.0 Content]

@ xtaldaz

The MSSQL seems up to date (seems because I MS website is too messy for me an I couldn't find the cuurent version, my MS SQL server service manager is 8.00.760).

@ yen

The method you described is indeed quite nice, what I did is similar, maybe a bit less "decoupled".

My architecture has room for improvement but at the moment I am more focusing on a "technical/functional" issue regarding the DataBase and how to write into it.

@ Omar Mussa :

Indeed, Error Handling is necessary in a customer application, I do check the connection and handle (and log to file) potentiel errors.

The issue here is not that the connection is no longer alive.

It seems to that when I open the DataBase connection, the whole DB is loaded in memory ; but again, I know nothing about DataBase ...

I "heard to say" that there are different ways to open a connection to a DataBase, there must be a way that does not load the whole DB to memory ; this is what I am looking for ...

Any help would be highly appriciated.

[Michael Aivaliotis]

QUOTE(TiT @ Mar 5 2007, 12:30 AM)

I "heard to say" that there are different ways to open a connection to a DataBase, there must be a way that does not load the whole DB to memory ; this is what I am looking for ...

Huh? The whole database is not loaded into memory.

[LAVA 1.0 Content]

CITATION(Michael_Aivaliotis @ Mar 5 2007, 09:48 AM)

Huh? The whole database is not loaded into memory.

Well... As I said I don't really know...

If not, then why the memory usage keeps increasing ?

How should I code the "write to DataBase" so as to keep the memory usage stable ?

I've just downloaded a couple of example code from NI KnowledgeBase, I'm going to have a look into it.

[Yair]

QUOTE(TiT @ Mar 5 2007, 11:09 AM)

If not, then why the memory usage keeps increasing ?

As Chris originally stated, the most common reason for this is opening references without closing them. I can't look at the Insert Data VI at the moment, but it should definitely handle it's own memory internally (I don't remember having any problems with it), so in the code you posted this can happen if one of the other write cases has a T constant in it which stops the loop (it might even be hidden outside the visible case area), or if one of the other cases does something along these lines or it might be caused by some other VI.

Using a method like the one I mentioned will ensure that the connection reference handling is constrained to a simple VI and not handled all over the code.

[xtal]

QUOTE(yen @ Mar 5 2007, 02:55 PM)

As Chris originally stated, the most common reason for this is opening references without closing them. I can't look at the Insert Data VI at the moment, but it should definitely handle it's own memory internally (I don't remember having any problems with it), so in the code you posted this can happen if one of the other write cases has a T constant in it which stops the loop (it might even be hidden outside the visible case area), or if one of the other cases does something along these lines or it might be caused by some other VI.

Using a method like the one I mentioned will ensure that the connection reference handling is constrained to a simple VI and not handled all over the code.

Actually, I can answer the questions about the Insert Data VI because I was the owner of that toolkit when I was in LV R&D. It does handle it's own memory and does not leave references open. However, if you look at that VI, there is a Boolean input called create table. If you wire a True to that input, about 6 or so levels down in the resulting code lives a CIN that does have a memory leak. The solution is to make sure that input is False and you make sure the table is created ahead of time. I actually looked for this when I first saw your screen captures and it is indeed left as False. So that isn't the source of the memory leak.

However, as I mentioned before, I do remember there being a leak in one of the drivers for SQL Server or in MDAC. That's why I recommended you use the ODBC driver temporarily to see if the driver has the leak; that's how I found/verified the leak originally. You shouldn't have to change your code other than to wire in a string DSN instead of the file path to the UDL in the Open Connection VI. And yes, sigh, I know the MS site is really difficult to navigate. The version of the service doesn't have any relationship to the version of the driver. I don't currently have my SQL Server installed or I'd tell you how to find it.

Maybe some kind soul at NI will look in the CAR database and see what version of the driver had the leak and make a recommendation. {hint, hint}

[Grampa_of_Oliva_n_Eden]

Titou,

If you move the DB to another machine physical machine does it still keep sucking down memory?

Ben

[LAVA 1.0 Content]

CITATION(Ben @ Mar 6 2007, 01:00 AM)

If you move the DB to another machine physical machine does it still keep sucking down memory?

@ Ben

I wish I knew enough on DataBase to be able to do so

CITATION(xtaldaz)

That's why I recommended you use the ODBC driver temporarily to see if the driver has the leak; that's how I found/verified the leak originally. You shouldn't have to change your code other than to wire in a string DSN instead of the file path to the UDL in the Open Connection VI.

@ xtaldaz

I "tried to try" this but I am not the one who installed the database on the PC and I couldn't figure out how to build "DSN" string to input instead of the UDL

I keep ont trying to get a good string...

Also :

Maybe the fact the PC runs WinXP pro with only 256MB of RAM doesn't really help, at the beginning of the project I told the customer that maybe 512MB wouldn't be too much, but check out the link he gave me : http://www.microsoft.com/windowsxp/pro/eva...on/sysreqs.mspx

Thanks Microsoft ! I would laugh alot from that joke if only it WAS a joke !

UPDATE :

NI support pointed me this link : http://digital.ni.com/softlib.nsf/websearc...6256F49006F3693

I installed the patch and re-ran the soft.

In a couple of hours I'll be able to tell you if it solves the issue... stay in tune...

[Grampa_of_Oliva_n_Eden]

QUOTE(TiT @ Mar 6 2007, 03:16 AM)

@ Ben

I wish I knew enough on DataBase to be able to do so

I ask because I have sen the DB server suck up memory all by itself. Moving it to another machine would answer the Q "Is it LV or is it MS?"

Ben

[LAVA 1.0 Content]

CITATION(Ben @ Mar 6 2007, 08:13 PM)

I ask because I have sen the DB server suck up memory all by itself. Moving it to another machine would answer the Q "Is it LV or is it MS?"

Ben

The link given by NI Support doesn't solve the issue ( http://digital.ni.com/softlib.nsf/websearch/6A771B1EBA7E670786256F49006F3693' target="_blank">http://digital.ni.com/softlib.nsf/websearc...6256F49006F3693)

When the write to DB function are activated, the amount of memory used by the application increases of 4 byte every time I write to the DB...

I guess the next step to take is to follow Ben's advice and try the same with another DataBase, but that would take more time than I can afford. I'll see what I can do.

[Yair]

QUOTE(TiT @ Mar 8 2007, 10:03 AM)

When the write to DB function are activated, the amount of memory used by the application increases of 4 byte every time I write to the DB...

4 bytes = one I32 number = one reference.

It sure sounds like you're opening a reference and not closing it.

To check if this is a driver issue you can use the same driver and create a simple loop which runs many times and does the open-insert-close cycle. If there is a problem, you should see it even there. If not, can you upload the actual VI whose images you posted?

[alukindo]

TiT:

I have experienced the same memory leak issues when using SQL Toolkit VIs with MS SQL Server. I was recently in discussion with I2dx about the same. I2dx says he has yet to see this and has provided good evidence from his application that the memory problem does not apply to his applications.

In any case, adding the following properties --right after the Connection Object 'create method' has helped me to prevent memory leaks in my LabVIEW MS SQL database applications. Memory leaks were more prevalent when doing insert statements with many arguments. In one case I had memory dropping by 100 kb in about 5 second intervals!

The modification shown here was done inside the : DB Tools Open Connection.vi >>> DB Tools Open Connec (String).vi

P/se Note: The 'adReadMode' property will still allow you to write to database. It just means that the 'returned recordset' cannot be refreshed to modify the Database. This, I think, is a feature that you will not need.

.... Hope this helps

Anthony Lukindo

Tit:

Just wanted to add that the SQL Toolkit uses MS ADO ( Microsoft ActiveX Data Objects) drivers contained in MDAC (Microsoft Data Access Components) library of components. Latest and last version of ADO is 2.8. You can download this from this link:

ADO/MDAC 2.8

ADO is no longer being developed further by Microsoft because it has been superceded by ADO.net.

You can check if you have the latest version (2.8) by trying to reference the ADO library as shown in the screen shot below.

Anthony Lukindo

[LAVA 1.0 Content]

Thanks a lot for your post Anthony, I will upgrade the ADO (I currently have 2.7) and do some more testing.

Just to keep everyone involved in this thread updated, I tested my "write to DB" VIs on a new computer first with MS SQL server 2000 and then with 2005 ; in both cases the memory used by the LabVIEW exe keeps increasing of about 4bytes at each write.

The code I tested is a very small exe (see source attached), as you can see at each loop iteration, I :

- open connection to DB using a *.udl file

- write a couple data (floats)

- close the connection to DB

I have an NI car number : 521 262

As I said earlier I can't spend much time on this issue so I'll send this back to NI and demand an example that works without sucking up all the memory.

Many thanks to everyone involved in this thread.

[LAVA 1.0 Content]

CITATION(alukindo @ Mar 27 2007, 06:48 AM)

The modification shown here was done inside the : DB Tools Open Connection.vi >>> DB Tools Open Connec (String).vi

P/se Note: The 'adReadMode' property will still allow you to write to database. It just means that the 'returned recordset' cannot be refreshed to modify the Database. This, I think, is a feature that you will not need.

...

Just wanted to add that the SQL Toolkit uses MS ADO ( Microsoft ActiveX Data Objects) drivers contained in MDAC (Microsoft Data Access Components) library of components. Latest and last version of ADO is 2.8. You can download this from this link:

http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c' target="_blank">ADO/MDAC 2.8

I now have the lastest ADO/MDAC and I added the modifications you suggested at "open connection".

the issue remain the same. The memory keeps increasing...

:headbang:

[Grampa_of_Oliva_n_Eden]

QUOTE(TiT @ Mar 27 2007, 07:40 AM)

I now have the lastest ADO/MDAC and I added the modifications you suggested at "open connection".

the issue remain the same. The memory keeps increasing...

:headbang:

Broken record....

Is it LV or the DB that is eating up memeory?

Ben

[LAVA 1.0 Content]

CITATION(Ben @ Mar 27 2007, 02:48 PM)

Broken record....

I'm not trying to break any record... just to find out how not to suck up all the memory

CITATION(Ben @ Mar 27 2007, 02:48 PM)

Is it

LV

or the DB that is eating up memeory?

Ben

When I look at the windows task manager, it is the virtual memory taken by my exe (made with LV 7.1.1) that keeps increasing.

[alukindo]

TiT:

I recreated your database in MS SQL Server and I was able to duplicate the memory leak. I got exactly 4 kb of memory leak for each database call from the database VI. I also determined that the fix I suggested previously did not fix the problem.

However, when I opened the connection just once and left it open in a shift register, the memory leak problem went away. I am attaching your code modified to leave the connection open until DB operations are done. I tested this on my MS SQL Server and it looks OK now.

I hope this helps

Anthony Lukindo

[i2dx]

QUOTE(TiT @ Mar 27 2007, 09:37 AM)

in both cases the memory used by the LabVIEW exe keeps increasing of about 4bytes at each write.

I have no knowledge about LabSQL, but the increase of memory of exactly 4 bytes each write operation leads me to the conclusion, that you have an issue with an open reference. I don't know where to search, but I'd search for a reference, which is not closed.