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 |
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 2010-06-06 : 02:03:29
|
| I am not sure yet whether this task is for SQL 2000 or higher.There is an ERP system with lots of regular updates and lots of tables.I have been tasked with assisting on the reporting side of things.I have to create one meaningful table from all the others so that reporting can be done from this table.My predesessor used odbc links in excel and bought everything into an excel spreadsheet. This took a couple of minutes to refresh each time.I thought about creating a SQL view (containing lots of little queries to get the final results) and then reporting on the SQL view. But I think that SQL views have to be complied at runtime don't they? (which would slow things down)Maybe I should create a stored procedure that drops and recreates a real table every night?Any suggestions would be great.My main objective is that reporting should be fairly instant.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-06-06 : 04:40:57
|
| "How current does the data need to be? "i think that a snapshot taken at night is probably OK but I would like to give them the ability to refresh the data.No I haven't confirmed that a regular SQL view is slow yet, but I am making that assumption based on past experience. I once had a crystal report that was pulling data from a view and that used to take about a minute and a half to produce the report.mind you I've never done an indexed view before (I'll have to read up on that) will that speed up the compiling of the view or just the pulling of data from the view?I think the results set is in hundreds of thousand of rows rather than millions. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-06-06 : 14:47:44
|
| Thanks for your help so far.So should I get a DTS or ssms package to truncate and re-populate a table nightly so that the data is instantly ready for reporting |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 2010-06-07 : 00:59:16
|
| Thanks for your help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Best way to do something