Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>
Showing posts with label storing. Show all posts
Showing posts with label storing. Show all posts
Sunday, March 25, 2012
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
Database getting slower for every BLOB object
Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.
Database for storing Polish characters
Hi!
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
install[vbcol=seagreen]
Server[vbcol=seagreen]
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> database
> install
> Server
>
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
install[vbcol=seagreen]
Server[vbcol=seagreen]
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> database
> install
> Server
>
Database for storing Polish characters
Hi!
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter Lo
Peter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>
|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
install[vbcol=seagreen]
Server
>
|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> database
> install
> Server
>
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter Lo
Peter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>
|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
database[vbcol=seagreen]
install[vbcol=seagreen]
Server
>
|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> database
> install
> Server
>
Database for storing Polish characters
Hi!
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> > Hi!
> >
> > We have apps running on a English-based MS SQL Server 2000. One of our
> > clients
> > has asked about if it is possible to store Polish characters in
database
> > whose collation
> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
install
> > their MS SQL Server 2000
> > server using the Collation for Polish language.
> >
> > How about the data types used in tables ? we do not use UNICODE data
> > types such as nvarchar, etc.
> > Would it become a problem for storing polish characters into SQL
Server
> > 2000 which was installed with
> > collation for Polish ?
> >
> >
> > Regards,
> >
> > -- Peter Lo
> >
> >
> >
> >
> >
> >
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
>> Peter,
>> From the SQL BOL:
>> "You can specify collations for each character string column using the
>> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
>> Another option if you're not using UNICODE data types.
>> HTH
>> Jerry
>> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
>> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> > Hi!
>> >
>> > We have apps running on a English-based MS SQL Server 2000. One of
>> > our
>> > clients
>> > has asked about if it is possible to store Polish characters in
> database
>> > whose collation
>> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
> install
>> > their MS SQL Server 2000
>> > server using the Collation for Polish language.
>> >
>> > How about the data types used in tables ? we do not use UNICODE data
>> > types such as nvarchar, etc.
>> > Would it become a problem for storing polish characters into SQL
> Server
>> > 2000 which was installed with
>> > collation for Polish ?
>> >
>> >
>> > Regards,
>> >
>> > -- Peter Lo
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
We have apps running on a English-based MS SQL Server 2000. One of our
clients
has asked about if it is possible to store Polish characters in database
whose collation
is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
their MS SQL Server 2000
server using the Collation for Polish language.
How about the data types used in tables ? we do not use UNICODE data
types such as nvarchar, etc.
Would it become a problem for storing polish characters into SQL Server
2000 which was installed with
collation for Polish ?
Regards,
-- Peter LoPeter,
From the SQL BOL:
"You can specify collations for each character string column using the
COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
Another option if you're not using UNICODE data types.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi!
> We have apps running on a English-based MS SQL Server 2000. One of our
> clients
> has asked about if it is possible to store Polish characters in database
> whose collation
> is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to install
> their MS SQL Server 2000
> server using the Collation for Polish language.
> How about the data types used in tables ? we do not use UNICODE data
> types such as nvarchar, etc.
> Would it become a problem for storing polish characters into SQL Server
> 2000 which was installed with
> collation for Polish ?
>
> Regards,
> -- Peter Lo
>
>
>|||Thanks for quick response.
So, to be able to store Polish characters in MS SQL Server 2000, I have
two options:
Option #1:
1. install database with SQL_Latin1_General_CP1_CS_AS
2. As you suggested, use ALTER TABLE to set all string-columns to use
the
UNICODE data type
Option #2:
1. Install database with collation for Polish language
2. All string columns in tables must use UNICODE data types.
Please confirm. Thanks
-- Peter Lo
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Peter,
> From the SQL BOL:
> "You can specify collations for each character string column using the
> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
> Another option if you're not using UNICODE data types.
> HTH
> Jerry
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
> > Hi!
> >
> > We have apps running on a English-based MS SQL Server 2000. One of our
> > clients
> > has asked about if it is possible to store Polish characters in
database
> > whose collation
> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
install
> > their MS SQL Server 2000
> > server using the Collation for Polish language.
> >
> > How about the data types used in tables ? we do not use UNICODE data
> > types such as nvarchar, etc.
> > Would it become a problem for storing polish characters into SQL
Server
> > 2000 which was installed with
> > collation for Polish ?
> >
> >
> > Regards,
> >
> > -- Peter Lo
> >
> >
> >
> >
> >
> >
>|||Peter,
You can use a UNICODE data type, a column level collation or a database
level collation. Choice would probably be dependent on the number of
columns and tables that will be storing Polish characters.
HTH
Jerry
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:%238NEGCvuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Thanks for quick response.
> So, to be able to store Polish characters in MS SQL Server 2000, I have
> two options:
> Option #1:
> 1. install database with SQL_Latin1_General_CP1_CS_AS
> 2. As you suggested, use ALTER TABLE to set all string-columns to use
> the
> UNICODE data type
> Option #2:
> 1. Install database with collation for Polish language
> 2. All string columns in tables must use UNICODE data types.
> Please confirm. Thanks
>
> -- Peter Lo
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eYsXiouuFHA.2568@.TK2MSFTNGP15.phx.gbl...
>> Peter,
>> From the SQL BOL:
>> "You can specify collations for each character string column using the
>> COLLATE clause of the CREATE TABLE or ALTER TABLE statement."
>> Another option if you're not using UNICODE data types.
>> HTH
>> Jerry
>> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
>> news:%23zxNdKuuFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> > Hi!
>> >
>> > We have apps running on a English-based MS SQL Server 2000. One of
>> > our
>> > clients
>> > has asked about if it is possible to store Polish characters in
> database
>> > whose collation
>> > is SQL_Latin1_General_CP1_CS_AS? Or, shall this client needs to
> install
>> > their MS SQL Server 2000
>> > server using the Collation for Polish language.
>> >
>> > How about the data types used in tables ? we do not use UNICODE data
>> > types such as nvarchar, etc.
>> > Would it become a problem for storing polish characters into SQL
> Server
>> > 2000 which was installed with
>> > collation for Polish ?
>> >
>> >
>> > Regards,
>> >
>> > -- Peter Lo
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
Saturday, February 25, 2012
Database Design Question
Anyone know a good database scheme for storing formatted text? The text will be coming from XML in this type of format:
<text id="01">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
<text id="02">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
... etc
How can I store in a database and keep the headlines, subheads, and paragraphs delimited without just storing the XML tags and keeping everything in order (each "text" may contain multiple paragraphs and subheadings ... )?
I'm just wondering if anyone has done this before and what has worked for them ... just looking for opinions on the most efficient way to accomplish ... thanks!If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.|||Originally posted by smorton
If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.
Thanks for the reply. I'm looking for the most efficient way to set up relationships that will keep my text formatted the same way it is in the XML doc and in the same order. Any ideas?|||Again, why don't you just drop this into a varchar or text field? Do you need to be able to query on different Tags? I don't get why you need to establish relationships to the XML.
blindman|||I need to be able to store other properties about each piece of text ... I guess this could be done with attributes of the XML tags. Is that the best way? Just dump the XML right into the db? Thanks!|||What other attributes?
How do you define a piece of text? One line? All the text between two tags?
What about nested tags?
blindman|||There will be no nested tags ... no need for them at this point. Attributes will be font size, family, color, etc ... and a transition id.
(I'm using ASP to generate XML for Flash ... but I also need to save the everything I write the XML in a database).
So I guess I could just store in the db as follows:
<text id="1">
<head font="arial" size="12" color="000000" transition_id="1">This is the title</head>
<sub font="arial" size="11" color="000000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<sub font="arial" size="11" color="FF0000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="FF0000" transition_id="3">this is a paragraph</para>
</text>
... etc
Does that sound right to you? Thanks!|||(follow up) ... text is everything between 2 tags.|||Well, you could have each line be an independent record containing both the text and the common attributes as separate fields. To keep the lines in order, use an incrementing key.
blindman|||you can also identify all the attributes that define a piece of text and separate them into a set of tables that would contain key-based references, all grouped together by a page reference (if applicable).|||Thanks guys. This is kind of what I was thinking ... just wanted to get an expert's opinion. If I use both the incrementing key and the page id with several related tables then I can keep the data all seperated out into organized tables and carry all the required attributes. Then when I am ready to write to XML, for each page I just do a select across the related tables by page id and order it by the incremented order id.
That brings the text out for each page in the right order with all of the formatting in place.
Sounds right to me. Thanks again!
<text id="01">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
<text id="02">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
... etc
How can I store in a database and keep the headlines, subheads, and paragraphs delimited without just storing the XML tags and keeping everything in order (each "text" may contain multiple paragraphs and subheadings ... )?
I'm just wondering if anyone has done this before and what has worked for them ... just looking for opinions on the most efficient way to accomplish ... thanks!If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.|||Originally posted by smorton
If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.
Thanks for the reply. I'm looking for the most efficient way to set up relationships that will keep my text formatted the same way it is in the XML doc and in the same order. Any ideas?|||Again, why don't you just drop this into a varchar or text field? Do you need to be able to query on different Tags? I don't get why you need to establish relationships to the XML.
blindman|||I need to be able to store other properties about each piece of text ... I guess this could be done with attributes of the XML tags. Is that the best way? Just dump the XML right into the db? Thanks!|||What other attributes?
How do you define a piece of text? One line? All the text between two tags?
What about nested tags?
blindman|||There will be no nested tags ... no need for them at this point. Attributes will be font size, family, color, etc ... and a transition id.
(I'm using ASP to generate XML for Flash ... but I also need to save the everything I write the XML in a database).
So I guess I could just store in the db as follows:
<text id="1">
<head font="arial" size="12" color="000000" transition_id="1">This is the title</head>
<sub font="arial" size="11" color="000000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<sub font="arial" size="11" color="FF0000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="FF0000" transition_id="3">this is a paragraph</para>
</text>
... etc
Does that sound right to you? Thanks!|||(follow up) ... text is everything between 2 tags.|||Well, you could have each line be an independent record containing both the text and the common attributes as separate fields. To keep the lines in order, use an incrementing key.
blindman|||you can also identify all the attributes that define a piece of text and separate them into a set of tables that would contain key-based references, all grouped together by a page reference (if applicable).|||Thanks guys. This is kind of what I was thinking ... just wanted to get an expert's opinion. If I use both the incrementing key and the page id with several related tables then I can keep the data all seperated out into organized tables and carry all the required attributes. Then when I am ready to write to XML, for each page I just do a select across the related tables by page id and order it by the incremented order id.
That brings the text out for each page in the right order with all of the formatting in place.
Sounds right to me. Thanks again!
Subscribe to:
Posts (Atom)