large favour please

marc2003

marc2003

marc2003

Soldato
Joined
6 Feb 2004
Posts
20,884
Location
England
can a few people download this zip, extract the contents and run the batch file contained within.

EDIT: linky removed. problem solved. :)
 
Last edited:
you can open the batch file in notepad. it references an .exe that i've included. if people don't trust that, they can download it from here...

http://www.sqlite.org/download.html (first entry under precompiled binaries for windows)

you really think i have bad intentions given how long i've been a member here?
 
All I get is an invalid zip file of 104KB in size?

Got two VM's ready - one Win7, one XPSP3

Ah - more posts whilst I'm writing....
 
Last edited:
Right - i7-920@stock.

(VM)Win7: 6.33sec
(VM)XPSP3: 17.21sec
(RL)Win7: 12.73 whilst shutting VMs down on same HDD

Single run in each

Let meknow if you want me to be more rigorous :)
 
thanks for the replies guys.

Andre, is that windows 7 32bit or 64bit? looking at beenom's time and my own, both using 64bit, it's taking considerably longer than your times (and my XP results)
 
Windows 7 x64 / Q6600 (2.4GHz) / 8GB ram - 6m 21s
Windows Server 2008 R2 x64 / Quad Xeon E5345 (2.33GHz) / 32GB ram - 0m 4s.

It seems like SQLite is waiting for ages while it tries to load the file into ram before executing it. When it's actually working it does it near instantly (as it should, it's just a few insert statements!) but there's a huge pause first.

Edit:

Just ran it in a transaction and it finished in 0.33 seconds (yes, really) on the same Windows 7 machine above. Is that a feasible change to increase your speed?

Code: 
BEGIN TRANSACTION;
	INSERT OR REPLACE INTO quicktag(url,subsong,fieldname,value) VALUES("2400150164","-1","LASTFM_PLAYCOUNT_DB","247");
	INSERT OR REPLACE INTO quicktag(url,subsong,fieldname,value) VALUES("3525150206","-1","LASTFM_PLAYCOUNT_DB","211");
	...
COMMIT;

I only just realised it was INSERT OR REPLACE, that has to do a select before each insert to see if the data exists, no? That'd be a lot of data to reference, even with your index on url, subsong and fieldname.

Further, it's defaulting to delete for the transaction journal. When you change this to memory
Code: 
PRAGMA journal_mode = MEMORY;
The same windows 7 machine did the job in 1m 4s. Although if you can use transactions, it's a bit of a moot point as it's just the one journal file then.

Essentially it was creating a new journal file for each command, then deleting it afterwards to commit it. That's a lot of file IO.
 
Last edited:
that's fantastic. thank you very much. :cool:

i can add that to the sql file, no problem at all. :)

as you can tell i have no idea what i'm doing. :o
 
marc2003 said:
thanks for the replies guys.

Andre, is that windows 7 32bit or 64bit? looking at beenom's time and my own, both using 64bit, it's taking considerably longer than your times (and my XP results)
Click to expand...

Win7 is 64-bit, XP is 32-bit

Thought I had an XP x64 VM, but I seem to have removed it now :o

Couple of further runs on main machine they roll in at 16.48 and 15.15, on XP VM they roll in at 8.22/8.12 weird?

Oh - all VMs running with two processors available if that makes any difference

Missing factd that might be influencing things, I have 12GB of RAM, and it is running on an Samsung F1 1TB drive - don't know whether that changes anything
 
Last edited:
i've been testing much larger datasets with Moredhel's advice and it barely takes a second. this thread is purely the result of my own incompetence. :D

but thanks for taking the time everyone. :)
 
You must log in or register to reply here.
Back
Top Bottom