michaelslamet Posted January 3, 2014 Author Share Posted January 3, 2014 ms, I changed something trying to speed it up and have since forgotten what I changed...doh! The solution needs to be re-visited anyway given your new criteria. However, it's 4:00AM here and the wife will be PISSED if I stay up all night, again. kylomas Haha, nevermind, kylomas, go to sleep now I'm not in hurry Thanks a lot! Link to comment Share on other sites More sharing options...
kylomas Posted January 3, 2014 Share Posted January 3, 2014 (edited) michaelslamet, Try this... expandcollapse popup#include <sqlite.au3> #include <array.au3> #include <date.au3> local $data, $st, $tdata, $sSaveUser, $sOut $data &= "user1 :: 3 :: www.abc.com :: Dec-12-2013 11:12:13" & @CRLF $data &= "user2 :: 4 :: www.abc1.com :: Nov-12-2013 11:12:18" & @CRLF $data &= "user3 :: 2 :: www.abc2.com :: Oct-12-2013 10:12:18" & @CRLF $data &= "user2 :: 3 :: www.xyz2.com :: Oct-12-2013 09:12:18" & @CRLF $data &= "user1 :: 2 :: www.rrrxxy.com :: Oct-11-2013 09:12:18" & @CRLF $data &= "user2 :: 1 :: www.abcxxy.com :: Oct-18-2013 10:11:18" & @CRLF ;~ for $1 = 1 to 5000 ;~ $data &= 'user' & stringformat('%04i',random(1,20,1)) & ' :: ' & random(1,150,1) & ' :: ' & 'www.' ;~ for $2 = 1 to random(5,16,1) ;~ $data &= chr(random(97,122,1)) ;~ Next ;~ $data &= '.com :: ' ;~ $data &= _dateadd('D',random(-365,365,1),_nowcalc()) & @crlf ;~ next $st = timerinit() _SQLite_Startup() _SQLite_Open(':memory:') _sqlite_exec(-1,'create table tbl1 (user, cnt, url, dte);') _sqlite_exec(-1,'begin transaction') local $a10 = StringSplit($data,@crlf,3), $aTmp for $1 = 0 to ubound($a10) - 1 $aTmp = stringsplit($a10[$1],'::',3) if ubound($aTmp) < 3 then continueloop _sqlite_exec(-1,'insert into tbl1 values(' & _ _SQLite_FastEscape($aTmp[0]) & ',' & _ _SQLite_FastEscape(stringformat('%03i',$aTmp[1])) & ',' & _ _SQLite_FastEscape($aTmp[2]) & ',' & _ _SQLite_FastEscape($aTmp[3]) & ');') next _sqlite_exec(-1,'end transaction') _sqlite_exec(-1,'create table tbl2 as select user, count(*) as recs from tbl1 group by user;') local $ret,$arows,$irow,$icol $ret = _SQLite_GetTable2d(-1,'select * from tbl1 join tbl2 on tbl1.user = tbl2.user order by recs desc, user desc, cnt;',$arows,$irow,$icol) $a10 = $arows for $1 = 1 to ubound($a10) - 1 if stringlen($a10[$1][0]) = 0 then ContinueLoop if $sSaveUser = $a10[$1][0] then $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2], $a10[$1][3]) & @crlf Else $sOut &= $a10[$1][0] & ' > ' & $a10[$1][5] & @CRLF $sSaveUser = $a10[$1][0] $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2],$a10[$1][3]) & @crlf endif next filedelete('1.txt') FileWrite("1.txt", $sOut) ConsoleWrite(stringformat('Time to run %03i records = %2.4f',$1-1,timerdiff($st)/1000) & @lf) shellexecute('1.txt') func _exit() _SQLite_Close() _SQLite_Shutdown() endfunc I'm sure the SQL is pretty bad but it works in my testing. kylomas edit: moved url count formatting to DB population Edited January 4, 2014 by kylomas michaelslamet 1 Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
michaelslamet Posted January 4, 2014 Author Share Posted January 4, 2014 michaelslamet, Try this... expandcollapse popup#include <sqlite.au3> #include <array.au3> #include <date.au3> local $data, $st, $tdata, $sSaveUser, $sOut ;~ $data &= "user1 :: 3 :: www.abc.com :: Dec-12-2013 11:12:13" & @CRLF ;~ $data &= "user2 :: 4 :: www.abc1.com :: Nov-12-2013 11:12:18" & @CRLF ;~ $data &= "user3 :: 2 :: www.abc2.com :: Oct-12-2013 10:12:18" & @CRLF ;~ $data &= "user2 :: 3 :: www.xyz2.com :: Oct-12-2013 09:12:18" & @CRLF ;~ $data &= "user1 :: 2 :: www.rrrxxy.com :: Oct-11-2013 09:12:18" & @CRLF ;~ $data &= "user2 :: 1 :: www.abcxxy.com :: Oct-18-2013 10:11:18" & @CRLF for $1 = 1 to 500 $data &= 'user' & stringformat('%04i',random(1,20,1)) & ' :: ' & random(1,150,1) & ' :: ' & 'www.' for $2 = 1 to random(5,16,1) $data &= chr(random(97,122,1)) Next $data &= '.com :: ' $data &= _dateadd('D',random(-365,365,1),_nowcalc()) & @crlf next $st = timerinit() _SQLite_Startup() _SQLite_Open(':memory:') _sqlite_exec(-1,'create table tbl1 (user, cnt, url, dte);') _sqlite_exec(-1,'begin transaction') local $a10 = StringSplit($data,@crlf,3), $aTmp for $1 = 0 to ubound($a10) - 1 $aTmp = stringsplit($a10[$1],'::',3) if ubound($aTmp) < 3 then continueloop _sqlite_exec(-1,'insert into tbl1 values(' & _ _SQLite_FastEscape($aTmp[0]) & ',' & _ _SQLite_FastEscape(stringformat('%03i',$aTmp[1])) & ',' & _ _SQLite_FastEscape($aTmp[2]) & ',' & _ _SQLite_FastEscape($aTmp[3]) & ');') next _sqlite_exec(-1,'end transaction') _sqlite_exec(-1,'create table tbl2 as select user, count(*) as recs from tbl1 group by user;') local $ret,$arows,$irow,$icol $ret = _SQLite_GetTable2d(-1,'select * from tbl1 join tbl2 on tbl1.user = tbl2.user order by recs desc, user desc, cnt;',$arows,$irow,$icol) $a10 = $arows for $1 = 1 to ubound($a10) - 1 if stringlen($a10[$1][0]) = 0 then ContinueLoop if $sSaveUser = $a10[$1][0] then $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2], $a10[$1][3]) & @crlf Else stringreplace($data,$a10[$1][0],'') $iCnt = @extended $sOut &= $a10[$1][0] & ' > ' & $iCnt & @CRLF $sSaveUser = $a10[$1][0] $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2],$a10[$1][3]) & @crlf endif next filedelete('1.txt') FileWrite("1.txt", $sOut) ConsoleWrite(stringformat('Time to run %03i records = %2.4f',$1-1,timerdiff($st)/1000) & @lf) shellexecute('1.txt') func _exit() _SQLite_Close() _SQLite_Shutdown() endfunc I'm sure the SQL is pretty bad but it works in my testing. kylomas edit: moved url count formatting to DB population Perfect! And work very fast too! Thanks a lot, kylomas! This: func _exit() _SQLite_Close() _SQLite_Shutdown() endfunc Do we really need that? It's never called through the script anyway. What will happen if we never close and never shutdown the SQLite? Link to comment Share on other sites More sharing options...
Solution kylomas Posted January 4, 2014 Solution Share Posted January 4, 2014 ms, Changed close / shutdown. Don't know if it's necessary but it is a good practice. The function was cloned from other code that does call the function depending on conditions. Took out some unnecessary calcs to speed it up significantly...I'll stop jacking with the code now expandcollapse popup#include <sqlite.au3> #include <array.au3> #include <date.au3> local $data, $st, $tdata, $sSaveUser, $sOut $data &= "user1 :: 3 :: www.abc.com :: Dec-12-2013 11:12:13" & @CRLF $data &= "user2 :: 4 :: www.abc1.com :: Nov-12-2013 11:12:18" & @CRLF $data &= "user3 :: 2 :: www.abc2.com :: Oct-12-2013 10:12:18" & @CRLF $data &= "user2 :: 3 :: www.xyz2.com :: Oct-12-2013 09:12:18" & @CRLF $data &= "user1 :: 2 :: www.rrrxxy.com :: Oct-11-2013 09:12:18" & @CRLF $data &= "user2 :: 1 :: www.abcxxy.com :: Oct-18-2013 10:11:18" & @CRLF ;~ for $1 = 1 to 5000 ;~ $data &= 'user' & stringformat('%04i',random(1,20,1)) & ' :: ' & random(1,150,1) & ' :: ' & 'www.' ;~ for $2 = 1 to random(5,16,1) ;~ $data &= chr(random(97,122,1)) ;~ Next ;~ $data &= '.com :: ' ;~ $data &= _dateadd('D',random(-365,365,1),_nowcalc()) & @crlf ;~ next $st = timerinit() _SQLite_Startup() _SQLite_Open(':memory:') _sqlite_exec(-1,'create table tbl1 (user, cnt, url, dte);') _sqlite_exec(-1,'begin transaction') local $a10 = StringSplit($data,@crlf,3), $aTmp for $1 = 0 to ubound($a10) - 1 $aTmp = stringsplit($a10[$1],'::',3) if ubound($aTmp) < 3 then continueloop _sqlite_exec(-1,'insert into tbl1 values(' & _ _SQLite_FastEscape($aTmp[0]) & ',' & _ _SQLite_FastEscape(stringformat('%03i',$aTmp[1])) & ',' & _ _SQLite_FastEscape($aTmp[2]) & ',' & _ _SQLite_FastEscape($aTmp[3]) & ');') next _sqlite_exec(-1,'end transaction') _sqlite_exec(-1,'create table tbl2 as select user, count(*) as recs from tbl1 group by user;') local $ret,$arows,$irow,$icol $ret = _SQLite_GetTable2d(-1,'select * from tbl1 join tbl2 on tbl1.user = tbl2.user order by recs desc, user desc, cnt;',$arows,$irow,$icol) $a10 = $arows for $1 = 1 to ubound($a10) - 1 if stringlen($a10[$1][0]) = 0 then ContinueLoop if $sSaveUser = $a10[$1][0] then $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2], $a10[$1][3]) & @crlf Else $sOut &= $a10[$1][0] & ' > ' & $a10[$1][5] & @CRLF $sSaveUser = $a10[$1][0] $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2],$a10[$1][3]) & @crlf endif next filedelete('1.txt') FileWrite("1.txt", $sOut) ConsoleWrite(stringformat('Time to run %03i records = %2.4f',$1-1,timerdiff($st)/1000) & @lf) _SQLite_Close() _SQLite_Shutdown() shellexecute('1.txt') michaelslamet 1 Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
michaelslamet Posted January 4, 2014 Author Share Posted January 4, 2014 ms, Changed close / shutdown. Don't know if it's necessary but it is a good practice. The function was cloned from other code that does call the function depending on conditions. Took out some unnecessary calcs to speed it up significantly...I'll stop jacking with the code now expandcollapse popup#include <sqlite.au3> #include <array.au3> #include <date.au3> local $data, $st, $tdata, $sSaveUser, $sOut $data &= "user1 :: 3 :: www.abc.com :: Dec-12-2013 11:12:13" & @CRLF $data &= "user2 :: 4 :: www.abc1.com :: Nov-12-2013 11:12:18" & @CRLF $data &= "user3 :: 2 :: www.abc2.com :: Oct-12-2013 10:12:18" & @CRLF $data &= "user2 :: 3 :: www.xyz2.com :: Oct-12-2013 09:12:18" & @CRLF $data &= "user1 :: 2 :: www.rrrxxy.com :: Oct-11-2013 09:12:18" & @CRLF $data &= "user2 :: 1 :: www.abcxxy.com :: Oct-18-2013 10:11:18" & @CRLF ;~ for $1 = 1 to 5000 ;~ $data &= 'user' & stringformat('%04i',random(1,20,1)) & ' :: ' & random(1,150,1) & ' :: ' & 'www.' ;~ for $2 = 1 to random(5,16,1) ;~ $data &= chr(random(97,122,1)) ;~ Next ;~ $data &= '.com :: ' ;~ $data &= _dateadd('D',random(-365,365,1),_nowcalc()) & @crlf ;~ next $st = timerinit() _SQLite_Startup() _SQLite_Open(':memory:') _sqlite_exec(-1,'create table tbl1 (user, cnt, url, dte);') _sqlite_exec(-1,'begin transaction') local $a10 = StringSplit($data,@crlf,3), $aTmp for $1 = 0 to ubound($a10) - 1 $aTmp = stringsplit($a10[$1],'::',3) if ubound($aTmp) < 3 then continueloop _sqlite_exec(-1,'insert into tbl1 values(' & _ _SQLite_FastEscape($aTmp[0]) & ',' & _ _SQLite_FastEscape(stringformat('%03i',$aTmp[1])) & ',' & _ _SQLite_FastEscape($aTmp[2]) & ',' & _ _SQLite_FastEscape($aTmp[3]) & ');') next _sqlite_exec(-1,'end transaction') _sqlite_exec(-1,'create table tbl2 as select user, count(*) as recs from tbl1 group by user;') local $ret,$arows,$irow,$icol $ret = _SQLite_GetTable2d(-1,'select * from tbl1 join tbl2 on tbl1.user = tbl2.user order by recs desc, user desc, cnt;',$arows,$irow,$icol) $a10 = $arows for $1 = 1 to ubound($a10) - 1 if stringlen($a10[$1][0]) = 0 then ContinueLoop if $sSaveUser = $a10[$1][0] then $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2], $a10[$1][3]) & @crlf Else $sOut &= $a10[$1][0] & ' > ' & $a10[$1][5] & @CRLF $sSaveUser = $a10[$1][0] $sOut &= stringformat(' %-5s %-30s %-20s',$a10[$1][1], $a10[$1][2],$a10[$1][3]) & @crlf endif next filedelete('1.txt') FileWrite("1.txt", $sOut) ConsoleWrite(stringformat('Time to run %03i records = %2.4f',$1-1,timerdiff($st)/1000) & @lf) _SQLite_Close() _SQLite_Shutdown() shellexecute('1.txt') Thank you, kylomas My homework now is learning SQLite and RegExp Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now