Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 3rd, 2004, 12:54 PM
ea ea is offline
Registered User
 
Join Date: Nov 2004
Location: New York, NY, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default using ADP with subreports very slow help!!!

Originally posted on the Access forum but havn't gotten any replies. Hope someone here can help. Thanks.


Hi,

Does anyone know why an .ADP report with three subreports would take over two minutes to load??? I've got 3 more subforms to add and I'm afraid the report will be unusable.

Some specs:

Two of the subforms are views linked by master child relationship.
One of the subforms is a stored procedure using input parameters.
There's not that much data to filter in either the views or the sp (500 records is the largest view)
The there are 4 input parameters on the sp, two being supplied by an open form and two by the main report.

Any ideas???

Yesterday, the thing was still trying to load after 15 minutes before I stopped it. Today, it's loading in about 2 and a half.

I'm using Access 2003 connected to SQL 2000 Desktop Engine. I'm on a stand alone machine.

Any help would be greatly appreciated.

Thanks,
ea



Reply With Quote
  #2 (permalink)  
Old December 4th, 2004, 01:47 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

If your view has more than two underlining tables this might have slow performance issues. Check or add indexes to your underlining tables and or index your view,

Post your views and store procedures and its possible it may need some optimizing.

Jaime E. Maccou
Applications Analyst
Database Programmer
Reply With Quote
  #3 (permalink)  
Old December 6th, 2004, 11:04 AM
ea ea is offline
Registered User
 
Join Date: Nov 2004
Location: New York, NY, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks so much for responding. My post seems to be read but no one seems to know what to do.

I just realized that there are no primary keys selected in any of the database tables. I've added the PK's but to no avail. It still runs very slow.

My view and stored procedures are using several tables, about 4 to 6 at a time. Why should that be an issue?

Also, I've never added indexes to a view or stored procedure. How is that done.

Thanks for the help.

Reply With Quote
  #4 (permalink)  
Old December 6th, 2004, 11:33 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

The design and number of tables a view touches does have an impact of the execution and results of a view. I have pasted below information related to views which may be helpful. I recommend that you profile your view and examing the execution plan to see where it is slowing down

View Information from Books Online.

View Indexes
Microsoft® SQL Server™ 2000 supports defining indexes on views. Views are sometimes called virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements. The result set of a non-indexed view is not stored permanently in the database. Each time a query references the view, SQL Server dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view. For more information, see View Resolution.

For a nonindexed view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows. Examples include views that aggregate large amounts of data, or join many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. For more information about the structure used to store clustered indexes, see Clustered Indexes.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.

Creating a clustered index on a view stores the result set built at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the view's clustered index be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

You must have set specific SET options before you can create an index on a view. The query optimizer will not consider the index for any subsequent SQL statements unless the connection executing the statement has the same option settings. For more information, see SET Options That Affect Results.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries.

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name.

All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Although only the columns that make up the clustered index key are specified in the CREATE UNIQUE CLUSTERED INDEX statement, the complete result set of the view is stored in the database. As in a clustered index on a base table, the b-tree structure of the clustered index contains only the key columns, but the data rows contain all of the columns in the view result set.

View Indexes
Microsoft® SQL Server™ 2000 supports defining indexes on views. Views are sometimes called virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements. The result set of a non-indexed view is not stored permanently in the database. Each time a query references the view, SQL Server dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view. For more information, see View Resolution.

For a nonindexed view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows. Examples include views that aggregate large amounts of data, or join many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. For more information about the structure used to store clustered indexes, see Clustered Indexes.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.

Creating a clustered index on a view stores the result set built at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the view's clustered index be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

You must have set specific SET options before you can create an index on a view. The query optimizer will not consider the index for any subsequent SQL statements unless the connection executing the statement has the same option settings. For more information, see SET Options That Affect Results.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries.

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name.

All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

Although only the columns that make up the clustered index key are specified in the CREATE UNIQUE CLUSTERED INDEX statement, the complete result set of the view is stored in the database. As in a clustered index on a base table, the b-tree structure of the clustered index contains only the key columns, but the data rows contain all of the columns in the view result set.



Jaime E. Maccou
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing parameter value to subreports elygp Crystal Reports 4 May 3rd, 2007 01:25 PM
no datas displayed in subreports suji_dec08 Crystal Reports 0 April 11th, 2007 12:56 AM
ADP VERY SLOW WITH SUBFORMS HELP! ea Access 1 December 3rd, 2004 02:53 PM
Subreports nesting svenvandevelde Crystal Reports 2 February 4th, 2004 12:20 PM
Subreports in Access 2002 mhosneddine Access 1 October 26th, 2003 08:55 PM



All times are GMT -4. The time now is 04:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.