Best way to interface LabVIEW to SQL Server

[John Lokanis]

I have written a large test application that logs all data live to an SQL server. I am using the System.Data.SQLClient .NET assembly to interact with the database. This is working, but it seems to be a bit slow and I am wondering what the best method is out there. There are lower level drivers between this assembly and the transport layer. Has anyone written VIs to directly access those?

One thing I did was write a little C#.NET code to move the record set data from .NET to LabVIEW in a single block move instead of an iterative process. This was a huge time saver, but I am wondering if there are more ways to speed things up.

I realize that LabVIEW is not very speedy when calling .NET code. Would it be worthwhile to try an implementation using COM (ActiveX) instead?

What about directly calling the lower level DLLs that the .NET code calls?

Is there another solution entirely that I could try? All I need to be able to do is pass a SQL query to the DB and get back a record set.

thanks for any help or insights.

-John

[alukindo]

Hello jlokanis:

I continue to use ActiveX Data Objects (ADO) to access SQL Server databases ---but also use the latest SQL Native Client drivers for enhanced performance. I have attached a single VI that works as a functional global that I use to connect, read, and write to SQL Server 2000/2005 databases. There is an example with notes that can help you to implement this method. I have included web links to driver files that you may need.

Keep in mind though that the fastest read/write operations that can be achieved with databases is about 300 round-trip calls per second. You can, however, use methods such as 'Bulk Copy' or 'Bulk Insert' to export/import ~300,000 rows of data per second or better.

Anthony

[Michael Aivaliotis]

I've always used the ADO interface myself. Have been for years without problems.

You can download some VI's that wrap around it here:

http://www.jeffreytravis.com/lost/labsql.html

I've modified the original to optimize it here and there and it works fine.

[John Lokanis]

Thanks for the replys. It looks like both of the VIs mentioned above use COM to access the ADO interface instead of ADO.NET. Do you feel COM (ActiveX) is faster than .NET in LabVIEW?

One difficulty I see in .NET vs COM is the lack of a native means of moving the recordset from .NET into LabVIEW. The ADO.NET assembly wants to use a Reader to iterate through the results one by one and fetch them. While this is a more portable interface (especially for script based languages that run in a VM, like VBScript or JavaScript), it is very slow in LabVIEW. As a result, I had to implement some tricks learned from Brian Tyler's old LV Blog and some C# code to work around this.

Also, since both of your implementations use ADO, they must go through an additional layer (OLE DB) to reach the database. This is good if you want code that can use a variety of databases, but if your target is only SQL Server, then the ADO.NET System.Data.SqlClient provider (see http://msdn2.microsoft.com/en-us/library/k...ks0(VS.80).aspx) should be faster.

Also See http://sqlbible.tar.hu/sqlbible0123.html

And http://docs.msdnaa.net/ark_new3.0/cd3/cont...rgas%5CCh12.pdf

And http://msdn2.microsoft.com/en-us/library/ms675532.aspx

So, I guess ADO via ActiveX and ADO.NET are the leading methods. I have looked at directly calling the DBLIB DLL but that looks a bit more formidable to program.

I wonder if passing the SQL call outside of LabVIEW to another environment would make more sense?

-John

[Michael Aivaliotis]

I haven't used .NET for database access, since what I have works, so I'm not familiar with this iterative retrieval process. The ADO interface I'm using with the labsql allows me to collect multiple rows of data with a single sql call. It's pretty fast. In essence, you want to make as an elaborate an SQL query as possible so that it can retrieve data in a single call.

[alukindo]

QUOTE (jlokanis @ Mar 14 2008, 05:26 PM)

. . . . Also, since both of your implementations use ADO, they must go through an additional layer (OLE DB) to reach the database. This is good if you want code that can use a variety of databases, but if your target is only SQL Server, then the ADO.NET System.Data.SqlClient provider

-John

John:

I think that ADO exposes a high level and simplified API with the requirement of using any other COM-compatible lower level driver. This means that at least one other lower level driver layer must be there for ADO to access a data source. To meet this requirement ADO comes bundled inside MDAC (Microsoft Data Access Components) which includes lower level drivers for ODBC, OLEDB, as well as ODBC for OLEDB .

However, the SQL Native Client driver that was released with SQL Server 2005 is not an OLE DB type driver and is 'native to SQL Server'. This is why other than requiring ADO you must also have the SQL Native Client driver. This SQL Native client has never been part of any version of MDAC. Note also that the example connection strings that I included in the previous post refer to Driver={SQL Native Client}

At some point I figured that ADO.NET technology is mostly suited to web access where connections need to be closed from the data source and have the data reader component work off-line. There is also database schema that ADO.NET exposes. But for simple data access chores, ADO does a very good job.

. . . just my two cents worth.

Anthony

[jdunham]

QUOTE (jlokanis @ Mar 13 2008, 04:33 PM)

I have written a large test application that logs all data live to an SQL server. I am using the System.Data.SQLClient .NET assembly to interact with the database. This is working, but it seems to be a bit slow and I am wondering what the best method is out there. There are lower level drivers between this assembly and the transport layer. Has anyone written VIs to directly access those?

One thing I did was write a little C#.NET code to move the record set data from .NET to LabVIEW in a single block move instead of an iterative process. This was a huge time saver, but I am wondering if there are more ways to speed things up.

I realize that LabVIEW is not very speedy when calling .NET code. Would it be worthwhile to try an implementation using COM (ActiveX) instead?

What about directly calling the lower level DLLs that the .NET code calls?

Is there another solution entirely that I could try? All I need to be able to do is pass a SQL query to the DB and get back a record set.

thanks for any help or insights.

-John

John, We log tons of data to SQL server, sometimes a few dozen statements per second. The main optimization we use is that all statements which don't require a reply are sent in to a LabVIEW queue. Then a separate process flushes the queue once per second, concatentates all of the query statements, and inserts them a single ADO call. This process is not using a significant percentage of the CPU, so we could probably be logging a lot more.

If you need to return results, then it seems like you are doing more than just logging. Our system also needs results sometimes, and while those calls can't be batched together, we can still call several queries per second.

Good luck

[i2dx]

QUOTE (jlokanis @ Mar 14 2008, 01:33 AM)

Is there another solution entirely that I could try? All I need to be able to do is pass a SQL query to the DB and get back a record set.

maybe you want to try out my ADO-Toolkit?

(the link is in my signature ...)

cheers,

CB

[John Lokanis]

QUOTE (i2dx @ Mar 15 2008, 01:26 PM)

maybe you want to try out my ADO-Toolkit?

What interface does your toolkit use? ADO (ActiveX) or ADO.NET or some other external method to reach ADO databases?

-John

QUOTE (jdunham @ Mar 14 2008, 09:59 PM)

John, We log tons of data to SQL server, sometimes a few dozen statements per second. The main optimization we use is that all statements which don't require a reply are sent in to a LabVIEW queue. Then a separate process flushes the queue once per second, concatentates all of the query statements, and inserts them a single ADO call. This process is not using a significant percentage of the CPU, so we could probably be logging a lot more.

If you need to return results, then it seems like you are doing more than just logging. Our system also needs results sometimes, and while those calls can't be batched together, we can still call several queries per second.

Good luck

I am doing something similar. Each of my test engines (one for each DUT) has a thread that offloads all DB interactions and uses a queue. Calls that need return data pass a single element queue reference along with their SQL call to place the results in. Also, calls that require an answer enqueued at the opposite end so they get processed first. "Insert only" calls just queue up and get processed when there is time.

I have not tried merging calls together. All my calls are to stored procedures so they all return something (custom error message). I don't think I can merge them. I do detect errors and retry the call 5 times before giving up. I get a lot of timeouts due to the DB being overloaded.

We can have several testers all logging at once and up to 200 transactions per second hitting the database.

I also did a little experiment recently to test methods of moving data between .NET and LabVIEW. I was building an ArrayList in .NET from the record set. The potential issue was that adding to an ArrayList allocates new space on each call. This reoccurring malloc could slow down the process. So, I changed it to a fixed 2D array in .NET. The problem here is the DataReader that gets the record set from the server is a forward only reader that cannot know how many rows are being returned. So, there is no way to know how big of an array to create before reading. So, I used a call that I knew would return 200 rows only. I then hardcoded the size of the 2D array to 200 rows. The funny thing was, when testing this the static array method was no faster than the ArrayList method. So, my new guess is the forward only data reader is the bottleneck in the process.

FWIW, I was getting back a 200 row, 15 field record set in ~200ms. Still, the processing time on the DB to do the fetch and build the record set was < 1 ms. So, there is some significant overhead somewhere in there.

-John

[Rolf Kalbermatter]

QUOTE (jlokanis @ Mar 13 2008, 08:33 PM)

I have written a large test application that logs all data live to an SQL server. I am using the System.Data.SQLClient .NET assembly to interact with the database. This is working, but it seems to be a bit slow and I am wondering what the best method is out there. There are lower level drivers between this assembly and the transport layer. Has anyone written VIs to directly access those?

One thing I did was write a little C#.NET code to move the record set data from .NET to LabVIEW in a single block move instead of an iterative process. This was a huge time saver, but I am wondering if there are more ways to speed things up.

I realize that LabVIEW is not very speedy when calling .NET code. Would it be worthwhile to try an implementation using COM (ActiveX) instead?

What about directly calling the lower level DLLs that the .NET code calls?

Is there another solution entirely that I could try? All I need to be able to do is pass a SQL query to the DB and get back a record set.

I'm using my own LabVIEW ODBC VI library to do database access. From a VI interface perspective it is closely related to the previous non ADO Databases Toolkit from NI, ex Ellipsis Medium SQL Toolkit. All these VIs do is calling into a DLL/shared library that interfaces to the actual ODBC API. While there have been claims that ODBC is slow this is not really my experience but the problem is similar to what you see in your ADO.NET access. The most simple ODBC access method is to query every row and column value independantly causing many server roundtrips. By using the right methods (multi row cursors the equivalent to rowsets in ActiveX ADO) you can avoid many server roundtrips and get quite impressive speeds. I implemented this in the DLL almost completely transparent to the calling LabVIEW application (almost because you can influence the rowset size by a parameter or disable it entirely). Doing the SQL datatype to LabVIEW datatype conversion in the DLL too, and making sure to use similar datatypes in LabVIEW that match closely to the SQL datatype instead of all strings will also help to speed up DB access.

And before you ask, no I haven't made that VI library available to people outside of our company yet. I considered doing this but there are several reasons why this might be not such a good idea. One of them is that there are already many (albeit non ODBC) database access libraries out there for LabVIEW. Adding yet another one is not likely to help many people. Another one is that it was and is a lot of work to write and maintain such a library and even more to support it once it is public but there is no real market for VI libraries sold by someone else than NI.

Rolf Kalbermatter

[John Lokanis]

QUOTE (rolfk @ Mar 20 2008, 12:38 PM)

there is no real market for VI libraries sold by someone else than NI.

Don't tell that to Jim Kring!

Actually, I am looking at taking the same path. As you mention above, this wheel is been reinvented many times...

-John

[Rolf Kalbermatter]

QUOTE (jlokanis @ Mar 21 2008, 08:29 PM)

Don't tell that to Jim Kring!

Actually if you talk about VIPM that is another story. It is not a VI library but quite a useful tool. There is probably some market for this but it will be very difficult to generate enough sales for that to even pay for the maintenance of that product.

QUOTE

Actually, I am looking at taking the same path. As you mention above, this wheel is been reinvented many times...

Ouch! I started this back in around 1996 for a customer project where the NI SQL Toolkit couldn't interface to their own proprietary ODBC driver for some strange reasons and improved since on it. It was then an absolute requirement for that project to access their database control system so there was no way around this, but over the years I can't actually count the hours I invested to get it to the state it is now. It must be many 100 hours for sure.

Even implementing it from scratch again with all the knowledge I have now, I would estimate it to be at least 150 to 200 hours of work to get it where it is now. That is a lot of time.

While I'm not ready to release the C source code for the shared library to the public I would consider making the VI library available to people under some still to be defined conditions (not necessarely money related) for the added benefit of more thorough testing and usefullness to other people.

It wouldn't be an OpenG library though because I believe that every part of an OpenG library including the underlying shared library if any should be Open Source in one or the other form.

Rolf Kalbermatter