Networked Database speed issue...

[dbrown78]

I apologize... I am kind of new to programming and have a limited scope of understanding when it comes to databases and sql queries.  I was given the task of creating an application to perform a sorting algorithm and implemented it using LabVIEW 2013, the database toolkit, and a MS Access 2007 database.  The application I wrote worked great when it was first deployed and now that it has been operational for about 2 months, it has slowed significantly. I did some testing and I think I have isolated the latency to network communication issues.  

I believe it is network speed/traffic related because I copied the database locally and sorting was only about 2s - the networked copy takes about 14s!!! Also, the number of users had no impact on this number either - I created a copy of the database, renamed it, saved it to the same folder, pointed my application to that file instead and it was still 14s...  The only noticeable improvement was using a local copy, which will not work for this scenario.  

I spoke to a few people about it and someone suggested changing the cursor location from server based to client based (http://msdn.microsoft.com/en-us/library/windows/desktop/ms676564(v=vs.85).aspx).  Only thing is, I cannot figure out which LabVIEW is using or how to change it... Is it changeable?  If not, do you have any other suggestions for speeding it up?  It is unlikely I will get much support from our IT department, so any improvement suggestions will have to be recommendations for change to my application.

[dkfire]

How big is your MS Access file?

[dbrown78]

As of this morning it is 62MB

[dbrown78]

If it would help, I can post my vis... I'm sure I'm doing a lot of things that can be done much more efficiently.  I just need to clear out any company sensitive info from it first. 

[Yamaeda]

I think Access copies the file in order to sort it, which'd explain the 2 to 14 sec disparancy. You can ofc do stuff like changing the table columns to indexed to speed up sorting and possibly optimize the SQL query, but i'd say the best and easiest solution is to setup a SQL server (e.g. SQL Express). It's quite easy to import the Access file and change the connection string to connect to the real database. 

The difference being that the server does the interpretation and work of the SQL command before sending the result.

However, if you sort and return all rows, it'll still be 14 secs since 62 Mb takes that time to transfer.

/Y

[mikeporter]

I don't understand what you mean by sorting the database. You can never assume the order of data in a set of results unless you specify an ORDER BY clause in the query.

Also, you have the JET data file on a network drive? Also not a good idea since JET databases are not meant to be multiuser. Two users accessing the same file can kill performance.

Yes, you can change the cursor type -- but it might be difficult if you are using the Database Connectivity Toolkit.

Mike...

[mikeporter]

A couple more thing. Here is a link to a thread that discusses an alternative to the DCT - check the last page for a download. Also what does the query look like that you are using to access the data.

For what it's worth, if the sorting operation is complex I have often found it easier to read the entire dataset into LV and sort it there.

You might also consider using something other than Jet to manage the DB.

Mike...

[dbrown78]

I suppose I should have been more clear when I said sort...lol.  The application is used to find a PO for a particular device (sorting to a PO).  There is only one sort operation (a->z) performed in any of my sql queries (see below)

I was concerned about using the database as well - mostly because I'm not as experienced using them...  The original implementation of the application used text files and flags to prevent data collisions, but we have about 10 people sorting at one time and it was creating a huge lag time.

The network location of the database (we believe) is probably what is causing most of the issue.  Our IT dept keeps things very locked down and there weren't many choices for implementation methods.  I was forced to use a shared folder on the network to store the database and this shared folder is accessible to everyone in our facility.  Be believe the general traffic to this server is probably most of the issue we are having.  

when the version 2 sort tool was deployed (the one using the database instead of text files) it took just a few seconds to return a result - on the order of 3-5s.  There are now over 150,000 entries in the database, so I'm sure that too is contributing to the lag.  

The database has several tables:
(1) Log Table: used strictly for logging the device sort data,
(2) Liquidation Table: lists all of the possible sort locations and the device requirements for each PO,
(3) Activity Log - used for capturing devices which shouldn't be sorted or other device condition based exceptions,  
(4) Allocation table - for each model it lists whether the device should be sorted to a PO based on the priority of the PO or an allocation percentage, and
(5) Requirements table - only 3 entries.  Used to control specific behavior of the sort.

I have several SQL queries in the code that hit this database...  

1. Check the requirements table.  The flags reside in the table to (1) stop the sorters from being able to sort devices temporarily and (2) a flag to force all devices to be cleared and/or graded before they can be sorted.  

SELECT Requirements.Requirement, Requirements.Enable FROM Requirements
WHERE (((Requirements.[Start])<Now()) AND ((Requirements.[End])>Now()));

2. Check the log to see if the device was already sorted - if it is, it flags that log entry so it is not counted against a PO and can be resorted.  

SELECT ID  FROM LQD_Log

WHERE PO_ID > -2 AND ITEM_ID ='[ITEM ID]'

If not null, a case statement updates the PO_ID for that log entry to a value that will not count against any open POs.  

3. The next query is a little more complicated.  Depending on the device condition, several case statements build the SQL query string to find a PO the device can be sorted to.  Since there could be multiple POs returned, I have it return the POs (at most 5 or so) sorted by priority.  (there are 13 elements for each PO returned).

4.  The next query checks the allocation table to see if the that model is to be sorted based on priority of the PO or by an allocation percentage.  

SELECT Percent  FROM Allocation_Table

WHERE CNN ='[COLOR NEUTRAL NAME OF DEVICE]'

A PO is selected in my LabVIEW code based on the POs returned and the allocation method. 

The PO (Sort location) is displayed to the user.  they scan the sort location and then I write the information to the log in the database - Pertinent device characteristics related to the sort decision, PO Information, user information and scanned location (21 elements).  

There is very little which can be done about the location of the file... it has to be in a shared location, and it has to be able to support multiple user access.  One of the programmers here recommended using an Access database for this reason and many others.  I could possibly lock it down with flags again, but I'm afraid that would only contribute to the problem - I did test a copy of the database to the same network location and I saw the same types of delays, so I really do not believe it is related to multiple users accessing the file. 

[johnsold]

These will probably be considered heretical suggestions.

Why not make IT your ally? Ask them if they support (and operate) any databases for other organizations in your company. IT departments usually have someone who understands databases, even if they do not run any themselves. If they do support and operate databases, see if they will take over yours - and still allow you to have programmatic access to it through LV. They will know nothing about LV of course.  They will know about how to communicate with the database software from other programs.

I was not completely successful in understanding your description of the operation. You keep referring to "sorting to a PO" but it sounds more like you should be searching for records associated with that PO. That operation is one for which most databases are optimized because it is done very frequently. I wonder if you are under-utilizing the power of the database.  LV is slow at searching and sorting large strings or arrays of strings. If you can push this part of the job onto the database software and only return to LV the much smaller subset of data that you really need, things might be much faster.

Lynn

[dbrown78]

yes...it really is matching a device to a PO...  from a user standpoint it is a sorting operation...they get a bin of devices and they use the tool to sort them to a particular PO.

I spoke to the head of our IT dept here and she stated something similar to what you said.  The unfortuneate (depending on persepctive) thing is that our business is growing at an exponential rate and they just don't have enough people to support this yet.  Plus, they are also planning on integrating my "sorting" logic into our systems, but that isn't going to be for another 6 months or so.  They initally wanted to do a kanban system with spreadsheets for this and I said I could come up with something better than that.  They liked my solution so much they've delayed development on their side!!!!   And their solution to the sorting backlog is to throw more users at it...which doesn't help either...

The most intensive part of my algorithm is taking the device criteria and choosing a PO for it - which is done using a SQL query string.   I tried to do as much as I could programmatically in LabVIEW because even though i'm not a programmer, and I know even less SQL.  I don't want to have to hunt down someone in IT when I have a question as to why the tool isn't acting the way I expect it to. 

I've stripped my code down some and attached it here.  ... I know I'm not a programmer so there are probably a million things I could have done better...  and please forgive the spaghetti - sometimes the block diagram clean up works well...other times - not so much, lol