Changes to make Centralized data smaller: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
← Older editNewer edit →
JMojica (talk | contribs)
CCMDB_legit, admin, approved, Administrators
2,572 edits
mNo edit summary
Line 21: Line 21:
** Julie copied the  [[Legacy_TMS_Data.mdb]] to local where SAS will pull from (08Aug2022 )
** Julie copied the  [[Legacy_TMS_Data.mdb]] to local where SAS will pull from (08Aug2022 )


=== [[Postal_Code_Master table]] ===
There is a [[Postal_Code_Master table]] in [[Centralized data.mdb]]. The table is likely in the correct file because Pagasa maintains it and Julie uses it.


=== [[Postal_Code_Master table]] ===
When extracted to its own file, table is 89MB
There is a [[Postal_Code_Master table]] in [[Centralized data.mdb]]. It’s in this file because Pagasa maintains it, so if it was in [[CFE]] it would get lost if Tina made an update to that file. Removing this table would further shrink the file to 0.721GB .
 
Discussion with Julie established that she doesn't use the following fields, so eliminating them: latitude and longitude, so
{{Todo
| who = Tina
| todo_added = 2022-08-11
| todo_action = 2022-08-11
| question = _after
* reduce field sizes as much as possible
* do some quality control
* remove
** latitude and longitude
}}


{{DJ |
{{DJ |
* Do you actually use this table? If so we might need to keep it in Centralized so you get any updates Pagasa makes. [[User:Ttenbergen|Ttenbergen]] 16:38, 2022 August 10 (CDT)
...
* The table has a lot of fields that are only populated for MB, an not even consistently there. Do you use any of these fields for aggregation? Do you use this table at all when you do geographic analysis, or do you have a different one? They are all 255char fields, so if there are any we don't use we should consider eliminating them. There are also some erratic entries in there, eg. latitudes and longitudes missing their decimal points, or rhacode and province blank... do we need to review this? [[User:Ttenbergen|Ttenbergen]] 16:38, 2022 August 10 (CDT)
** yes I use the table  and particularly helpful for MB where the RHA are listed. ...  ---[[User:JMojica|JMojica]] 09:44, 2022 August 11 (CDT)  
** yes I use the table  and particularly helpful for MB where the RHA are listed. i do not use the latitudes or longitudes. if occupying more space, one option is to separate the MB from the rest. maybe save the rest to another accdb but  keep the MB.  besides, new postal codes often occur at WPG/MB.  ---[[User:JMojica|JMojica]] 09:44, 2022 August 11 (CDT)  
*** How about the CA, CA_Name, NC, NC_Name columns - do you use those? If you use the table we will just keep it in there, but I might still reduce the field sizes, they don't need to be 255. [[User:Ttenbergen|Ttenbergen]] 10:52, 2022 August 11 (CDT)
*** How about the CA, CA_Name, NC, NC_Name columns - do you use those? If you use the table we will just keep it in there, but I might still reduce the field sizes, they don't need to be 255. [[User:Ttenbergen|Ttenbergen]] 10:52, 2022 August 11 (CDT)
**** I think I have used it when asked where from winnipeg area patients come from but very rare. - --[[User:JMojica|JMojica]] 11:03, 2022 August 11 (CDT)  }}
**** I think I have used it when asked where from winnipeg area patients come from but very rare. - --[[User:JMojica|JMojica]] 11:03, 2022 August 11 (CDT)  
***** Do you use the code and name fields for them or would you be OK if we use the code fields?   }}


If Pagasa is the only user of this table we could move it into a different file, which would just have to be added to the push pull and reconnect machinery for Pagasa.


==== Next Steps ====
==== Next Steps ====

Revision as of 10:29, 11 August 2022

As of 2022-08-10 Centralized data.mdb was 1.348GB . This is large and slowly approaching MS Access' 2GB limit. The size also makes it slow to copy down, and even slower to copy up. This page is about attempts to make the file smaller.

L_Dxs table and L_Como table

We can reduce the file size to 0.810GB by moving the legacy tables L_Dxs table and L_Como table to Legacy TMS Data.mdb. These tables are “stale” and no longer change, so we don’t really need to have them in Centralized data.mdb.

Next Steps
  • after Pagasa has legacy local, remove tables from Centralized master.
  • added: 2022-08-10
  • action: 2022-08-10
  • Cargo


  • Categories

Log

Postal_Code_Master table

There is a Postal_Code_Master table in Centralized data.mdb. The table is likely in the correct file because Pagasa maintains it and Julie uses it.

When extracted to its own file, table is 89MB

Discussion with Julie established that she doesn't use the following fields, so eliminating them: latitude and longitude, so

_after

  • reduce field sizes as much as possible
  • do some quality control
  • remove
    • latitude and longitude
  • added: 2022-08-11
  • action: 2022-08-11
  • Cargo


  • Categories


...

    • yes I use the table and particularly helpful for MB where the RHA are listed. ... ---JMojica 09:44, 2022 August 11 (CDT)
      • How about the CA, CA_Name, NC, NC_Name columns - do you use those? If you use the table we will just keep it in there, but I might still reduce the field sizes, they don't need to be 255. Ttenbergen 10:52, 2022 August 11 (CDT)
        • I think I have used it when asked where from winnipeg area patients come from but very rare. - --JMojica 11:03, 2022 August 11 (CDT)
          • Do you use the code and name fields for them or would you be OK if we use the code fields?
  • SMW


  • Cargo


  • Categories


Next Steps

_after

  • Waiting for info from Julie:
    • Can we move the file out? Ttenbergen 16:38, 2022 August 10 (CDT)
    • Can we reduce field sizes? Ttenbergen 16:38, 2022 August 10 (CDT)
  • added: 2022-08-10
  • action: 2022-08-10
  • Cargo


  • Categories

Log

Related articles

Related articles:
Retrieved from "https://ccmdb.kuality.ca/index.php?title=Changes_to_make_Centralized_data_smaller&oldid=157124"