Saving Pictures Inside the DB?

Hi,
I have a database that stores a lot of searchable data (I mean INT and
VARCHAR and ...) but also huge number of pictures are added regularly. My
estimation is that after a few months (when all clients become active), at
least 4GB of pictures will be inserted daily. So after 3 years the database
reaches to 4TB!
I'd like to know that whether this is a right way that we are going or not?!
I mean what will happen to size of our backups?
Some people suggest to store pictures outside and keep the path in rows.
I would be grateful if someone could evaluate these two methods.
Thanks in advance,
Leila
GOOGLE 'storing files database'.
Here's are 2 sites that discuss both options:
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||When SQL Server 2008 is released it will offer a better alternative using
the FILESTREAM data type:
http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||You may also want to check out this paper;
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Linchi
"Leila" wrote:

> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
>
|||Leila we use the database only to store the Thumbs and show them in the grid
when the client do a search, ours thumbs are under 2kb and our database
start becaming very big 17millions of records so far, another issue will be
your transacion log, think about people updating or deleting the images,
that will create a HUGE transaction log file but you can solve that using
"writetext".
Remember this: if you are going to show only 1 image at the time you can
save the image path on the row and always load the image and show in the
client.
Last thing to consider is your network traffic, you don't want huge images
going forward and backward in your network
hope this will help you
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||I appreciate everybody!
We won't have too much of manipulations over pictures. What if we store
pictures in another database within the same SQL Server instance? This way
we can have: transactional consistency, replication, smaller size for
backing up the main database.
Any ideas?
Thanks again.
"Jorge" <nospam@.austin.rr.com> wrote in message
news:480638d2$0$31736$4c368faf@.roadrunner.com...
> Leila we use the database only to store the Thumbs and show them in the
> grid when the client do a search, ours thumbs are under 2kb and our
> database start becaming very big 17millions of records so far, another
> issue will be your transacion log, think about people updating or deleting
> the images, that will create a HUGE transaction log file but you can solve
> that using "writetext".
> Remember this: if you are going to show only 1 image at the time you can
> save the image path on the row and always load the image and show in the
> client.
> Last thing to consider is your network traffic, you don't want huge images
> going forward and backward in your network
> hope this will help you
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
>
|||Just curious, are you EVER going to fix your system clock? I have asked you
multiple times to correct this, and the fact that you are ignoring my
requests demonstrates that you are doing it intentionally. This is very
inconsiderate.
On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
"Leila" <Leilas@.hotpop.com> wrote:

> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
|||I also have a problem with the system date\time. It just does not remember
the date\time after a short while. I guess it's because of the BIOS battery
(even if this laptop is just 2,5 years old?) of my laptop. However I keep it
up to date using Clock Synchronization of Windows.
Maybe Leila also has a problem like this and does not know how to work
around it.
Ekrem nsoy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:C42D4D61.DFC%ten.xoc@.dnartreb.noraa...
> Just curious, are you EVER going to fix your system clock? I have asked
> you
> multiple times to correct this, and the fact that you are ignoring my
> requests demonstrates that you are doing it intentionally. This is very
> inconsiderate.
>
>
> On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
> "Leila" <Leilas@.hotpop.com> wrote:
>
|||If dangling relationships concerns you:
http://sql-server-performance.com/Community/forums/p/26369/144268.aspx#144268
Be sure to put images in a separate filegroup though.
/k
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>

Saving Pictures Inside the DB?

Hi,
I have a database that stores a lot of searchable data (I mean INT and
VARCHAR and ...) but also huge number of pictures are added regularly. My
estimation is that after a few months (when all clients become active), at
least 4GB of pictures will be inserted daily. So after 3 years the database
reaches to 4TB!
I'd like to know that whether this is a right way that we are going or not?!
I mean what will happen to size of our backups?
Some people suggest to store pictures outside and keep the path in rows.
I would be grateful if someone could evaluate these two methods.
Thanks in advance,
LeilaGOOGLE 'storing files database'.
Here's are 2 sites that discuss both options:
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||When SQL Server 2008 is released it will offer a better alternative using
the FILESTREAM data type:
http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||You may also want to check out this paper;
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Linchi
"Leila" wrote:
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
>|||Leila we use the database only to store the Thumbs and show them in the grid
when the client do a search, ours thumbs are under 2kb and our database
start becaming very big 17millions of records so far, another issue will be
your transacion log, think about people updating or deleting the images,
that will create a HUGE transaction log file but you can solve that using
"writetext".
Remember this: if you are going to show only 1 image at the time you can
save the image path on the row and always load the image and show in the
client.
Last thing to consider is your network traffic, you don't want huge images
going forward and backward in your network
hope this will help you
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||I appreciate everybody!
We won't have too much of manipulations over pictures. What if we store
pictures in another database within the same SQL Server instance? This way
we can have: transactional consistency, replication, smaller size for
backing up the main database.
Any ideas?
Thanks again.
"Jorge" <nospam@.austin.rr.com> wrote in message
news:480638d2$0$31736$4c368faf@.roadrunner.com...
> Leila we use the database only to store the Thumbs and show them in the
> grid when the client do a search, ours thumbs are under 2kb and our
> database start becaming very big 17millions of records so far, another
> issue will be your transacion log, think about people updating or deleting
> the images, that will create a HUGE transaction log file but you can solve
> that using "writetext".
> Remember this: if you are going to show only 1 image at the time you can
> save the image path on the row and always load the image and show in the
> client.
> Last thing to consider is your network traffic, you don't want huge images
> going forward and backward in your network
> hope this will help you
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a database that stores a lot of searchable data (I mean INT and
>> VARCHAR and ...) but also huge number of pictures are added regularly. My
>> estimation is that after a few months (when all clients become active),
>> at least 4GB of pictures will be inserted daily. So after 3 years the
>> database reaches to 4TB!
>> I'd like to know that whether this is a right way that we are going or
>> not?! I mean what will happen to size of our backups?
>> Some people suggest to store pictures outside and keep the path in rows.
>> I would be grateful if someone could evaluate these two methods.
>> Thanks in advance,
>> Leila
>|||Just curious, are you EVER going to fix your system clock? I have asked you
multiple times to correct this, and the fact that you are ignoring my
requests demonstrates that you are doing it intentionally. This is very
inconsiderate.
On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
"Leila" <Leilas@.hotpop.com> wrote:
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||I also have a problem with the system date\time. It just does not remember
the date\time after a short while. I guess it's because of the BIOS battery
(even if this laptop is just 2,5 years old?) of my laptop. However I keep it
up to date using Clock Synchronization of Windows.
Maybe Leila also has a problem like this and does not know how to work
around it.
--
Ekrem Önsoy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:C42D4D61.DFC%ten.xoc@.dnartreb.noraa...
> Just curious, are you EVER going to fix your system clock? I have asked
> you
> multiple times to correct this, and the fact that you are ignoring my
> requests demonstrates that you are doing it intentionally. This is very
> inconsiderate.
>
>
> On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
> "Leila" <Leilas@.hotpop.com> wrote:
>> Hi,
>> I have a database that stores a lot of searchable data (I mean INT and
>> VARCHAR and ...) but also huge number of pictures are added regularly. My
>> estimation is that after a few months (when all clients become active),
>> at
>> least 4GB of pictures will be inserted daily. So after 3 years the
>> database
>> reaches to 4TB!
>> I'd like to know that whether this is a right way that we are going or
>> not?!
>> I mean what will happen to size of our backups?
>> Some people suggest to store pictures outside and keep the path in rows.
>> I would be grateful if someone could evaluate these two methods.
>> Thanks in advance,
>> Leila
>>
>|||If dangling relationships concerns you:
http://sql-server-performance.com/Community/forums/p/26369/144268.aspx#144268
Be sure to put images in a separate filegroup though.
/k
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||> I also have a problem with the system date\time. It just does not remember
> the date\time after a short while. I guess it's because of the BIOS
> battery (even if this laptop is just 2,5 years old?) of my laptop. However
> I keep it up to date using Clock Synchronization of Windows.
> Maybe Leila also has a problem like this and does not know how to work
> around it.
No, Leila has been told about this multiple times, and her system clock is
always magically 24 hours ahead. If it were a BIOS battery issue, the clock
should lose time, so she should be posting in the past in that case, not the
future. Future posting is a slimy way to keep your post at the top of the
message list when you sort by sent descending, since the server obeys the
date/time in the headers of your message, as opposed to the date/time they
actually received the post.|||Hi Aaron,
Thank you for mentioning this issue. Actually I had checked this multiple
times before and my clock was fixed. It was a long time that I didn't come
to this newsgroup and I wasn't aware that this problem has turned up again.
I have the same problem with my Pocket PC, sometimes my reminder displays my
tasks of tomorrow and I realize that my PPC's clock is wrong. Probably this
happens when I sync my PPC with notebook.
I guess some software which is resident in the memory is doing this due to
some wrong setting, but really have no idea what can this be.
I apologize again!
Leila
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:7971D90C-DBB1-43FB-92AA-95B8959A9B04@.microsoft.com...
>> I also have a problem with the system date\time. It just does not
>> remember the date\time after a short while. I guess it's because of the
>> BIOS battery (even if this laptop is just 2,5 years old?) of my laptop.
>> However I keep it up to date using Clock Synchronization of Windows.
>> Maybe Leila also has a problem like this and does not know how to work
>> around it.
> No, Leila has been told about this multiple times, and her system clock is
> always magically 24 hours ahead. If it were a BIOS battery issue, the
> clock should lose time, so she should be posting in the past in that case,
> not the future. Future posting is a slimy way to keep your post at the
> top of the message list when you sort by sent descending, since the server
> obeys the date/time in the headers of your message, as opposed to the
> date/time they actually received the post.

Saving cumulative data or something like that :)

Hi,
There are 3 tables
Table,
TableDetails,
TableDaily.

With structure

TABLE:

TableID UserID Money
---- ---- ----
(int) (int) (money)

TABLEDETAILS:

TableDetailsID TableID ItemID PaidForItem DayID
---- ---- ---- ---- ----
(int) (int) (int) (money) (int)

TABLEDAILY:

TableDailyID TableID PaidForItem Money Total Change
---- ---- ---- ---- ---- ----
(int) (int) (money) (money) (PaidForItem + Money) (money)

"Table" holds id for user and his money amount, which changes during time. "TableDetails" holds data about items user bought, amount paid for them and dayid which relates to one particular day.

"TableDaily" holds history. I do not know how to update this table.
I created job whish runs stored procedure. This procedure sums "PaidForItem" using group by TableID and WHERE DAYID = '11'.

Problem is with Change column. This column sould hold difference between today's Total and previous one etc.

Current procedure looks like this:
INSERT INTO TableDaily
(TableID, PaidForItem, Money, DayID)
SELECT TableDetails.TableID,
SUM(PaidForItem) AS PaidForItem,
Table.Money,
(SELECT DayID
FROM Days
WHERE (Aktive = 1)) AS DayID
FROM TableDetails INNER JOIN
Table ON TableDetails.TableID = Table.TableID
GROUP BY TableDetails.TableID, Table.MoneyShould TABLEDAILY have a DayId column? Or maby even 2 since Day table has an active column and difference could be calculated over several calender days.|||

Hi,

Try the following code for finding the change, u can add this to your exisiting SP

1declare @.changemoney2select @.change=t2_total-t1_totalfrom3(select t1.Total , t2.Total4from TABLEDAILYas t15leftouter join TABLEDAILYas t26on t2.TableDailyID= (select top 1 TabledailyIDfrom TABLEDAILY7where TabledailyID > t1.TabledailyID8and DayID=@.DayID)where t1.DayID=@.DayID) tab3

This is the general idea, here you can update the TABLEDAILY with @.change for the corresponding @.DayID..

|||

Hi, thanks for trying to help.

Yes, there should be one column with DayID. There is no need for 2, because I will use TabelDaily to calculate changes on demand for periods like week, month etc. For now I just want to have values from last day and changes compared to day before.

Besides column Change there is RelativeChange which will hold percentage value, but that is something that would be easy to implement once when I solve original problem.

Sargable Rewrite

Is there a way to rewrite the following Where clause to make it Sargable?
DECLARE @.dtmNow datetime
DELCARE @.days int
WHERE DateDiff(d, cr.CreatedAt, @.dtmNow) >= @.days
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200702/1"cbrichards" <u3288@.uwe> wrote in message news:6d5e19f0998c9@.uwe...
> Is there a way to rewrite the following Where clause to make it Sargable?
> DECLARE @.dtmNow datetime
> DELCARE @.days int
>
> WHERE DateDiff(d, cr.CreatedAt, @.dtmNow) >= @.days
>
something like
WHERE cr.CreatedAt <= dateadd(d,-5,@.dtmNow)
David|||On Feb 5, 4:24 pm, "cbrichards" <u3288@.uwe> wrote:
> Is there a way to rewrite the following Where clause to make it Sargable?
> DECLARE @.dtmNow datetime
> DELCARE @.days int
> WHERE DateDiff(d, cr.CreatedAt, @.dtmNow) >= @.days
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200702/1
WHERE cr.CreatedAt < DATEADD(day, @.days, @.dtmNow)