get table size: rows

Showing posts with label rows. Show all posts

Friday, March 23, 2012

How many rows...?

I'm developing an app that will allow a user to track clicks and sales by date/time. However, I was wondering with the potential for a TON of clicks, is it feasable to track individual clicks in a SQL Server 2000 or 2005 database? I'm talking potentially 50,000 products receiving thousands of clicks per day, coud the db handle that kind of traffic? Or should I stick with a running total of clicks per product, and forget about the ability to see a breakdown of clicks per day, etc?That data is going to get out of control rather fast. However you can keep a running total by day.|||How many rows can a typical table handle? 1 million? 500 million? 1 billion? More? That will help me figure out if I can track clicks daily, or if I have to track weekly or monthly.|||The only limitation of rows per table in SQL is the storage available, see 'Maximum Capacity Specifications' topic in SQL Books Online

How many rows has been affected?

hi everyone,

I’d like to know how many rows has been affected for this query. But I don’t want to use aprevious Sql Task style “SELECT COUNT(*) REGISTROS FROM…”

Such a query like that:

INSERT INTO TABLEDEST

SELECT F1,F2,

FROM TABLESOURCE

WHERE <CONDITION>

I’d like to retrieve that value from a later Script Task

Thanks a lot for your help and thoughts,

Moved from SSIS to t-sql forum...|||I don't know about getting it from a later script task, but you could get it from the same script task pretty easily. You could add a SELECT @.@.ROWCOUNT after your insert statement and then catch the result in a variable.
|||I agree. Use @.@.rowcount to get the rows affected, but getting it into a SSIS variable (or something similar) is beyond this forum. If you need to know that we can bounce it back to the SSIS forum

|||Thanks for that JayH. It was very useful.

Wednesday, March 21, 2012

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
SimonWell, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon|||here
http://www.microsoft.com/sql/evalua...ies/default.asp
Bojidar Alexandrov|||and "the one"
[url]http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=11205[/ur
l]
Bojidar Alexandrov|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>|||Thanks all.
Thats all really helpful. Cheers
Simon

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
Simon
Well, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>
|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon
|||here
http://www.microsoft.com/sql/evaluat...es/default.asp
Bojidar Alexandrov
|||and "the one"
http://www.microsoft.com/resources/c...eStudyID=11205
Bojidar Alexandrov
|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:

> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>
|||Thanks all.
Thats all really helpful. Cheers
Simon

How many rows can a given processor handle

Hi all,
I know this is a very general question but if anyone can give me even a
ballpark figure that would be a big help.
I have a database that in a single table has between 75 million and 150
million. These are the absolute maximum values.
The datatypes are all very simple and there is about 8 columns. This table
links to various other tables through standard relationships.
There are 6 other table.
When a join occurs, it occurs between at most, 3 tables.
My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
SCSI disks handle this dealing with 100 users at a time?
If not, could anyone suggest a spec that could
Thanks for any advice you can share
SimonWell, it all depends on what kind of joins and what other operations
needed.
Here're some estimate, 8 columns, 2 bytes each, so total 16 bytes per
row. Say it has 150 millions rows, that's
150,000,000 * 16 = 2,400,000,000 byte = 2.3 GB
Assume on average each user retrieves 1.5 millions rows (that's still
alot), then you should have enough memory to handle 100 users. CPU won't
be a problem unless you do some complicated calculations. I am more
worry about the disk performance since unless you design index right,
there may be table scans, which will really hurt the performance.
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:
> Hi all,
> I know this is a very general question but if anyone can give me even a
> ballpark figure that would be a big help.
> I have a database that in a single table has between 75 million and 150
> million. These are the absolute maximum values.
> The datatypes are all very simple and there is about 8 columns. This table
> links to various other tables through standard relationships.
> There are 6 other table.
> When a join occurs, it occurs between at most, 3 tables.
> My question -: Can a dual Xeon 3.06 dedicated sql server with 3Gb ram and
> SCSI disks handle this dealing with 100 users at a time?
> If not, could anyone suggest a spec that could
> Thanks for any advice you can share
> Simon
>|||Thanks for the reply Eric,
If anyone happens to have experience of using very large databases I would
really appreciate any information on the sort of machines that are being
used to run them
Thanks all
Simon|||here
http://www.microsoft.com/sql/evaluation/casestudies/default.asp
Bojidar Alexandrov|||and "the one"
http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=11205
Bojidar Alexandrov|||For a large DB, like > 100 GB DB, most of the time, disk/memory is more
a concern than CPU, unless there're alot of complicated floating point
calculations, like financial risk analysis/scientific modeling,
otherwise, a 4 CPU config. should be good enough.
Memory is cheap now, get as much as you can, I was working on a 600 GB
DB before, and it run happily on a 8 GB machine.
Get SAN if you have $$$, or RAID 10, or multiple SCSI disk controllers,
partition your DB into different file groups and put different group on
different disk, if possible. There are just too many different ways, all
depend on how big your budget is.
Or you can get multiple machines, partition your DB and chain those
machines together.
There's no one-size-fit-all solution, you need a seasoned DBA to look at
your requirement.
--
Eric Li
SQL DBA
MCDBA
Simon Harvey wrote:
> Thanks for the reply Eric,
> If anyone happens to have experience of using very large databases I would
> really appreciate any information on the sort of machines that are being
> used to run them
> Thanks all
> Simon
>|||Thanks all.
Thats all really helpful. Cheers
Simonsql

How many rows before optimizer will use index

Hi
2 questions for you Gentlemen ;-)
1:
I think I have heared that SQL Server optimizer (always ?)
will use a table scan, if the row count of a table is
under a certain value even if there exist an - othervise
appropriate - index, simply because this is considered
faster.
How many rows do a table need to have before indexes are
considered usefull - does a roughly value exist?
(I think I read the number 8000 rows once)
Is there any differences between indextypes (CL, NCL,
single/multicolumn...)in the minimum "required" row count
2:
In a table A with many rows a column colA1 exists (with
a "code type" value), referencing table B with few rows
containing the list of codes.
Though a FK (and sometimes used in queries, joins etc.)
woundn't it be superfluous to index the FK column colA1,
because of the relative bad/low selectivity of the
different domain values (both if an fairly even spread of
the different few codes are assumed or if one of the codes
are in majority).
kind regards
Jakob Persson
Denmarkactually,
SQL Server will almost always try to use an index unless
there are atleast 6-12 rows, and the plan involves a NC
index and bookmark lookups
see:
http://www.sql-server-
performance.com/jc_sql_server_quantative_analysis1.asp
for more info
2. if you don't expect a plan to use the index, it may not
be necessary. i am assuming you do not delete from table B.
>--Original Message--
>Hi
>2 questions for you Gentlemen ;-)
>1:
>I think I have heared that SQL Server optimizer
(always ?)
>will use a table scan, if the row count of a table is
>under a certain value even if there exist an - othervise
>appropriate - index, simply because this is considered
>faster.
>How many rows do a table need to have before indexes are
>considered usefull - does a roughly value exist?
>(I think I read the number 8000 rows once)
>Is there any differences between indextypes (CL, NCL,
>single/multicolumn...)in the minimum "required" row count
>2:
>In a table A with many rows a column colA1 exists (with
>a "code type" value), referencing table B with few rows
>containing the list of codes.
>Though a FK (and sometimes used in queries, joins etc.)
>woundn't it be superfluous to index the FK column colA1,
>because of the relative bad/low selectivity of the
>different domain values (both if an fairly even spread of
>the different few codes are assumed or if one of the
codes
>are in majority).
>kind regards
>Jakob Persson
>Denmark
>.
>|||Jakob Persson wrote:
<snip>
> 2:
> In a table A with many rows a column colA1 exists (with
> a "code type" value), referencing table B with few rows
> containing the list of codes.
> Though a FK (and sometimes used in queries, joins etc.)
> woundn't it be superfluous to index the FK column colA1,
> because of the relative bad/low selectivity of the
> different domain values (both if an fairly even spread of
> the different few codes are assumed or if one of the codes
> are in majority).
This is only true if the values of colA1 are evenly distributed. If the
data distribution is skewed (for example, many 'A' but few 'B'), then
the index can still be beneficial when querying on 'B'.
And (as Joe mentioned), the index is also useful when deleting rows from
the referenced table.
Gert-Jan

How many records in a table?

Hi! Stupid question as this also depends on hardware but is there a limit on
how many rows I can have in a single MSSQL 2000 table? Table consists of six
int columns and has three indexes. Is like 10 Million or 100 Million
considered to be 'normal' or unrealistic?

Thanks very much for your thoughts!

MartinMartin,

It's actually limited ONLY by hardware (disk space). I wouldn't say that 10
or 100 million are anywhere near "unrealistic". Maybe 1 trillion would be
more like it!

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cit9ft$m5b$1@.hercules.btinternet.com...
> Hi! Stupid question as this also depends on hardware but is there a limit
on
> how many rows I can have in a single MSSQL 2000 table? Table consists of
six
> int columns and has three indexes. Is like 10 Million or 100 Million
> considered to be 'normal' or unrealistic?
> Thanks very much for your thoughts!
> Martin|||Thanks!

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:4152361a$0$2656$61fed72c@.news.rcn.com...
> Martin,
> It's actually limited ONLY by hardware (disk space). I wouldn't say that
> 10
> or 100 million are anywhere near "unrealistic". Maybe 1 trillion would be
> more like it!

Monday, March 12, 2012

How make script component output 2 asynchronous?

I am working with the Data Flow Task Script Component for the first time. I have created a second Output. In my script I add rows to this output.

I have found that Ssis does not release those rows to the second Output until it has processed all of the incomine pipeline records. This will not work for me as there are going to be a few million records coming down the pipe, so I need the Script Component to as soon as possible release these records downstream for insert into the destination component Ole Db component.

Any help would be greatly appreciated?

Hmm... I could not replicate.

How are you sending data to the 2nd output?|||

The only way I could figure to add a row to the dataset incode was to make the Synchronous property "None", to reference the Output Buffer, and then I could access the OutputBuffer.AddRow() method.

The problem is that then it tries to complete processing the entire pipeline before proceeding beyond the Script Component.

Is there a way to access the AddRow() method when the Output has the Input set as the SynchronousInput?

Here is my Script Component Code:

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports System.Xml

Imports System.Windows.Forms

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private _Name As String

Private _Value As String

Private _Paco As String

Private _MessageId As Guid

Private _MessageStreamId As Guid

Private _LogDateTimeStamp As DateTime

Public Sub AddRow(ByVal message As String, ByVal row As Input1Buffer, ByVal xPathQuery As String, ByVal adduri As String, ByVal xPathPaco As String, ByVal removeuri As String)

If Not message Is Nothing Then

Dim doc As XmlDataDocument = New XmlDataDocument

Dim aXmlNode As XmlNode

doc.PreserveWhitespace = False

doc.LoadXml(message)

Dim namespaceManager As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)

If Not removeuri Is Nothing Then namespaceManager.RemoveNamespace("ns0", removeuri)

namespaceManager.AddNamespace("ns0", adduri)

aXmlNode = doc.SelectSingleNode(xPathQuery, namespaceManager)

If Not aXmlNode Is Nothing Then

If aXmlNode.InnerText <> "0" Then

_Name = aXmlNode.Name

_Value = aXmlNode.InnerText

_MessageId = row.MessageID

_MessageStreamId = row.MessageStreamID

_LogDateTimeStamp = row.LogDateTimeStamp

aXmlNode = doc.SelectSingleNode(xPathPaco, namespaceManager)

If Not aXmlNode Is Nothing Then _Paco = aXmlNode.InnerText

CreateNewOutputRows()

End If

End Sub

Public Overrides Sub CreateNewOutputRows()

MyBase.CreateNewOutputRows()

With Output2Buffer

.AddRow()

.Name = _Name

.Value = _Value

.MessageId = _MessageId

.MessageStreamId = _MessageStreamId

.LogDateTimeStamp = _LogDateTimeStamp

.Paco = _Paco

End With

End Sub

Public Overrides Sub Input1_ProcessInputRow(ByVal row As Input1Buffer)

AddRow(row.StringMessageBody, row, row.xPathPrId, row.xPathPrNamespace, row.xPathPrPaco, Nothing)

AddRow(row.StringResponseMessageBody, row, row.xPathPrId, row.xPathPrNamespace, row.xPathPrPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathPpId, row.xPathPpNamespace, row.xPathPpPaco, row.xPathPrNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathPpId, row.xPathPpNamespace, row.xPathPpPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathConId, row.xPathConNamespace, row.xPathConPaco, row.xPathPpNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathConId, row.xPathConNamespace, row.xPathConPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathEmailId, row.xPathEmailNamespace, row.xPathConPaco, row.xPathConNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathEmailId, row.xPathEmailNamespace, row.xPathConPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathPhoneId, row.xPathPhoneNamespace, row.xPathConPaco, row.xPathEmailNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathPhoneId, row.xPathPhoneNamespace, row.xPathConPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathAddrId, row.xPathAddrNamespace, row.xPathConPaco, row.xPathPhoneNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathAddrId, row.xPathAddrNamespace, row.xPathConPaco, Nothing)

AddRow(row.StringMessageBody, row, row.xPathPtId, row.xPathPtNamespace, row.xPathConPaco, row.xPathAddrNamespace)

AddRow(row.StringResponseMessageBody, row, row.xPathPtId, row.xPathPtNamespace, row.xPathConPaco, Nothing)

End Sub

End Class

|||You are correct that your component should be in asynchronous mode and you should be using AddRow. You should not, however, be calling CreateNewOutputRows the way you are. The engine will call that on its own. From what I can tell of you logic, you don't need to implement it. Just move the logic you currently have in CreateNewOutputRows to where you are calling it. The AddRow method can be called from anywhere in the script, not just CreateNewOutputRows. As it is, one of your rows will be duplicated when the engine makes its call.

Regarding your concern about the script task wanting to complete the entire pipeline, you need to understand that rows do not move through the pipeline, only buffers do, and buffers consist of about 10,000 rows by default. Buffers are only ejected from a component when they are full, or the pipeline is empty. I see you have some logic filtering the incoming rows. I don't know how many incoming rows you have, or how stringent the filtering is, but if your output is less than a full buffer, then yes, it will wait until the source is finished.
|||Are you only wanting one row coming out of output 2? Or do you want a row for every input row?|||

Can I set the count of rows to cause the buffer to push the data downstream?

Each row that comes in contains an Xml document from which I am harvesting certain node values and sticking them into a structured table.

I originally did not use the CreateNewOutputRows. This was one attempt I made at trying to force the buffer data down through the pipeline. I am encountering the same results with or without the use of CreateNewOutputRows.

Again, my experience was that the script component was not pushing the output down the pipeline. My fear was/is that when I am processing millions of rows it will fill the Ssis Engine Memory space up before it pushes data down the pipeline.

Perhaps if I could set the count at which I want it to push data down the pipeline? Is that possible?

|||Calling AddRow() on the 2nd output in "Public Overrides Sub Input1_ProcessInputRow" should send rows to the 2nd output without waiting for the 1st output. That's what my tests yield.|||

Dotnet Fellow wrote:

Can I set the count of rows to cause the buffer to push the data downstream?

Each row that comes in contains an Xml document from which I am harvesting certain node values and sticking them into a structured table.

I originally did not use the CreateNewOutputRows. This was one attempt I made at trying to force the buffer data down through the pipeline. I am encountering the same results with or without the use of CreateNewOutputRows.

Again, my experience was that the script component was not pushing the output down the pipeline. My fear was/is that when I am processing millions of rows it will fill the Ssis Engine Memory space up before it pushes data down the pipeline.

Perhaps if I could set the count at which I want it to push data down the pipeline? Is that possible?

Yes, you can change the number of rows that go on a buffer. This will cause your buffers to fill and be ejected from your script earlier. I don't recommend it or think it is necessary, though. By default, buffers are 10,000 rows or 10 MB, whichever comes first. So memory should not be a concern. The DefaultMaxBufferRows property of the DataFlow controls the number of rows allowed on a buffer.

If you really expect to be running a million rows through this code, you may want to consider changing it so the XML documents are only loaded once for each row, instead of for each of the xpath nodes your are extracting. I count 14 document loads per row, when probably only 2 are necessary.