Use SQL INSERT command to append 1000 rows to an existing

[JoeSpinozzi]

I am using SQL for data storage on automated experiments due to its speed and storage capability. So far I've appended data into the table one row at-a-time. If I do that, using an N-loop, it takes about 90 sec over LAN, which is no good.

I need a way to append 1000 rows simultaneously, such as using (in MS Access)

INSERT INTO RawShotData ( Energy, Voltage, Linecenter, Linewidth, Flag ) SELECT DataSrc.En, DataSrc.Vo, DataSrc.LC, DataSrc.LW, DataSrc.BF FROM DataSrc

assuming in this case the table DataSrc exists and contains the data I want to append. Instead, of using table datasrc, I want to feed a 2-D array (5x1000) and have that data appended into RawShotData.

Ideas?

[codeman]

Do you have the LabVIEW SQL toolkit, Database Connectivity toolset, or are you going to try to do this with Active X and calls to ODBC? If you have the toolkits, are you getting any errors when you try the same sort of insert statement in LabVIEW?

[JoeSpinozzi]

Thanks for answering - I am in fact using the SQL Toolkit, specifically "DB Tools Insert Data", which will append data fine - one row at a time. So I loop that 1000x and it takes forever. It requires a cluster for the input. I feed it a cluster of 5 items: (5 columns - like SQL statement in original message), and this works. But if I have to repeat this 1000x, it's terrible. During testing I repeat this same 1000 samples every 5 minutes!

Every sample VI I can find that inserts more than one row does it one at a time. Is this my only choice?

From Access or SQL, you can paste many rows in one command, by telling it to INSERT-SELECT (insert the result of a query, or insert the contents of another tab
le). The problem with that in Labview is that there is no source to select from - my data is currently in an array (5x1000). See attachment.

[codeman]

I do not know a way to update several rows with a single SQL statement in LabVIEW. You are correct about not being able to do a INSERT-SELECT statement in LabVIEW. But there are faster ways to execute the SQL inserts then the way you are doing it right now. Try looking at the example program for Dynamic SQL and the benchmarking example. Dynamic SQL could probably improve things. The examples ship with the toolkit and should be in the LabVIEW\examples\SQL\insert.llb

[JoeSpinozzi]

Cannot find your example - I found c:\program files\National Instruments\Labview6\examples\Database\examples\DatabaseEx.llb, automatically installed by DB-Connectivity Toolset. I've searched for any reference to Dynamic SQL and insert.llb - cannot find any of these. What am I doing wrong?

[JellyBeans]

Look for any "parameterized" insertion example. That is same as dynamic SQL (just another name by microsoft).
The reason calls are made faster is because you are not sending the whole SQL INSERT command again and again, but the DB engine only updates the data in the command. So it doesn't process the whole command, instead it caches the command and keep replacing the data elements and executing them.

A Rafiq
National Instruments

[Labviewguru]

Joe,

Were you able to resolve this issue?

Sounds like you need to use some batching, or perhaps make a stored procedure on your database to handle this. If I am not mistaken (someone will surely tell me...) you can just dump the data into a stored procedure all at once and the stored procedure can process into the rows for you.

I have worked with similar situations, but certainly not with that much data.

Let me know how it goes.

Oh, and how is the laser biz anyway?

-Mike

[HWJ]

hi to all.. I'm new to using labview database connectivity toolkit. Was surfing this thread and realise i'm also currently encountering the same prob. Has anyone found good methods to insert large amounts of data into the database using the "insert data.vi" under the labview database connectivity toolkit. Will greatly appreciate any form of advice or help. 😃

[Jaime Rodriguez]

You can use a for loop with auto indexing enabled and place the insert data vi inside the loop. THis way the data is inserted one row at a time, but the loop will execute 1000 times. I know this sounds weird but this is how you need to do it in the Toolkit. The performance should not be affected too bad if you Open Connection only once (outside the loop) and close the connection once it is done.

[Crystal]

I recommend looking at the Parameterized examples or look at stored procedures.  It depends upon what database management system you are using.  I don't think Access supports stored procedures, but they are great for SQL Server and Oracle.