hexagon logo
Register Sign in
  • Replies 12 replies
  • Subscribers 10 subscribers
  • Views 261 views
  • Users 0 members are here
Options
Related

DataPage+ 5.0

Capa!
Reaching out as I'm currently looking for a workaround regarding large amounts of data.

We have close to 40 CMMs here running 24/7 and STATS ON/OFF'ing into a database on a SQL Server. It's been a couple weeks since a project was implemented at one of our remote facilities and I've stumbled across a problem. This query is coming out of PCDMIS as these programs run and is taking a very long time to complete,

SELECT "Transaction_SID","Variable_SID","Data_Value","Exclude" FROM "Transaction_Data"

There are currently 903995 records in that table and it's rapidly increasing. Every time a part is ran with STATS ON/OFF it is running this query - which is adding around ~15 seconds to complete. This will only get worse as more records are added. The funny thing is I can't see a reason to return Every value ever entered into this table.

Has anyone run across something like this before and if so how have you handled this?
  • Reaching out as I'm currently looking for a workaround regarding large amounts of data.

    We have close to 40 CMMs here running 24/7 and STATS ON/OFF'ing into a database on a SQL Server. It's been a couple weeks since a project was implemented at one of our remote facilities and I've stumbled across a problem. This query is coming out of PCDMIS as these programs run and is taking a very long time to complete,

    SELECT "Transaction_SID","Variable_SID","Data_Value", "Exc lude" FROM "Transaction_Data"

    There are currently 903995 records in that table and it's rapidly increasing. Every time a part is ran with STATS ON/OFF it is running this query - which is adding around ~15 seconds to complete. This will only get worse as more records are added. The funny thing is I can't see a reason to return Every value ever entered into this table.

    Has anyone run across something like this before and if so how have you handled this?

    Can you edit the Query?

    If so adding a WHERE clause can drastically speed that up.

    Why would Pc-Dmis be query-ing something? Is this something custom?
  • No. It's PCDMIS's STATS command that is running this (I think). The SPID's program name field has "Hexagon Manufacturing Intelligence " as its value.

    These SPID's are also originating from the computers at the CMM machines. It's all newly setup and the only thing interacting with the database is PCDMIS via STATS ON/OFF.
  • We used to have issues with stats on/off taking to long to send data. We switched to a external command like this....
    EXTERNALCOMMAND/NO_DISPLAY, NO_WAIT ; C:\PROGRAM FILES (X86)\HEXAGON\DATAPAGE+ 5.2\DATAPAGESTATS.EXE -L
    Adding the -L to the end means its listening and gathering info as the prg runs, so when its done, the file is already built and ready to send to datapage. Maybe this will work for you?
  • Just an update.

    @2013_junkie
    Yeah one of the CMM guys mentioned something about this. It's my backup plan ATM as it will require us to touch all the programs either by hand or via COM PCDLRN. Thanks for the info.

    Right now I've set up a special schema on the database side that we're applying to CMM's users only. It has a view on it with the same name (Transaction_Data) and limits the select via a "TOP 1". This means that those queries are only returning one row at the CMMs when this query runs - which is pretty regularly. We've been testing to make sure nothing was broken by doing this and everything has looked positive so far. It is kind of insane that STATS ON/OFF queries the entirety of Transaction_Data.
  • STATS ON/OFF queries the entirety of Transaction_Data.

    Why does it query? It's 'uploading' data from a finished routine run, right? If so that is weird.



  • Why does it query? It's 'uploading' data from a finished routine run, right? If so that is weird.



    This is exactly why I'm posting here. It doesn't make sense. Limiting that query doesn't affect the output over to the database as far as we've tested so I have no clue what it's doing with all that information. This query is coming from a ton of different versions of PCDMIS as well, including 2018 R2.

  • I am having the same, if not very similar, issue with Datapage+. My CMM program runtimes are 3-4 times longer when ran with stats on (a program that normally runs 20 mins could take an hour or longer, my 1 hour programs now take 3-4 hours). Can someone explain how to setup this special schema on the database side? I am trying to avoid having to change to an external command if I can help it. 

  • in reply to Zachary Hicks

    Are you using the offline file via the Stats/On command?

  • in reply to Chris Fleshood

    No, we use online connection. My predecessor setup all of the programs with an online connection to DataPage. I have tested a couple programs with swapping to an offline file and having the Data Importer sweep the files into the database. It does work and does not slow down the programs, but for the same reason I don't want to swap to an external command (we have thousands of programs I would have to update) I would prefer to setup a schema on the database to help fix the connection. I just don't know how to do that yet. 

  • in reply to Zachary Hicks

    Yeah that would be the problem.  That function of the Stats/On database direct connection command hasn't really had much support in a long time as we switched to the offline file method a long time ago.  Let me also recommend if you ever switch, do not use the External Command, just check the box for the Offline in the Stats/On command.

  • in reply to Chris Fleshood

    I guess for now I will just dedicate some portion of my day to changing over all programs to create an offline file. Do you know of a way to change the default location where it creates the offline file?

  • in reply to Zachary Hicks

    At the end of the routine.  Add a Stats/Off command, followed by a Stats/Transfer command, and you can send it wherever you like.

© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement