Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2009-09-15 : 02:49:39
|
| i need to make report in Reporting Server.mt table have 10 M rows, and every dayt i delete 70K rows.my clinets want immediately result when he push on the buttom.i added clustered index on the column (Invoice Number).do you have another solution?index is good idea?(i am afraid,because every day i need to delete and insert from the table 70K rows,after that my table isn't completly is indexed, so i need to rebuild index on 10 M rows ) |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-09-15 : 04:35:22
|
| you could try to partition table horizontally, or even to split the table into 2 or 3optimization here mainly depends on the query you are running to generate the report,can we see it? |
 |
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-09-15 : 07:10:04
|
| i dont understand you answer.my query is : "SELECT InvoiceNumber,InvoiceDate FROM INVOICET"(very simple)the problem ,even i split it to 2-3 table i still have each table more than 2M rows. and the search time of InvoiceNumber is still take long time |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-09-15 : 07:17:16
|
| Can you batch the data changes and report together?That is, create a job that DELETES the data, REBUILDS the index (if required) and creates the report. Maybe schedule this.Then when the client queries the report it will be made available quicker. |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-09-15 : 07:50:27
|
| SELECT InvoiceNumber,InvoiceDate FROM INVOICETif our query is this simple then it returns 10M rows and if it takes you ~5sec to show it to the client its WOW fast :)so how many rows do you usually show? |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-09-16 : 01:30:45
|
| YellowBug - the question is is it right to build index of table that i need to delete and insert from the table 10 M rows?asgast - yes, i have today 10M rows.the clients insert invoicenumber in paramter (and i search on table that have 10M rows)can i make rebuild on index evert day ,of table that have 8M rows and i delete and insert every day 70K ?what is recommended of microsoft? |
|
|
|
|
|
|
|
|
index ??