Sapevo che .Transpose(myOut) era limitata a 65k posizioni, per questo avevo parlato di "5-65mila righe di numeri vari"; anche a me era successo di scoprire che le WorksheetFunctions, anche in XL2010, si rifiutano di digerire matrici oltre le 65mila e spicciole righe.
Fortunatamente l' uso di un secondo Array (myOut) e' assolutamente inutile nell' algoritmo che poi ho adottato, potendo quindi inserire l' esito nella stessa matrice iniziale (mySorg), gia' correttamente dimensionata in verticale e pertanto senza bisogno di trasposizione.
Ho voluto anche io fare un confronto tra le varie proposte, per questo ho preparato i file allegato:
http://rapidshare.com/share/24146764AFF ... BEC631D739In colonna B sono presenti 100mila righe di numeri a 4-5 cifre; la colonna C e' quella che sara' usata per i test.
Nel progetto vba sono presenti tutte le macro presentate piu' l' aggiornamento del mio codice per evitare l' uso di Transpose e piu' una variante a una macro di scossa (variante che ritenevo furba, ma che alla prova furba non lo e' stata; vedi N° 6):
1) ANTelabora2: e' la mia macro di ieri (**) che usa la conversione Unicode
2) ANTelabora3: e' la stessa macro senza l' uso di Transpose (**)
3) SC1Elabora: e' la macro di scossa che lavora col doppio loop
4) SC2Ingrassa: e' la macro di scossa che usa le Regular Expressions (la genesi del nome e' ignota)
5) SC3Ingrassa2: e' una variante di scossa che usa la conversione Unicode
6) ElaboraSC1MOD: e' una variante mia alla macro "doppio loop" di scossa (ha un IF in meno nel loop ma e' sistematicamente piu' lenta della macro originale... boh)
Le macro marcate ** compilano prima la colonna B per tante righe quante impostate in B1
Per poter confrontare i tempi di esecuzione in modalita' automatica ho inserito in ogni macro:
verso l' inizio
- Codice: Seleziona tutto
myTim = Timer
LogCol = 10 'il numero varia da 10 a 15, corrispondenti alle colonne J:O
In coda (al posto del MsgBox)
- Codice: Seleziona tutto
Cells(Rows.Count, LogCol).End(xlUp).Offset(1, 0) = Timer - myTim
Questo consente a ogni macro di scrivere in una colonna specifica il proprio tempo di esecuzione.
Nelle colonne J:O sono presenti 6 pulsanti, che attivano ognuna la macro indicata in riga 1 e scrive il risultato nella prima cella libera della colonna.
Questi pulsanti possono essere usati per avere una prima idea di tempi di esecuzione delle singole macro, ma il vero test e' stato fatto con la sequente macro, che si avvia col pulsante "TUTTE", e che tra l' altro congela nella tabella S1:Y12 i tempi di esecuzione calcolati nell' area J3:O12
- Codice: Seleziona tutto
Sub testt()
Dim LogCol As Long, Dimens As Range, I As Long
'size
Range("A1").Select
Application.Calculation = xlCalculationManual
For Each Dimens In Range("S2:S12")
If Dimens = 0 Then Exit For
Range("J3").Resize(30, 6).ClearContents 'Area accumulo risultati
[B1] = Dimens.Value 'imposta N° righe
For I = 1 To [R1]
Call ANTelabora2 '(compila anche N° di righe)
Call ANTelabora3
Call SC1Elabora
Call SC2Ingrassa
Call SC3Ingrassa2
Call ElaboraSC1MOD
Next I
DoEvents
'accumula risultati in col T:
Application.Calculate
Cells(Rows.Count, "U").End(xlUp).Offset(1, -1).Resize(1, 6) = Range("J2:O2").Value
Next Dimens
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub
Cosa fa la macro:
-azzera il contenuto della tabella J3:O30, in cui saranno scritti gli esiti degli N cicli delle 6 macro
-Poi, per ognuno dei valori presenti in S2:S12 (valori che rappresentano il numero di righe con cui collaudare le macro)
-.e per il numero di cicli scritto in R1 (io ho usato 10)
-..lancia ognuna delle 6 macro, che scrivono il tempo di esecuzione nella tabella J3:J12 (se parliamo di 10 cicli)
-Le formule in J2:O2 sommano i valori sottostanti
-.e a completamento dei 10 cicli la somma dei tempi viene copiata da J2:O2 alla tabella in colonna T:Y
Ergo a completamento della macro avremo in T2:Y12 i tempi necessari alle macro per eseguirsi 10 volte (valore in R1) sul numero di righe scritte in colonna S.
Si ricordi che la compilazione di colonna C e' fatta dalle macro 1-2.
INFINE in colonne AA:AG ho creato un riepilogo che potesse confrontare i tempi di esecuzione delle varie macro. Tra i metodi di confronto possibile ho scelto di individuare per ogni riga il tempo migliore e calcolare il delta di ogni risultato rispetto a questo tempo. Quindi 0 corrisponde alla performance migliore, tempi crescenti sono performance peggiori.
Il risultato e' questo grafico:
upload immaginiSull' asse X ci sono il numero di righe usate per il test (io ho provato da 1k a 100k in 11 step); sull' asse Y ci sono i tempi in secondi calcolati come descritto sopra.
Dai grafici sembrerebbe che fino a 50mila righe la conversione Unicode sia la piu' performante; oltre 50mila la macro a doppio loop batte tutti. Le altre sono comprimarie. Comunque parliamo di differenze, in un ciclo 60mila righe, contenute entro 100msec tra la migliore e la peggiore...
Strana la performance della SC3Ingrassa2, che pure usa la stessa tecnica della conversione Unicode; e della ElaboraSC1MOD, che pur con un IF in meno va molto peggio dell' originale (SC1Elabora). L' uso delle RegExp sembra competitivo fino a 60k righe, poi le performance peggiorano velocemente.
Il foglio contiene anche un secondo grafico che pero' e' stato realizzato in condizione meno documentata; non guardatelo.
Ora sarebbe bello trovare quale e' la combinazione che, nell' esercizio attuale, sia la piu' performante sia a bassi che alti numero di righe...
Se volete ripetere il test (e vi pregherei di farlo, per confrontare gli esiti non in termini di tempi assoluti ma di confronto tra le varie condizioni) tenete presente che:
-la macro ANTelabora2 (quella che usa Transpose) non scrive nella colonna dei risultati se il numero righe e' superiore a 65mila (per poterla inserire nel benchmark ho inserito una IF prima della Transpose); quindi i suoi tempi sono fasulli e vanno cancellati a mano nella tabella T:Y, altrimenti risulterebbero i migliori...
-in tabella AB:AG, le celle AB10:AB12 (corrispondondenti ai valori >65mila della suddetta ANTelabora2) sono prive di formule proprio perche' non significative; se pero' cambiate i valori in colonna S bisogna ripristinare alcune o toglierne altre (di formule), in funzione della scaletta che inserirete.
-mentre la tabella J3:J10 viene azzerata prima dell' esecuzione della macro di test, la tabella T2:Yxx deve essere cancellata a mano, altrimenti i dati si accoderanno agli esistenti, risultando slegati dal numero di righe di colonna S.
-la riga J2:O2 non va cancellata in quanto contiene formule.
Se ripetete i test, credo che possa essere sufficiente pubblicare solo le tabelle dei tempi di esecuzione (diciamo R1:Y12. R1 visualizza quanti cicli ogni macro ha fatto nel test; le altre celle sono i tempi di esecuzione per ogni numero di righe testate, colonna S.
So che quanto detto, oltre che detto confusamente, e' tutt' altro che ingegnerizzato, ma il tempo e' scarso (le cognizioni le lasciamo stare...).
Ciao a tutti.