speeding up a view

Question

Post reply

speeding up a view

karen mcavoy

Newbie

Points: 4
More actions
February 6, 2004 at 9:18 am

#76123

I have a form that uses a view for a control source. This view resides in one database and looks into another one. The data is coming out right, however it is too slow. I used the index tuning wizard to see if indexing the view would speed things up, but it said it was not needed. The code used in the view is:
SELECT DISTINCT
               a.bdrid, a.containerid, b.actionlocation + '-' + b.actionnum + '-' + b.currentyear + '-' + LTRIM(RTRIM(ABS(b.revision))) AS NCRID, d.ActualCondition,
               d.FinalDispositionID, d.IsNCROpen
FROM CCP_NCR.dbo.BDR_Container a INNER JOIN
               CCP_NCR.dbo.bdr_ncr b ON a.bdrid = b.bdrid INNER JOIN
               CCP_NCR.dbo.Container_NCR c ON a.containerid = c.containerid INNER JOIN
               CCP_NCR.dbo.NCR_Master d ON d.Actionlocation + '-' + d.ActionNum + '-' + d.CurrentYear + '-' + LTRIM(RTRIM(ABS(d.Revision)))
               = b.actionlocation + '-' + b.actionnum + '-' + b.currentyear + '-' + LTRIM(RTRIM(ABS(b.revision)))
Any suggestions to speed things up??---thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I would suggest you to create Two Computed Columns

1 On CCP_NCR.dbo.NCR_Master

CompColName1 AS (Actionlocation + '-' + ActionNum + '-' + CurrentYear + '-' + LTRIM(RTRIM(ABS(Revision)))

2. On CCP_NCR.dbo.bdr_ncr

CompColName2 AS (actionlocation + '-' + actionnum + '-' + currentyear + '-' + LTRIM(RTRIM(ABS(revision))))

3. Create An index on Each!

4. Rewrite the statement Like:

SELECT DISTINCT

a.bdrid

, a.containerid

, b.CompColName2 AS NCRID

, d.ActualCondition

, d.FinalDispositionID

, d.IsNCROpen

FROM CCP_NCR.dbo.BDR_Container a INNER JOIN

CCP_NCR.dbo.bdr_ncr b ON a.bdrid = b.bdrid INNER JOIN

CCP_NCR.dbo.Container_NCR c ON a.containerid = c.containerid INNER JOIN

CCP_NCR.dbo.NCR_Master d ON d.CompColName1 = b.CompColName2

And ... give it a shot!

* Noel

Mike Good SSCertifiable Points: 7415 More actions · Answer 2

Or consider join on Actionlocation, ActionNum, CurrentYear and Revision as separate columns as alternative to computed columns--why would they have to be concatenated for the join? Do you have indexes to support all of your joins? Query analyzer's query plan will show you this. What WHERE criteria are typically used when performance is sub-par?

The index tuning wizard is no use for cross-DB queries.

karen mcavoy Newbie Points: 4 More actions · Answer 3

Mike, I originally did have separate columns and I felt that was what was slowing things down. Yes, I have indexes to support allthe joins. Thanks for your help.

karen mcavoy Newbie Points: 4 More actions · Answer 4

noel, I tried what you said, and it did seem to help! thanks for your speedy reply!

Mike Good SSCertifiable Points: 7415 More actions · Answer 5

I am positive you will not improve performance by concatenating columns for the join.

Why the join to Container_NCR (aliased as c)? I don't see c being referenced anywhere. Is this join needed for filtering out nonmatching data, or can you eliminate this join?

Desired join indexes are: bdr_ncr(bdrid), Container_NCR(containerid), and aggregate index on NCR_Master(ActionLocation, ActionNum, CurrentYear, Revision).

You have not shown your WHERE clause--is there one, and are there indexes to support it, or are you scanning BDR_Container?

If none of this helps, set showplan_text on and post your query plan here.

karen mcavoy Newbie Points: 4 More actions · Answer 6

The container_ncr join is needed to filter out unneeded data; I did create an aggregate index and sure enough it sped things up. The first time it is slow, but once the query gets cached, it is much faster! No, I am not scanning bdr_container, there are indexes to support it and at this point in time, I do not want the where clause.