fastest type of database connection/use

[Michael Ludwig]

Hi all,

i have (inherited) a large piece of code that is constantly pulling medium-sized amounts of data (20 rows by 10 cols of numbers, strings and xml) from a db. We will make up to 400 calls to execute a single operation. This can get very slow.

Does anyone have suggestions as to how to speed this up? I can see a few options:

1. batch some of the queries together as much as possible;

2. pull bunches of data once out of the db, keep in memory, and sort through in LV when needed;

3. switch from ODBC to, say, ADO (is that easy to do?);

4. try to run queries in parallel (can that even be done?).

This is LV 7.1 on XP with SQL Server 2000/5/8 and ODBC.  

all suggestions welcome. and thanks. 

[jcarmody]

I saw a great improvement in the speed of one of my database applications when I got my IT guy to index the tables/fields I used most.

[Michael Ludwig]

most of my tables are already indexed (which can indeed really help). The bottleneck seems to be all the layers between LV, the OS and the Server. The send/retrieve times are about the same regardless of the type of (select) query. Some of them are on very small tables and still take a long time.

[Rob_Calhoun]

cross referencing Michael's other thread:

(1): As noted in the thread above batching inserts is definitely worth it.

(2): Reading from LabView memory is going to be faster than hitting the database, but you don't want to rewrite a database server in LabView! Beyond making sure that you are not asking for the same data over and over, my recommendation is to let the database be the database. 

. 

(3): I haven't used ODBC in a long time.We use ADO via the ADODB Active X object and SQLOLEDB. It is pretty fast. (We can read about 1000 rows/second from a table with 50+ columns of numeric/timestamp data.) We have also tried ADO.NET and although it should be fast (SQL Server 2008 Management Studio uses it, for example) we found it between 10 and 40 times slower than ADO when called from LabView. (LabView's .NET implementation is apparently more focused on compatibility than speed.)

ADO is very easy. If your code is all pure SQL, you really only need a few methods: Connection.Open, Connection.Execute (which returns a Recordset), and Recordset.GetRows. GetRows() is very fast; it returns an ActiveX variant that you cast to a 2D array of (ActiveX) variants that you can then convert to LV data. Jeffrey Travis' LabSQL is one set of wrappers around ADO; I think there's another one out there. Microsoft wants you to use ADO.NET for new designs but I can't see ADO going away any time soon. (And when it does, ODBC will still be around! Maybe you want to stay where you are...)

This is probably a good time to point out that there is a ghastly memory leak in ADO under Vista/2008 Server . Follow Microsoft's suggestions for changing the connection string to avoid it.

(4): You can absolutely run queries in parallel; just open multiple connections to the DB and do it. You have to make sure all of the VIs you are using are re-entrant all the way down. 

Other ideas:

How are you converting from database types to LabView types? That can be a bottleneck.

Are you stitching data from more than one table together to build your internal data structure? Consider making a view that abstracts a join in SQL Server so it does the gluing and you do a single read.

Can you make your LabView program less synchronous? LabView queues are fast and robust; if you can measure your data and then toss it off into a queue to get logged to the DB by another thread, you'll be able to read data faster.

Make sure SQL Server has plenty of RAM.