April 7, 2004 at 10:46 am
Hi,
I am working with a SQL 2000 Database. This Database has around 15 Tables. I need to build a View that creates a large un-normalised Recordset. This will then be used as a base Data Cube for a 3rd Party reporting tool.
Central to this database is an Orders Table with around 165,000 rows. I can link the vast majority of this Table will all others to create a large un-normalised recordset.
However, amongst the columns are 4 Account Numbers. I have a Table called 'Customer Master' with around 150,000 rows in it. This Customer Master Table contains Account Number and a Customer Name. I need to link the Orders Table to this Table to retrieve the Customer Name for all 4 Account Numbers. I have done this using the View builder and 4 Alias Tables. However, when I run the View I get a TimeOut error. Seems I am asking the View to do too much querying the Customer Master table 4 times for each Order?
All Tables have Indexes on them.
What can I do to get round this? Can I increase thw Timeout property of the database from within a View?
I guess one option would be to create several Views that gradually build up the recordset but that seems a bit of a bodge. I'm sure there must be a way?
Any help would be much appreciated.
Thanks.
April 7, 2004 at 3:55 pm
Neil,
Instead of "running" the view in the Enterprise Manager try to use SQL Query Analyzer. After you've created the view open Query Analyzer and run
SELECT * from <YourViewName>
Query Analyzer doesn't have the timeout limit. Also, in QA you can check the Execution Plan for your query. It'll show you which indexes are actually used (or maybe it's a table scan).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply