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 |
|
Milano
Starting Member
2 Posts |
 Posted - 2008-07-24 : 14:50:31
|
| We have a SQL Server 2K5 DB Application (with Merge Replication Involved) and we were having performance issues with Reporting (they took too long to generate). We introduced Maintained Views to reduce our reporting times and discovered that most of our data input functions through our front end GUI have ground to a halt. Our front end is Delphi 7 using ADO components to access the DB.How can we increase the speed of our reports without compromising speed of data input? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-24 : 16:23:02
|
| Maintained Views? Do you mean indexed views? If so, may affect data loading since sql has to refresh those rows with new data. |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-07-25 : 06:25:43
|
| If you are using Reporting Services you may want to have a look at snapshots: http://technet.microsoft.com/en-us/library/ms157204.aspx. Are the users happy to see data at the end of yesterday? If so, another option may be creating a process to copy the tables to another DB where you have the indexed views & leaving the operational DB without them. You'd need a process which is run out of business hours.Other things to check1. Check resourcing on the server (RAM, data & log file sizes, swap space / disk space, CPU utilisation)2. Use the SQL Profiler to have a look at what the reports are doing to see if there are any obvious problems eg missing join clauses3. Do some analysis of the indexes: http://www.sql-server-performance.com/tips/index_tuning_wizard_p1.aspx4. If all this looks ok, you may need to look at the reports & create a process to summaries the data in summary tables, so when the reports run they don't have to do so much work |
|
|
|
Milano
Starting Member
2 Posts |
Posted - 2008-07-28 : 10:11:26
|
| Thank you very much for your input. We are going to investigate a few of the things that were mentioned and we'll post back with some results or possibly a few more questions.Thanks again. |
|
|
|
|
|
|
Optimized reporting and slowed down data input