access database query import

[Brad_Turpin]

And you want ALL the rows from those columns?

[JimNoel]

Correct - there are 20 columns of data for each row in the query and approx 750,000 rows  (approx 15million datapoints!)

[Brad_Turpin]

Hi Jim,

I was able to load all 747338 rows from all the columns of the LTOE_#141 table.  This took quite a while, and it did give me an error message afterwards, but the loaded values looked (and graphed) OK.  I then tried to load the following selected channels from the same LTOE_#141 table:

Date
Time
TPIM_Fan_DutyCycle
VBatt

This also worked just fine, though again I got the spurious error message.  Loading these selected channels also resulted in each channel having 747338 values in the Data Portal.  I noticed that your data is acquired twice per second over a multi-day period for this table.  Are those two values retests, or is the scan interval actually 0.5 seconds?  If so, then the Date and Time columns do not store the sub-second time resolution of your data-- do you have this information stored in some other column?

The first query I would assume you want would be a subset of the data rows based on datetime interval.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[JimNoel]

Yes the data is acquired every 500msec over muliple days and I also noticed that the date/time columns are not stored correctly when I am able to import the data but I can live with that. 

Is there a reason that Diadem takes so much longer to load the data - in Access running the query takes about 15-20minutes to display the results, however, after letting the PC sit for about 2hrs the data is sometimes still not loaded and status bar does not appear to have moved so I check the task manager and it states that DIAdem is no longer responding (note these are on different databases then the one you are using but are identical in format just not length).

I have even downloaded and tried the SQL Wizard script from NI and tried modifing that for my databases but it limits the number of imports to 200000 entries even though the row count query option shows much higher values and I have not been able to determine where to change this value.  Is there any other plugins for Access databases that might work better then the SQL Reader option or the SQL Wizard or should these work without any issues? 

[JimNoel]

Also, curious on how are you loading the query specifically as I do not recall getting any error messages when attempting to load the query data?

[Brad_Turpin]

Hi Jim,

Sorry for the radio silence the last couple of days.  I was actually the original author of the "SQL Wizard" example program, and I never had to implement buffered queries (until now!).  I've spent some time over the last few days adding in the looping and the SQL_FetChNext commands and channel appending, etc., and it seems to work correctly now.  There's still a little weirdness with the status dialog sometimes not displaying, but I thought I'd go ahead and send this on to you anyway.  There's also an error when running it in DIAdem 8.1, but it works fine in DIAdem 10.1 and DIAdem 10.2.  I assume it would work fine in DIadem 9.x, but I haven't tested that yet.

With this updated SQL Wizard SUDialog I can load all 747,338 values from all 20 columns of the LTOE_#141 table in about two and a half minutes on my laptop computer.  The nice thing about using the SQL Wizard is that you're already set up to begin adding query conditions, and it's real easy to quickly get the number of return records for your query conditions or glance at the first 100 values of your query in the preview dialog, etc.

I plan to make more improvements to this example, but these improvements will only run on DIAdem 10.1 and later.  The version attached below should run on all DIAdem 9.x or later versions.  Note that you can add the Date and Time channels together in DIAdem with the Formula Calculator (or the Add function in DIAdem 10.0 or later)-- this will give you a combined DateTime channel that will be optimal for the X-axis of plots.

Let me know how that works for you,
Brad Turpin
DIAdem Product Support Engineer
National Instruments

[JimNoel]

Thanks Brad....I am actually using Diadem 10.1 so I will try the updated SQL Wizard today and let you know how it works for me.

[JimNoel]

Are you using the SUD only or the entire SQL wizard and just replacing the updated SUD with the old one.  If I try to only use the SUD within the dialog editor, I dont see any files to select from.  If I use the SQL Wizard.vbs and modify the database name for my database, then I can select the query but I never see the SQL Wizard SUD as it displays in the editor.  Also, you state on your laptop that you are loading all the data in 2 1/2 minutes but I am having no luck - what is the speed and memory size of your laptop if you dont mind me asking?

[Brad_Turpin]

Hi Jim,

I intended you to replace the old "SQL Wizard.SUD" file with the new one I attached above.  I made no changes to the VBScript file or any of the other files, so you should be able to run the "SQL Wizard.VBS" file that you were running previously, as long as you deleted the old SUDialog file and placed the new one in the same folder.

What do you mean that you "can select the query but never see the SQL Wizard SUD as it displays in the editor"?  How can you select a table or view table (query) from your data base if you don't see the dialog as it displays in the SUD editor?  There are 2 dialogs in the SUDialog file, and the one that shows up on top in the editor is the quick view dialog that you can invoke from the main dialog, but you should be able to see the bottom of the query conditions rows and the error out text control peeking out below the quick view dialog-- they are the bottom part of the main dialog where you select tables.

My laptop is from last summer-- dual 2GHz processors and 2GB RAM, with a 7600 RPM hard drive.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[JimNoel]

Brad

When I said I could not select a query it is because none of the view buttons were clicked for the tree control.  Once I clicked one of those it showed me my tables/views/etc  (Sorry  about the confusion).  Also when I select, for example LTOE_#141 and chose "Show query results in a record dialog" button, it does show the first 100 entries less in less than 1minute.  Also if I select "Current Query Row Count" button it does show my 747338 in less than 1minute.  However, when I actually try the "Load Selected Data" button, it show the dialog box stating "Querying Table 1 of 1, Executing Query 1 of 1" and continues to do so for various amounts of time until a get an error and if I wish to debug it using a script editor - this usually happens around 80% but has happened around 53% according to the dialog box.  The loading aborts from there.  I am running a 3GHz HP desktop with 1.5GB RAM so I dont think I should be having an issue with memory as it is not that different from your setup and even to reach the abort points in the load takes more than 2-3minutes?  Any suggestions?