Showing posts with label records. Show all posts
Showing posts with label records. Show all posts

Thursday, March 29, 2012

database speed in SQL server

Hi there,
My program in Delphi have 7 table with about 120,000 records and 200 fields.
I use SQL server for my database.
When my program launchs its speed is too low.
What shall I do to increase its speed?
Regards,
Doroodgarit probably has nothing to do with the number of tables and records in the
database. you can try several things:
* delay creating unnecessary objects and forms, and create them as needed
* delay connecting to the database, and again connect only when needed
* move time-consuming processing to background thread, if possible
* show splash screen, it will make the appearance better :)
in general, minimize the amount of work you perform on load.
btw, those '200 fields' seem pretty much..
dean
"doroodgar" <rdoroodgar@.noornet.net> wrote in message
news:uTKCZCpDFHA.148@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200
fields.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>|||HI doroodgar,
You sound convinced SQL slow your application purely on the fact the you
return tons of records so I'll work with that.
Why do you need that size recordset on application launch ? This confuse me
a bit cause you load the records based on what ?
What does your application do ? Is there some sort of logic to the records
that you return, is it possible that you can narrow it down , you present
120k records on one page anyways... should you maybe try and load 100 at a
time ?
How do you application present the 200 fields ?
Is it a straight select statement that populate your recordset or do you use
joins ?
What's the timing compared to when you run it through QA ?
Hope this will help
Can you give more info regarding your query and application ?
"doroodgar" wrote:

> Hi there,
> My program in Delphi have 7 table with about 120,000 records and 200 field
s.
> I use SQL server for my database.
> When my program launchs its speed is too low.
> What shall I do to increase its speed?
> Regards,
> Doroodgar
>
>|||http://www.sql-server-performance.com
Regards
Mike
"Mal" wrote:
> HI doroodgar,
> You sound convinced SQL slow your application purely on the fact the you
> return tons of records so I'll work with that.
> Why do you need that size recordset on application launch ? This confuse m
e
> a bit cause you load the records based on what ?
> What does your application do ? Is there some sort of logic to the records
> that you return, is it possible that you can narrow it down , you present
> 120k records on one page anyways... should you maybe try and load 100 at a
> time ?
> How do you application present the 200 fields ?
> Is it a straight select statement that populate your recordset or do you u
se
> joins ?
> What's the timing compared to when you run it through QA ?
> Hope this will help
> Can you give more info regarding your query and application ?
> "doroodgar" wrote:
>sql

Sunday, March 25, 2012

database size increased twice

We had to move exsiting database on new server and ran querey copying all re
cords into new table on new server.
System said that new table was created successfully and all records were cop
ied.
We decided to backup this new database, but it failed. Investigating of back
up problem led us to the point where we realised that our actuall database b
ecome 2 times larger - 150GB insted of 75 (original size).
Kind of lost where this additional chunk came from.
Will be thankful for any suggestions,
Alan.Alan,
Did you create new database with the same data device and log device as the
original one?
Did you try to shrink the data files using DBCC?
larry
"ALan" <anonymous@.discussions.microsoft.com> wrote in message
news:49C0FD90-FFFA-4CE5-B8F8-0A43E1E8B22D@.microsoft.com...
quote:

> We had to move exsiting database on new server and ran querey copying all

records into new table on new server.
quote:

> System said that new table was created successfully and all records were

copied.
quote:

> We decided to backup this new database, but it failed. Investigating of

backup problem led us to the point where we realised that our actuall
database become 2 times larger - 150GB insted of 75 (original size).
quote:

> Kind of lost where this additional chunk came from.
> Will be thankful for any suggestions,
> Alan.

Thursday, March 22, 2012

Database Size

I am building a database and I can not explain the size
it is growing to. I made the size of the database file
100GB. I added 28,000 records with a length of 100
bytes. I added 70,000,000 records with a lenght of 65
bytes. I ran out of space in the database.
I assumed 3 or 4 times the record sizes for overhead when
planning my DB. But I still ran out of room. I have the
backup mode set to simple so the Log file doesn't grow.
Any thoughts would be appreciated.Please post DDL, including indexes. Also, how are you inserting the data?
"DB" <daveblair-nospam-@.adelphia.net> wrote in message
news:209a901c45a06$a1f1f9b0$a601280a@.phx
.gbl...
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||DB,
Hmmm, you should only need a database of around 4-5Gb for that amount of
data. How are you inserting the data?
The log file WILL grow if you are inserting data in large batches. It
needs to grow the log file so that it can roll back the batch if it
fails for some reason (like hardware failure).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DB wrote:
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||Here is the DDL for the tables. There are no indexes at
this time. The data is inserted by a VB program using an
ADO connection to the database. The general code is:
tblAlias.Open "tblAlias", cn3, adOpenDynamic,
adLockOptimistic
**Start loop for each record to add
tblAlias.AddNew
tblAlias!BTBID = rsBusiness!BTBID
tblAlias!State = rsPrs!State
tblAlias!BusID = rsPrs!BusID
tblAlias!SiteID = rsPrs!SiteID
tblAlias!Company = Trim(rsPrs!Company)
tblAlias!StreetName = Trim(rsBusiness!StreetName)
tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
tblAlias!Zip5 = rsBusiness!Zip5
tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
& Mid(rsBusiness!Company, 3, 2)
tblAlias!MatchAddress3 = ""
tblAlias.Update
**End loop
DDL for table definitions.
CREATE TABLE [dbo].[tblAlias] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress1] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress2] [char] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblWord] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Word] [char] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

>--Original Message--
>Please post DDL, including indexes. Also, how are you
inserting the data?
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
> news:209a901c45a06$a1f1f9b0$a601280a@.phx
.gbl...
when[vbcol=seagreen]
the[vbcol=seagreen]
>
>.
>|||Given that your tables have no indexes and your inserts are being done on a
row-by-row basis, I have no clue how it's possible that 70,000,000 rows of
data grew to over 100gb. Are you certain that no other processes are using
the database in question? Did all 70,000,000 rows get inserted, or did the
process stop before it reached completion?
<anonymous@.discussions.microsoft.com> wrote in message
news:210e101c45a1c$ffefdb70$a101280a@.phx
.gbl...[vbcol=seagreen]
> Here is the DDL for the tables. There are no indexes at
> this time. The data is inserted by a VB program using an
> ADO connection to the database. The general code is:
> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
> adLockOptimistic
> **Start loop for each record to add
> tblAlias.AddNew
> tblAlias!BTBID = rsBusiness!BTBID
> tblAlias!State = rsPrs!State
> tblAlias!BusID = rsPrs!BusID
> tblAlias!SiteID = rsPrs!SiteID
> tblAlias!Company = Trim(rsPrs!Company)
> tblAlias!StreetName = Trim(rsBusiness!StreetName)
> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
> tblAlias!Zip5 = rsBusiness!Zip5
> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> & Mid(rsBusiness!Company, 3, 2)
> tblAlias!MatchAddress3 = ""
> tblAlias.Update
> **End loop
>
> DDL for table definitions.
> CREATE TABLE [dbo].[tblAlias] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress1] [char] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress2] [char] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblWord] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Word] [char] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> inserting the data?
> when
> the|||Actually, that's my problem. This is a new stand alone
box. There are no users other than myself. I have
roughly 280,000,000 total records to add to the
database. It halted at the 70,000,000 or so because the
database was full.
I'm completely stumped. While I assume there is
significant storage "overhead" associated with database
records, even 2 or 3 times the size should be less than
20GB.
I have a work around - shrink the DB when it maxes out.
Get all that space back, then resume adding records. A
pain but I'll get there.
Thanks for your efforts.

>--Original Message--
>Given that your tables have no indexes and your inserts
are being done on a
>row-by-row basis, I have no clue how it's possible that
70,000,000 rows of
>data grew to over 100gb. Are you certain that no other
processes are using
>the database in question? Did all 70,000,000 rows get
inserted, or did the
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:210e101c45a1c$ffefdb70$a101280a@.phx
.gbl...
at[vbcol=seagreen]
an[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagr
een]
size[vbcol=seagreen]
file[vbcol=seagreen]
65[vbcol=seagreen]
have[vbcol=seagreen]
grow.[vbcol=seagreen]
>
>.
>

Database Size

I am building a database and I can not explain the size
it is growing to. I made the size of the database file
100GB. I added 28,000 records with a length of 100
bytes. I added 70,000,000 records with a lenght of 65
bytes. I ran out of space in the database.
I assumed 3 or 4 times the record sizes for overhead when
planning my DB. But I still ran out of room. I have the
backup mode set to simple so the Log file doesn't grow.
Any thoughts would be appreciated.Please post DDL, including indexes. Also, how are you inserting the data?
"DB" <daveblair-nospam-@.adelphia.net> wrote in message
news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||DB,
Hmmm, you should only need a database of around 4-5Gb for that amount of
data. How are you inserting the data?
The log file WILL grow if you are inserting data in large batches. It
needs to grow the log file so that it can roll back the batch if it
fails for some reason (like hardware failure).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DB wrote:
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||Here is the DDL for the tables. There are no indexes at
this time. The data is inserted by a VB program using an
ADO connection to the database. The general code is:
tblAlias.Open "tblAlias", cn3, adOpenDynamic,
adLockOptimistic
**Start loop for each record to add
tblAlias.AddNew
tblAlias!BTBID = rsBusiness!BTBID
tblAlias!State = rsPrs!State
tblAlias!BusID = rsPrs!BusID
tblAlias!SiteID = rsPrs!SiteID
tblAlias!Company = Trim(rsPrs!Company)
tblAlias!StreetName = Trim(rsBusiness!StreetName)
tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
tblAlias!Zip5 = rsBusiness!Zip5
tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
& Mid(rsBusiness!Company, 3, 2)
tblAlias!MatchAddress3 = ""
tblAlias.Update
**End loop
DDL for table definitions.
CREATE TABLE [dbo].[tblAlias] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress1] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress2] [char] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblWord] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Word] [char] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
>--Original Message--
>Please post DDL, including indexes. Also, how are you
inserting the data?
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
>> I am building a database and I can not explain the size
>> it is growing to. I made the size of the database file
>> 100GB. I added 28,000 records with a length of 100
>> bytes. I added 70,000,000 records with a lenght of 65
>> bytes. I ran out of space in the database.
>> I assumed 3 or 4 times the record sizes for overhead
when
>> planning my DB. But I still ran out of room. I have
the
>> backup mode set to simple so the Log file doesn't grow.
>> Any thoughts would be appreciated.
>
>.
>|||Given that your tables have no indexes and your inserts are being done on a
row-by-row basis, I have no clue how it's possible that 70,000,000 rows of
data grew to over 100gb. Are you certain that no other processes are using
the database in question? Did all 70,000,000 rows get inserted, or did the
process stop before it reached completion?
<anonymous@.discussions.microsoft.com> wrote in message
news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
> Here is the DDL for the tables. There are no indexes at
> this time. The data is inserted by a VB program using an
> ADO connection to the database. The general code is:
> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
> adLockOptimistic
> **Start loop for each record to add
> tblAlias.AddNew
> tblAlias!BTBID = rsBusiness!BTBID
> tblAlias!State = rsPrs!State
> tblAlias!BusID = rsPrs!BusID
> tblAlias!SiteID = rsPrs!SiteID
> tblAlias!Company = Trim(rsPrs!Company)
> tblAlias!StreetName = Trim(rsBusiness!StreetName)
> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
> tblAlias!Zip5 = rsBusiness!Zip5
> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> & Mid(rsBusiness!Company, 3, 2)
> tblAlias!MatchAddress3 = ""
> tblAlias.Update
> **End loop
>
> DDL for table definitions.
> CREATE TABLE [dbo].[tblAlias] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress1] [char] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress2] [char] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblWord] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Word] [char] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> >--Original Message--
> >Please post DDL, including indexes. Also, how are you
> inserting the data?
> >
> >
> >"DB" <daveblair-nospam-@.adelphia.net> wrote in message
> >news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> >> I am building a database and I can not explain the size
> >> it is growing to. I made the size of the database file
> >> 100GB. I added 28,000 records with a length of 100
> >> bytes. I added 70,000,000 records with a lenght of 65
> >> bytes. I ran out of space in the database.
> >>
> >> I assumed 3 or 4 times the record sizes for overhead
> when
> >> planning my DB. But I still ran out of room. I have
> the
> >> backup mode set to simple so the Log file doesn't grow.
> >>
> >> Any thoughts would be appreciated.
> >
> >
> >.
> >|||Actually, that's my problem. This is a new stand alone
box. There are no users other than myself. I have
roughly 280,000,000 total records to add to the
database. It halted at the 70,000,000 or so because the
database was full.
I'm completely stumped. While I assume there is
significant storage "overhead" associated with database
records, even 2 or 3 times the size should be less than
20GB.
I have a work around - shrink the DB when it maxes out.
Get all that space back, then resume adding records. A
pain but I'll get there.
Thanks for your efforts.
>--Original Message--
>Given that your tables have no indexes and your inserts
are being done on a
>row-by-row basis, I have no clue how it's possible that
70,000,000 rows of
>data grew to over 100gb. Are you certain that no other
processes are using
>the database in question? Did all 70,000,000 rows get
inserted, or did the
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
>> Here is the DDL for the tables. There are no indexes
at
>> this time. The data is inserted by a VB program using
an
>> ADO connection to the database. The general code is:
>> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
>> adLockOptimistic
>> **Start loop for each record to add
>> tblAlias.AddNew
>> tblAlias!BTBID = rsBusiness!BTBID
>> tblAlias!State = rsPrs!State
>> tblAlias!BusID = rsPrs!BusID
>> tblAlias!SiteID = rsPrs!SiteID
>> tblAlias!Company = Trim(rsPrs!Company)
>> tblAlias!StreetName = Trim(rsBusiness!StreetName)
>> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
>> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
>> tblAlias!Zip5 = rsBusiness!Zip5
>> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) &
Left
>> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company,
1)
>> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) &
Left
>> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company,
1)
>> & Mid(rsBusiness!Company, 3, 2)
>> tblAlias!MatchAddress3 = ""
>> tblAlias.Update
>> **End loop
>>
>> DDL for table definitions.
>> CREATE TABLE [dbo].[tblAlias] (
>> [BTBID] [int] NOT NULL ,
>> [BusID] [int] NOT NULL ,
>> [SiteID] [int] NOT NULL ,
>> [State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS
>> NOT NULL ,
>> [Company] [char] (35) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip3] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip5] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress1] [char] (6) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress2] [char] (9) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress3] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[tblWord] (
>> [BTBID] [int] NOT NULL ,
>> [BusID] [int] NOT NULL ,
>> [SiteID] [int] NOT NULL ,
>> [State] [char] (2) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Company] [char] (35) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip3] [char] (3) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip5] [char] (5) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Word] [char] (15) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> >--Original Message--
>> >Please post DDL, including indexes. Also, how are you
>> inserting the data?
>> >
>> >
>> >"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>> >news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
>> >> I am building a database and I can not explain the
size
>> >> it is growing to. I made the size of the database
file
>> >> 100GB. I added 28,000 records with a length of 100
>> >> bytes. I added 70,000,000 records with a lenght of
65
>> >> bytes. I ran out of space in the database.
>> >>
>> >> I assumed 3 or 4 times the record sizes for overhead
>> when
>> >> planning my DB. But I still ran out of room. I
have
>> the
>> >> backup mode set to simple so the Log file doesn't
grow.
>> >>
>> >> Any thoughts would be appreciated.
>> >
>> >
>> >.
>> >
>
>.
>

Wednesday, March 21, 2012

Database Size

I am building a database and I can not explain the size
it is growing to. I made the size of the database file
100GB. I added 28,000 records with a length of 100
bytes. I added 70,000,000 records with a lenght of 65
bytes. I ran out of space in the database.
I assumed 3 or 4 times the record sizes for overhead when
planning my DB. But I still ran out of room. I have the
backup mode set to simple so the Log file doesn't grow.
Any thoughts would be appreciated.
Please post DDL, including indexes. Also, how are you inserting the data?
"DB" <daveblair-nospam-@.adelphia.net> wrote in message
news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.
|||DB,
Hmmm, you should only need a database of around 4-5Gb for that amount of
data. How are you inserting the data?
The log file WILL grow if you are inserting data in large batches. It
needs to grow the log file so that it can roll back the batch if it
fails for some reason (like hardware failure).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DB wrote:
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.
|||Here is the DDL for the tables. There are no indexes at
this time. The data is inserted by a VB program using an
ADO connection to the database. The general code is:
tblAlias.Open "tblAlias", cn3, adOpenDynamic,
adLockOptimistic
**Start loop for each record to add
tblAlias.AddNew
tblAlias!BTBID = rsBusiness!BTBID
tblAlias!State = rsPrs!State
tblAlias!BusID = rsPrs!BusID
tblAlias!SiteID = rsPrs!SiteID
tblAlias!Company = Trim(rsPrs!Company)
tblAlias!StreetName = Trim(rsBusiness!StreetName)
tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
tblAlias!Zip5 = rsBusiness!Zip5
tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
& Mid(rsBusiness!Company, 3, 2)
tblAlias!MatchAddress3 = ""
tblAlias.Update
**End loop
DDL for table definitions.
CREATE TABLE [dbo].[tblAlias] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress1] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress2] [char] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblWord] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Word] [char] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

>--Original Message--
>Please post DDL, including indexes. Also, how are you
inserting the data?[vbcol=seagreen]
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
when[vbcol=seagreen]
the
>
>.
>
|||Given that your tables have no indexes and your inserts are being done on a
row-by-row basis, I have no clue how it's possible that 70,000,000 rows of
data grew to over 100gb. Are you certain that no other processes are using
the database in question? Did all 70,000,000 rows get inserted, or did the
process stop before it reached completion?
<anonymous@.discussions.microsoft.com> wrote in message
news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...[vbcol=seagreen]
> Here is the DDL for the tables. There are no indexes at
> this time. The data is inserted by a VB program using an
> ADO connection to the database. The general code is:
> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
> adLockOptimistic
> **Start loop for each record to add
> tblAlias.AddNew
> tblAlias!BTBID = rsBusiness!BTBID
> tblAlias!State = rsPrs!State
> tblAlias!BusID = rsPrs!BusID
> tblAlias!SiteID = rsPrs!SiteID
> tblAlias!Company = Trim(rsPrs!Company)
> tblAlias!StreetName = Trim(rsBusiness!StreetName)
> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
> tblAlias!Zip5 = rsBusiness!Zip5
> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> & Mid(rsBusiness!Company, 3, 2)
> tblAlias!MatchAddress3 = ""
> tblAlias.Update
> **End loop
>
> DDL for table definitions.
> CREATE TABLE [dbo].[tblAlias] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress1] [char] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress2] [char] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblWord] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Word] [char] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> inserting the data?
> when
> the
|||Actually, that's my problem. This is a new stand alone
box. There are no users other than myself. I have
roughly 280,000,000 total records to add to the
database. It halted at the 70,000,000 or so because the
database was full.
I'm completely stumped. While I assume there is
significant storage "overhead" associated with database
records, even 2 or 3 times the size should be less than
20GB.
I have a work around - shrink the DB when it maxes out.
Get all that space back, then resume adding records. A
pain but I'll get there.
Thanks for your efforts.

>--Original Message--
>Given that your tables have no indexes and your inserts
are being done on a
>row-by-row basis, I have no clue how it's possible that
70,000,000 rows of
>data grew to over 100gb. Are you certain that no other
processes are using
>the database in question? Did all 70,000,000 rows get
inserted, or did the[vbcol=seagreen]
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
at[vbcol=seagreen]
an[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
Left[vbcol=seagreen]
1)[vbcol=seagreen]
SQL_Latin1_General_CP1_CI_AS[vbcol=seagreen]
size[vbcol=seagreen]
file[vbcol=seagreen]
65[vbcol=seagreen]
have[vbcol=seagreen]
grow.
>
>.
>

Sunday, February 19, 2012

Database Problem related Clusterd data

Hello sir
We have a very huge database its around 6 lakh records
are being stored in it.
records are not being a sorted order so we checked all
record field through clustering option in Sql server.when we used
clustering records are showing in sorted order but speed of database is
very slow .
i want to know with clustering is there any implecation in database
regading performance od data.Can any one give his views to solve this
problem?and increase the performance of our database.
thanks a lot
vibha wrote:
> Hello sir
> We have a very huge database its around 6 lakh records
^^^^^^
What kind of multiplier is this? --+

> are being stored in it.
> records are not being a sorted order so we checked all
> record field through clustering option in Sql server.when we used
> clustering records are showing in sorted order but speed of database
> is very slow .
> i want to know with clustering is there any implecation in database
> regading performance od data.Can any one give his views to solve this
> problem?and increase the performance of our database.
> thanks a lot
Using a clustered index just because you want to get sorted results is not
a good idea. Now, first you need to determine why it's slow. Then you
can take countermeasures. Have a look around
http://www.sql-server-performance.com/default.asp
and especially
http://www.sql-server-performance.co...ering_2000.asp
Kind regards
robert

Friday, February 17, 2012

Database Performance Halts

Hi,

I have been running a reporting App on an SQL 2000 server, which reads from one large table (roughly 80 million records which grows at around 2 million records a week).

However, it would seem that when multi-users try to access the App and the one large table is Read from using 1 database user name up to approximately 25 times, the system would slow to a halt. Essentially, each request on the App would be a new connection to the SQL database using the same database user name. Recently, we have been running into performance issues since we have increased the number of users for the App.

What would be causing this slow down? and what could solve this problem?

>>What would be causing this slow down? and what could solve this problem?

Do you have indexes on the table? are the queries sargable? are your statistics up to date?

>>and the one large table is Read

What does this mean how is the table read? Are you just doing a select * from table?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

>>Do you have indexes on the table? are the queries sargable? are your statistics up to date?

I have indexes on the table and seems to work perfectly fine when there is only 1 query running. All the queries I am running are based on atleast 1 index on that table. Also which statistics are you referring to?

>>What does this mean how is the table read? Are you just doing a select * from table?

From what I can tell, it should only be a series of select statements or nested select statements that the report App runs and stores on the report App side. The reporting App is making from 10-30 of these requests on that 1 indexed table at a single moment in time.

Database partition

What is best criteria to partition table?
Table size or number of records?
How many partition we can do?
If I have 20 GB size of table how many tables of how much size should i
create?
Regards
Amish ShahThe best criterion is to partition when you need to provide a solution to a
PROBLEM and the best solution is PARTITIONING.
So the question is.... do you have a PROBLEM that needs solving?
What is your PROBLEM?
Is PARTITIONING the best SOLUTION for your PROBLEM?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"amish" <shahamishm@.gmail.com> wrote in message
news:1129544966.925091.296410@.g43g2000cwa.googlegroups.com...
> What is best criteria to partition table?
> Table size or number of records?
> How many partition we can do?
> If I have 20 GB size of table how many tables of how much size should i
> create?
>
> Regards
> Amish Shah
>|||Suppose I have table of 20GB how much partition should i create? What
should be size of each partition?
Or
If I have table of 2.5 billion rows how much partition should be
created? How much rows should be in each partition?
Regards|||Amish,
I think what Nik was pointing out is that you are asking for a solution
without describing a problem. Size alone is not enough criteria to determine
proper partitioning. How can your data be segregated? The answer to
partitioning is not <x> rows = partition. The view your build will need to
determine which table(s) from the partition to query. If you do not
partition on a meaningful field, then you will hurt your performance rather
than improve it.
Provide more specifics and I am sure someone here can help you out. Since
you mention the number of rows, I will assume you are looking for horizontal
partitioning, so in the meantime read up on horizontal partitioning in BOL.
John
"amish" wrote:
> Suppose I have table of 20GB how much partition should i create? What
> should be size of each partition?
> Or
> If I have table of 2.5 billion rows how much partition should be
> created? How much rows should be in each partition?
> Regards
>|||Hi John.
Correct. Solutions are for problems. You've not described yours at all. Will
you really have 20 billion rows? If so how big are they, 1 single ID column
and an integer would still mean you may not need partitioning as all the
data could fit onto a normal (or several) medium sized disks.
Paritioning is not the only solution for large or high use databases. You
could split the data into 2 tables each with the same key. One table has a
few columns but is very frequently accessed. In this case the packing
density of rows will be high and a lot of data accesses willl get pages from
buffers alreay in memory. The other table could contain infrequently used
data.
I've seen many people come unstuck because they've embraced something which
sounds "high-tech" or "is the current buzzword", developed their solution
and it doesn't provide what they wanted because they didn't define the
problem properly in the first place.
Please explain your current problem and what you're trying to achieve (with
and without partitioning) and we'll all be able to better help you arrive at
the best solution.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AA57FE51-EFEA-4D74-8499-4F58535D92EA@.microsoft.com...
> Amish,
> I think what Nik was pointing out is that you are asking for a solution
> without describing a problem. Size alone is not enough criteria to
> determine
> proper partitioning. How can your data be segregated? The answer to
> partitioning is not <x> rows = partition. The view your build will need
> to
> determine which table(s) from the partition to query. If you do not
> partition on a meaningful field, then you will hurt your performance
> rather
> than improve it.
> Provide more specifics and I am sure someone here can help you out. Since
> you mention the number of rows, I will assume you are looking for
> horizontal
> partitioning, so in the meantime read up on horizontal partitioning in
> BOL.
> John
> "amish" wrote:
>> Suppose I have table of 20GB how much partition should i create? What
>> should be size of each partition?
>> Or
>> If I have table of 2.5 billion rows how much partition should be
>> created? How much rows should be in each partition?
>> Regards
>>|||Hi
Thanks for the reply
Actually I have joined a new company.
Here we have some tables of 20 gb to 50 gb in size.
So I just want to know should I partition this tables?
I think if I will partition it, reindex and other management activity
will be easier for me.
I want to partition this tables so I just want to find on which
criteria and how many partition should I create for this tables.
Regards|||Amish,
Again, the answer is not based on size. When you partition you are NOT
doing it to reduce the administrative workload. in fact, you probably
increase it. You are doing it for performance reasons. And the data has to
be partitioned based on criteria.
So for example your company has 5 sales regions and they are comprised of 10
states each. you could partition the data into 5 tables, each representing
the sales data for a specific refion. This might make sense since in most
cases the queries are going to be for a specific region. You see what we
mean when we say there has to be some logical criteria for the parition.
You cannot just say. 2billion / 10 = 200 million rows per table. All you
have done with this partition is forced the creation of a view that will
ALWAYS have to join ALL the tables. No benefit there at all. And how do you
decide which table to insert into? It needs to be based on some business
logic, not just pulled out of thin air. Stop looking at the size of the
tables and start looking at the logic for partitioning.
John Scragg
"amish" wrote:
> Hi
> Thanks for the reply
> Actually I have joined a new company.
> Here we have some tables of 20 gb to 50 gb in size.
> So I just want to know should I partition this tables?
> I think if I will partition it, reindex and other management activity
> will be easier for me.
> I want to partition this tables so I just want to find on which
> criteria and how many partition should I create for this tables.
> Regards
>