System.Data.SQLite

hi all,

this is what i'm trying to do:

i load an existing file db into a memory one (create a :memory:-connection, attaching the file db which has ~ 60 mb and then copying its tables and indices and detaching the file db - this works quite great so far, lasts around 5 secs). now i begin a transaction on the in-memory-db and start executing my queries (some 10k inserts, updates and deletes). and this process is horribly (sic!) slow.

in comparison i did all the inserts into an empty memory-db (without cloning the whole file db first) which is faster by around factor 1000...

anybody has a clue what happens there? i even switched off windows virtual memory paging - the same effect. disk io is near zero, it's really the db commands only that causes this huge difference... working on the file db is much faster than on memory given the same process. do i have to pragma sth special (tried almost everything here, too...)

thnx for your help,

pong 

Maybe it's your indexes? 

hey jeffrey, thnx for your reply so far.

some more details to get into it:

version info is 1.0.47.1 for the provider containing sqlite 3.5.3

my dev machine runs win xp pro sp2

i already tried it without indices not affecting speed in a noticeable manner

i dont know anything about sqlites internal structures. to clone the file db into memory i used the method provided on http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase (adapted the Tcl-Implementation sample)

i'm using rather simple update, insert and delete statements - they are all about equally slow samples (only executing these i run into the performance trap too):

INSERT INTO [Broadcast] ( [id], [channelId], [title], [startTime], [endTime] ) VALUES ( @id, @channelId, @title, @startTime, @endTime )

UPDATE [Broadcast] SET [Weekday] = @Weekday, [StartHour] = @StartHour, [BroadcastDay] = @BroadcastDay WHERE [Id] = @Id

UPDATE [Broadcast] SET [channelId] = @channelId, [title] = @title, [startTime] = @startTime, [endTime] = @endTime WHERE [Id] = @Id

the data consists of text and datetime values as well as binary data (small images around 15k each), each using ~half of db space

CPU usage is almost always around 100%

if it helps, the broadcast-table creation sql (denormalization is intended and shouldnt influence the queries above):

CREATE TABLE IF NOT EXISTS Broadcast ( Id INTEGER PRIMARY KEY NOT NULL, Titleid INTEGER DEFAULT NULL, Title TEXT DEFAULT NULL COLLATE NOCASE, RegionId INTEGER DEFAULT NULL, ChannelId INTEGER DEFAULT NULL, StartTime DATETIME DEFAULT NULL, EndTime DATETIME DEFAULT NULL, SeriesId INTEGER DEFAULT NULL, Season INTEGER DEFAULT NULL, Episode INTEGER DEFAULT NULL, Vps DATETIME DEFAULT NULL, CategoryId INTEGER DEFAULT NULL, Genre1Id INTEGER DEFAULT NULL, Genre2Id INTEGER DEFAULT NULL, TipType TINYINT DEFAULT NULL, TitleOriginal TEXT DEFAULT NULL, Subtitle TEXT DEFAULT NULL, Description TEXT DEFAULT NULL COLLATE NOCASE, DescriptionShort TEXT DEFAULT NULL COLLATE NOCASE, HasMedia TINYINT DEFAULT NULL, HasRating TINYINT DEFAULT NULL, AgeMarkIds NVARCHAR(64) DEFAULT NULL, ProductionYearValues NVARCHAR(512) DEFAULT NULL, ProductionCountryIds NVARCHAR(64) DEFAULT NULL, OverallRating TINYINT DEFAULT NULL, TechnicalAttributeIds NVARCHAR(64) DEFAULT NULL, People TEXT DEFAULT NULL, Weekday TINYINT DEFAULT NULL, StartHour TINYINT DEFAULT NULL, BroadcastDay INTEGER DEFAULT NULL );

I'll run some tests on the command-line and see if I get the same results.  Can you send me a sample database?  robert at blackcastlesoft dot com

 hi robert et. al.,

i built a test case (C#) which results in the same findings. updates on the memory table are very slow (maybe depending on system: mine is athlon64 4000+ dual core, 1gb ram, xp pro sp2).

the test case can be found here:

http://85.214.22.71/testcase_memspeed.zip

a sample database to test with is available here (i coded a db-generation script (included) to  generate a new db and create and populate only one table with sample data: this seems to work quite fine... so maybe its the db structure or anything..):

http://85.214.22.71/filedb.zip

greets,

pong