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 |
|
bab_ganesh
Starting Member
4 Posts |
 Posted - 2008-08-04 : 02:49:10
|
| HiI have a table with 4 million records,35 fields. A primary key field. Every 1 hours 1000s or 10000s of records inserting in this table using bulk insert.I have report in my web application querying 25 fields including the primary key field. But while querying the primary key field then the result pretty fast. But querying the non-indexed field then the process dead slow.So that I created an indexed view with all the 25 field. Then set the clustered index and the non-clustered Index for 24 field. Then my all the query are Good fast.Now I experiencing that, the insertion in the base table is too slow. I doubt the indexed view ( bcz SQL up-to-date the indexed view too while insertion happen in the base table - I think it takes time to up-to-date)So drop the indexed view. Now my all the queries gets slow. :( I don't know What I do next?Can I set the non-clustered index to all the 24 fields.(Querying fields) in the base table itself.? Will it leads any problem mentioned above.?Please suggest me.Thanks in AdvanceGanesh.Ganesh. |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-08-04 : 02:53:17
|
| What about using a separate table for the reports & updating it on a schedule. Are the uses happy with reports 1 day or a few hours old? Then you could optimise main table for inserts & secondary table for queries. |
 |
|
|
bab_ganesh
Starting Member
4 Posts |
Posted - 2008-08-04 : 03:14:51
|
| No.. Its not possible. transferring millions of records. But our client want the report up-to-date.Thats why I couldn't try this method alsow :-(Ganesh. |
|
|
|
|
|
|
Challanging Scenario in Index