Generating reports on very large and "live" databases

Hello All,
I am working on a application which has a table with 100 million
records.
A background process keeps inserting 5 rows per second into this
table.
My client is generating reports from that table. They are interested
in seeing reports with real time data. This means that as soon as a
record is inserted into the table, the reports should process that
record as well.
At the max around 20K records are processed to generate a report. When
this happens it takes simply 30 min to generate a report. Upon
analysis I find that since the table is so huge, the indexes on those
table is also very huge.
What can I do to fix this, I have thought about denormalizing the
table. But the some programmers say that picking up data from one
table is better because doing joins on multiple tables will be even
slower.
Another approach is to bring in a data warehouse but I don't know much
about this except what I learnt in MSDN session about creating of data
cubes. But I suppose cube can be created only when data is static. but
in my case new records are inserted every second and they are to be
included in the report.
The 3rd approach is that I create report specific tables and create a
trigger (or a C programm which polls for changes in main table) and
every time new records are inserted into the main table, I preprocess
them. Then when the users make a request for the report I generate my
report from the preprossed table.
But I feel the trigger will be fired to many times and if the number
of reports are significant (> 35) then trigger/C program could become
a bottleneck itself.
What should I do? it is such a tricky problem.
Please help me and give me some advice. Thank you for your help.
regards,
Abhishektable size (or row count) is irrelevent unless a
table/index scan is involved.
5 rows/sec insert is a negligible load on the system.
however, a report that involves 20k rows with good indexes
should not take 30min to run.
1) what is the query,
2) what are the indexes on this table
3) what does the execution plan show?
(indexes used, type of operation, rows involved, costs)
>--Original Message--
>Hello All,
>I am working on a application which has a table with 100
million
>records.
>A background process keeps inserting 5 rows per second
into this
>table.
>My client is generating reports from that table. They are
interested
>in seeing reports with real time data. This means that as
soon as a
>record is inserted into the table, the reports should
process that
>record as well.
>At the max around 20K records are processed to generate a
report. When
>this happens it takes simply 30 min to generate a report.
Upon
>analysis I find that since the table is so huge, the
indexes on those
>table is also very huge.
>What can I do to fix this, I have thought about
denormalizing the
>table. But the some programmers say that picking up data
from one
>table is better because doing joins on multiple tables
will be even
>slower.
>Another approach is to bring in a data warehouse but I
don't know much
>about this except what I learnt in MSDN session about
creating of data
>cubes. But I suppose cube can be created only when data
is static. but
>in my case new records are inserted every second and they
are to be
>included in the report.
>The 3rd approach is that I create report specific tables
and create a
>trigger (or a C programm which polls for changes in main
table) and
>every time new records are inserted into the main table,
I preprocess
>them. Then when the users make a request for the report
I generate my
>report from the preprossed table.
>But I feel the trigger will be fired to many times and if
the number
>of reports are significant (> 35) then trigger/C program
could become
>a bottleneck itself.
>What should I do? it is such a tricky problem.
>Please help me and give me some advice. Thank you for
your help.
>regards,
>Abhishek
>.
>

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
Thx
A common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx
|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thxsql

Generate AutoNumber

Hi, I have a question, I have created a table and with a primary key called "ID". However, I want the "ID" be auto increment as well. when inserting new record into the database.I'm using vb.net. how can I do in the following format: "1", "2", "3", ......... etc. I've the code below but it's not working in the right way, what's wrong with my code? PrivateSub BtnAdd_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAdd.Click Dim ssqlAsString Dim ItemidAsInteger Dim updcmdAs SqlClient.SqlCommand Itemid = 0 mysqladap =New SqlClient.SqlDataAdapter("select MAX(Item_id) From auction where item_type= '" & (Image1.ImageUrl) & "'", mySqlConn) Itemid = (Itemid) + 1 ssql = "insert into auction (item_id,owner_name,owner_mail,owner_mobile,owner_phone,owner_ext,item_type,item_name,item_image,item_desc,item_cost,start_date) values ('" & (Itemid) & "','" & Trim(ownertxt.Text) & "' ,'" & Trim(emailtxt.Text) & "', '" & Trim(mobiletxt.Text) & "', '" & Trim(phonetxt.Text) & "','" & Trim(exttxt.Text) & "','" & Trim(DropDownList1.SelectedValue) & "','" & Trim(itemtxt.Text) & "','" & Trim(Image1.ImageUrl) & "','" & Trim(desctxt.Text) & "','" & Trim(costtxt.Text) & "','" & Trim(Today.Date) & "')" updcmd =New SqlClient.SqlCommand(ssql, mySqlConn) updcmd.ExecuteNonQuery() lblmsg.Visible =True EndSub Anyone can help me? Thanks.

As I replied elsewhere where you asked the exact same question, use an IDENTITY column. Then you will not need to insert the value for the column, it will populate itself.|||

First of all thanks for your reply,
Can you please explain more?
Can you write the statement for me or the modification on my given code?
Thank you

|||You have to set the Identity column in the datatable table properties. You dont do that via code. In Sql Enterprise manager, when you open the table in design time, you can set the Identity property of that column to "yes" at the bottom of the screen.|||Thank you sooooooo much, and after that, delete the code I've written? and don't inset that column in the insert statement of sql?|||What's meant by identity seed? it's set to 1, shall I change it?|||

I've inserted 6 records and they are given the numbers 1,2,3,4,5, and 6, then I deleted them all and add new record, it has given the number 7, WHY?
why it isn't set to 1 again?

|||the numbers will not reset unless you recreate the table, or truncate table.