- Where Developers Learn, Share, & Build Careers


I have 40 tables that look like the following, and each table has 30 million records.

Table rodeata : pk ( carogirdid , time ) 

  range-id Time islampled value -------- --------------------------------------- ----------- - 1 2012-07-01 00: 00: 00.000 0 - & gt; 1 65.36347 1 2012-07-01 00: 00: 11.000 0 80.1672 9 1 2012-07-01 00: 00: 14.000 0 29.1 9 716 1 2012-07-01 00: 00: 25.000 0 - & gt; 1 7.05847 1 2012-07-01 00: 00: 36.000 0 - & gt; 1 98.08257 1 2012-07-01 00: 00: 57.000 0 75.35524 1 2012-07-01 00: 00: 59.000 0 35.35524   
 As of now,  IsSampled  Column is 0 for all records. 
 I need to update the records, so that for each category ID and for each minute range, record with Max (value), min (value) and  IsSampled  in the first record Must be 1 for  
 I have created the following procedural queries, but it takes a lot of time to run. (Approximately 2h 30m for each table)  
  announcement @ start datetime DECLARE @endRange datetime DECLARE @endTime datetime SET @startRange = '2012-07-01 00: 00: 00.000' SET @endTime = '2012-08-01 00: 00: 00.000' WHILE (@startrange & lt; @endTime) Update BEGIN SET @endRange = DATEADD (MI, 1, @tartrange) R1 SET IsSampled = 1 from Rudata R 1 as the JOIN (Selection R2 as MAXValue, MIN (value) as MIN (value), MinValue, MIN ([time]) as the first time from RawData AS r2 to WHERE @startrange & lt ; = [Time] and [time] & lt; @endRange Group by CategoryID) R1.CategoryID = Sample As sample issues. CategoryID and (r1.Value = samples.MaxValue or r1.Value = samples.MinValue OR r1. [Time] = Samples first time) and @start range & lt; = R1. [Time] and r1 [Time] & lt; @endRange SET @startRange = DATEADD (MI, 1, STARTRANGE) END   
 Is there any way to speed up these tables (potentially non-procedural)? Thanks!   
 
 
 I'm not sure how this would be performing, but it is more set-to-its-comparative approach :  
  Announcement @ T table (range id int is not empty, time datetime2 zero, not assemble bit not empty, value not decimal (10,5) zero) Enter @t (class id, time, Isompled, value) value (1, '2012-07-01 00: 00: 00', 0,65.36347), (1, '2012-07-01 t 00: 00: 11.000', 080.1672 9), ( 1, '2012-07-01 t 00: 00: 14.000', 0, 29.19 716), (1, '2012-07-01 t 00: 00: 25,000' , 0.7.05847), (1, '2012-07- 01T00: 00: 36.000', 0, 98.08257), (1, '2012-07-01T00: 00: 57.000', 0,75,3524), (1 , '2012-07-01T00: 00: 59.000', 0,35.35524); As BinnedValues ​​(Select Category ID, Time, IsSampled, Value, DATEADD (minutes, DATEDIFF (min, 0, time), 0) @ Time from time to time, as MinMax (Select Category ID, Time, Asppled, value, timebinn, ROW_NUMBER () value, as per Minipos, as per Minipos (divided by category ID), at ROW_NUMBER (), value div by time order, segment by ID, maxPos, ROW_NUMBER () As (Division divided by ID, Timebie Order By Time) as the oldest BWW) Update Minx Set ISamspled = 1 Where Mnapos = 1 or MaxPass = 1 or select maximum of = 1 @ T to   
 Results:  
  range id time par value ----------- ---------------- ------ --- ------ ----------------------------------- ---- 1 2012-07- 01 00: 00: 00.00 1 65.36347 1 2012-07-01 00: 00: 11.00 0 80.1672 9 1 2012-07-01 00: 00: 14.00 0 29.19716 1 2012-07-01 00: 00: 25.00 1 7.05847 1 2012 -07-01 00: 00: 36.00 1 98.08257 1 2012-07-01 00: 00: 57.00 0 75.35524 1 2012-07-01 00: 00: 59.00 0 35.35524   
 Probably It can be extended if  The Imbein  column can be added as a Gnnakrit column in the table and can be added to the appropriate indexes.  
 It should also be noted that this will mark  maximum  of 3 rows in the form of a sample - even if there is a minimum or maximum value at first, then only once It will be clearly marked, but the next nearest minimum or maximum value will not be. In addition, if multiple rows have the same  value , and it is the minimum or maximum value, then one of the rows will be selected voluntarily.

Comments

Post a Comment

Popular posts from this blog

Python SQLAlchemy：AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'schema' -

I tried to create a new database in my project, but when I run the script, I get this error, I have another project, this type of definition worked before, but now it gets a single error. I am using Python 2.7.8 and the version of SQLAlchemy module is 0.9.8. By the way, using a project flask- SQLAlchemy, it works well, I'm confused. Traceback information is as follows: Traceback (most recent call final): File "D: / Projects / IOM / db_create.py", line 4, & lt; Module & gt; From the models the base file "D: \ Projects \ OO-IM \ models.py", in line 15, in the & lt; Module & gt; Column ( 'followed_id', int (), ForeignKey ( 'user.id')) file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ schema.py", line 369, __new__ schema = metadata. Schema file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ elements.py", line 662, AttributeError in __getattr__ key): Neither 'column' object nor ...
Read more

java - How not to audit a join table and related entities using Hibernate Envers? -

I use Eners to be hibernate to audit my institutions. I have an audit entity, Foo , which includes a list & lt; Bars & gt; However, I do not want to audit the bar institutions, in the form of properties, I have written that: @inti @edicated public class Foo {@JoinTable (name = "T_FOO_BAR", joinColumns = @JoinColumn (name = "FOO_ID"), inverseJoinColumns = @JoinColumn (Name = "BAR_ID")) @ManyToMany (Cascade = Persist) @ Indited (targetAuditMode = RelationTargetAuditMode.NOT_AUDITED ) Public listing & lt; Bars & gt; GetBars () {Return bars; }} Now, I want to retrieve an amendment of foo : AuditReader reader = AuditReaderFactory.get (GetEntityManager ()); FU modification = (FU) reader.CreativeTeA () .entensEtrevision (Foo Class, 42) .getSingleResult (); Unfortunately, when I want to retrieve all the data (i.e. when it loads lazy times ), error me ORA-00942: table or view is not present , because Tried to query this: Sel...
Read more

mongodb - CakePHP paginator ignoring order, but only for certain values -

While pagging my user model in kppp, I can sort with some values, but not others. For example, I can order results from created or email , for example, but username or reputation seems to come back order by arbitrary command for example with a list of users: $ this-> paginate = array ('condition' => array ( 'User.is_active' = & gt; true), 'border' => 24, 'order' => array ('User.created' => DESC)); Works as expected, but $ this - & gt; Paged = array ('conditions' = & gt; array (' user.isIactive '= & gt; true),' border '= & gt; 24,' command '= & gt; array (' user repeats' = ' & Gt; DESC ')); No, no. I first thought that this could be a database issue, but when I search the database directly, sorted as expected. Note: I am using Mangodebi with the Mongo DB plugin of Ichikawa for the KPPHP; Pagnetizing users are used to work properl...
Read more