hey all, thnx for your replies so far. some more details to get into it:
first of all i'm coding a .net application using the ado.net 2.0 provider by robert simpson. i posted there as well but thought it would be more of a core sqlite problem (suspecting memory handling by sqlite or os or ..), maybe i'm wrong :) version info is 1.0.47.1 for the provider containing sqlite 3.5.3 my dev machine runs win xp pro sp2 i detached the file database before running the queries against the :memory: as stated 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% a test case wont be too easy to create, this would be ultima ratio to me do i have to go ultima? :) 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 ); -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
