April 4, 2005 at 2:07 pm
Hello,
We are using a couple of views in our DB that return specific information. The stored procedures in our app need to grab this information to return to front-end. The views are non indexable and slow running as they are selecting against a large portion of data. They work fine independently, but I am concerned that this could cause slow performance when used in application code repeatedly. What is the best way to pull the data in this scenario ? I was thinking maybe populate tables with the view data and index those tables. The stored procs can then access those table directly. Any suggestions are appreciated.
Thanks
April 4, 2005 at 2:59 pm
That may be your only decent option if your where clause is based on expressions in the view (forcing it to resolve the whole view prior to filtering rows), but if not you may just need better indexing on the root tables to better support the view's access path with the where clauses being applied to it. For example, you may need an alternate index to support the primary filtering criteria or even if an existing index can support it, the order of the columns in the index may not be effective enough (if multiple columns in the index, is the first column in the filter?). Without DDL it's hard to give more specific answers or ideas. If you give us the DDL, or at least an abstracted version of it, examples of the where clauses being applied from the stored procedure would be necessary as well to give solid answers.
April 4, 2005 at 3:05 pm
Hello Aaron,
Thanks for your reply -
Here is the view being used :
/* The view returns a single receivable address for based on the max update_date and address_id , This table has ~ 990000
rows*/
SELECT TOP 100 PERCENT *
FROM dbo.Address
WHERE (Address_Id IN
(SELECT MAX(address_id)
FROM dbo.Address
WHERE (Update_Date =
(SELECT MAX(Update_Date)
FROM Address b
WHERE Address.abmsuid = b.abmsuid AND (type_id IN ('H', 'P') OR
type_id IS NULL) AND deleted_ind = 0))
GROUP BY abmsuid))
ORDER BY ABMSUID
April 4, 2005 at 3:37 pm
If the stored procedures are working on the entire view results then you can't really do too much. If there is a WHERE clause then the following should work a lot better.
SELECT TOP 100 PERCENT TopAddress.*
FROM dbo.Address TopAddress
WHERE TopAddress.Address_Id =
(SELECT MAX(MaxAddress.address_id)
FROM dbo.Address MaxAddress
WHERE MaxAddress.Update_Date =
(SELECT MAX(Update_Date)
FROM Address b
WHERE MaxAddress.abmsuid = b.abmsuid
AND (b.type_id IN ('H', 'P') OR b.type_id IS NULL)
AND b.deleted_ind = 0
)
AND MaxAddress.abmsuid = TopAddress.abmsuid
)
ORDER BY TopAddress.ABMSUID
This allows TopAddress to be filtered avoiding much of the MAX work if, for example, it is addressed as follows.
SELECT * FROM MyAddressView
WHERE ABMSUID = 'XYZ'
Most any other where clause would help performance as well, especially, but not necessarily, if the columns in the SP WHERE clause are indexed within the Address table. The reason the rewrite works faster under those conditions is because the max is only determed for those rows (ABMSUIDs) that were not filtered out the easy way from the SP WHERE clause.
If the SPs are working with the entire view, and not a subset of it, then you may still be able to make slight improvements by making sure you have an index on ABMSUID, Type_Id, Deleted_Ind, Update_Date (in that order) and another index on Update_Date, Address_id, ABMSUID (in that order) and another index on only Address_ID. That would be 3 new indexes to provide an unknown abount of benefit (you could try and see easily enough) but you also have to weigh in the extra overhead to the INSERT/UPDATE/DELETE operations on the table for maintaining those 3 additional indexes. I'm guessing the added disk space isn't a major concern. Looking at the query that is the best I can come up with for indexing without seeing the SP code. You want to run it past Query Analyzer to see what it suggests for indexes, too.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply