hexagon logo
Register Sign in
  • Replies 12 replies
  • Subscribers 10 subscribers
  • Views 323 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?
Parents
  • 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.
Reply
  • 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.
Children
No Data
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement