Stored Images in SqLite is taking 2x more space

Question

I want to store jpg images in a SqLite database and I am using this code at the moment:

public byte[] ImageToByte(Image image, System.Drawing.Imaging.ImageFormat format)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                image.Save(ms, format);
                byte[] imageBytes = ms.ToArray();
                return imageBytes;
            }
        }
void btn_click()...
{
                photo = new Bitmap("invoker.jpg");
                pic = ImageToByte(photo, System.Drawing.Imaging.ImageFormat.Jpeg);
                SaveImage(pic);
}

UPDATE:

void SaveImage(byte[] imagen)
        {
            string conStringDatosUsuarios = @" Data Source = \bang.sqlite3 ;Version=3";
            SQLiteConnection con = new SQLiteConnection(conStringDatosUsuarios);
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("INSERT INTO tbl_pictures (record_id, pic) VALUES ('1', @0);");
            SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
            param.Value = imagen;
            cmd.Parameters.Add(param);
            con.Open();

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception exc1)
            {
                MessageBox.Show(exc1.Message);
            }
            con.Close();
        }

How do you determine the size of the image in the DB? `SELECT length(pic) FROM tbl_pictures`? — CL., May 13 '13 at 07:34
@CL. No just checking the size from properties window (right click on file,etc..) Using DB management tools also shows the same. — xperator, May 13 '13 at 18:05
What's the size of what you're adding? I've tested it with a 525897 bytes file and the resulting db size is 529408 bytes, for example. There is an overhead but nothing like a 2X factor. Or are you comparing the size of the resulting image byte[] or the size of the input image file? — Simon Mourier, May 24 '13 at 13:15
@SimonMourier I am comparing the size of the input image ( Let's say 1.76Mb ) with the size of the DB file (After insert) which is about 2x1.76Mb. Here is a pic of output size : http://i.imgur.com/PDfpGmX.png — xperator, May 24 '13 at 13:40
And what's the length of the byte[] resulting from the ImageToByte call? I bet this is the guilty one... — Simon Mourier, May 24 '13 at 14:07
Strange. I can't reproduce the problem, maybe a SQLite version issue? Check my test program here: http://pastebin.com/4HUGQkCR — Simon Mourier, May 24 '13 at 15:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/30578/discussion-between-xperator-and-simon-mourier) — xperator, May 24 '13 at 15:12
@xperator have you considered using H2 database? I find it much more pleasant to work with since it behaves much more like a "real" RDBMS. You wouldn't have those awful `SQLitexxxxx` statements that make your code messy, chaotic, non-portable, and difficult to port to a different DB in the future. Instead if uses the standard `Connection` and `PreparedStatement` and `Statement` etc. H2 also happens to be a pure Java implementation, way cool! — SnakeDoc, May 30 '13 at 14:50
@xperator doh! C# and Java look to damn similar code-wise! You can still use H2 database so long as there is a C# driver, still recommended! — SnakeDoc, May 30 '13 at 14:56

score 2 · Answer 1 · answered May 25 '13 at 14:47

2

I would think that creating a Bitmap from a jpg is the step causing the size increase. Actually, i'm surprised it's just a 2x size increase!

According to wikipedia ( http://en.wikipedia.org/wiki/JPEG#Sample_photographs ), the compression ratio can range from 2.6x to 46x and up. ;)

answered May 25 '13 at 14:47

C.B.

666
3
18

Actually Simon found the real culprit. (You can follow his replies in the first post) The problem was the way I was creating the DB, which was using 3rd party IDE managers. When I create the DB from inside the program, the pictures are saved normally. Too bad he is not replying back anymore. Anyway if you want edit your answer and tell me why there is such behavior when creating the blank DB from 2 different ways. Then I will mark as accepted answer and you get the bounty ;) – xperator May 25 '13 at 17:03
I do not have any experience with SqliteStudio and/or Firefox Sqlite manager, so i don't think i can provide an answer to that. But maybe it's related to the type of logging that goes on in the DB? (I'm thinking of SqlServer's Simple vs Full logging.) – C.B. May 25 '13 at 18:24

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

Well, Simon did find the solution (not the answer). But he didn't post the final answer. Here is the solution:

Create the blank DB using a C# code and inside your .NET program ( Don't use 3rd party to create the DB)

Here is the code snippet he used.

Anyway, I will be still waiting for a complete answer: Why does creating the DB using 3rd party tools makes the sqlite to store images in 2x size of the original? And how does it differ from the DB created inside the .NET

edited May 23 '17 at 11:43

Community

1
1

answered May 31 '13 at 07:32

xperator

2,743
7
34
58

I am not expert, but maybe your 3rd PArty tool is creating nvarchar field, what happen is nvarchar use 2 byte to store on character , as it store unicode. Though you are storing byte stream. Infact in Sample Code to create database, Simon doesn't mention the data type for Pic, which is not good idea, as in that case it take default data type which is may not be a blob and hence it was taking double space. Well that is all my Guess. – Sumit Gupta May 31 '13 at 08:06

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

Maybe something like this should help: Serialize/Deserialize images to store in DB

I referred to this question: How to save image in database using C#

edited May 23 '17 at 12:03

Community

1
1

answered May 12 '13 at 18:11

Niccolò Campolungo

11,824
4
32
39

serializing the image would just covert it to bits... which sort-of happens already when he converts to a byte array. Another way to go would be to run the image's bytes through a Base64 algorithm to shrink it's size down before storing that into the DB. Then reverse back to byte array and then to image when you need to extract it. – SnakeDoc May 30 '13 at 14:53

Stored Images in SqLite is taking 2x more space

3 Answers3