View problem?

Question

Post reply

View problem?

Paul.Mitchell.

Ten Centuries

Points: 1131
More actions
March 10, 2006 at 3:41 am

#171366

I'm writing numerous reports using RS. However, some of my recent reports are running rather slowly. I suspect that it is due to the complicated view I have created. Has anyne got any suggestions for improving it? SQL is not my strong point...
SELECT DISTINCT
                      TOP 100 PERCENT ANDTELCO.dbo.CallRecord.*, dbo.LIVE_RTINVDATES.IS_InvoiceDate AS INVOICE_DATE,
                      dbo.LIVE_RTVESSELNAMES.CON_COPY_SN AS VESSELNAME, ANDGROUP.dbo.SL_ACCOUNTS.CUNAME AS CUSTNAME,
                      ANDGROUP.dbo.SL_ACCOUNTS2.CU_USRCHAR1 AS AGENT_ACC, ANDTELCO.dbo.PMVIEW_CDKINDS_USEDBYSYSTEM.Description AS CDKINDDESC,
                      dbo.LIVE_REPORT_NAME_VIEW.REPORT_NAME AS LESONAME
FROM         dbo.LIVE_RTVESSELNAMES RIGHT OUTER JOIN
                      dbo.LIVE_REPORT_NAME_VIEW RIGHT OUTER JOIN
                      ANDTELCO.dbo.CallRecord ON dbo.LIVE_REPORT_NAME_VIEW.Actual_Account = ANDTELCO.dbo.CallRecord.CR_CUCODE LEFT OUTER JOIN
                      ANDTELCO.dbo.PMVIEW_CDKINDS_USEDBYSYSTEM ON
                      ANDTELCO.dbo.CallRecord.CR_CUCODE = ANDTELCO.dbo.PMVIEW_CDKINDS_USEDBYSYSTEM.CR_CUCODE AND
                      ANDTELCO.dbo.CallRecord.CR_CDKIND = ANDTELCO.dbo.PMVIEW_CDKINDS_USEDBYSYSTEM.CR_CDKIND LEFT OUTER JOIN
                      ANDGROUP.dbo.SL_ACCOUNTS ON ANDTELCO.dbo.CallRecord.CR_SUCODE = ANDGROUP.dbo.SL_ACCOUNTS.CUCODE ON
                      dbo.LIVE_RTVESSELNAMES.CON_NUMBER = ANDTELCO.dbo.CallRecord.CR_Vessel LEFT OUTER JOIN
                      dbo.LIVE_RTINVDATES ON ANDTELCO.dbo.CallRecord.CR_InvoiceNumber = dbo.LIVE_RTINVDATES.IS_InvoiceNumber LEFT OUTER JOIN
                      ANDGROUP.dbo.SL_ACCOUNTS2 ON ANDTELCO.dbo.CallRecord.CR_SUCODE = ANDGROUP.dbo.SL_ACCOUNTS2.CUCODE2
ORDER BY ANDTELCO.dbo.CallRecord.CR_Date, ANDTELCO.dbo.CallRecord.CR_Time

Viewing 3 posts - 1 through 2 (of 2 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Is this the complex view or the slow report..?

Anyway, some thoughts.

The query example joins to other views. What do they look like?

The query returns * (all columns) - unnecessary many?

There is no filtering, so all rows involving all tables and views joined will be worked on. How many rows is this?

DISTINCT and ORDER BY, may be needed, but uses tempdb, thus adding to the performance penalty

Are there sufficient indexes in the right places on the involved tables?

How slow is 'rather slowly'?

How many rows is the expected output?

It looks a bit complicated, but since there are other views involved, it's hard to say what can be done about it.

In general, though, it's pretty common that when you start stacking views on top of each other, that performance starts to degrade. Why and when tis can happen, is entirely different and up to what each view look like and what the table(s) it's based on looks like and contains.

/Kenneth

JeffB Hall of Fame Points: 3541 More actions · Answer 2

My first bit of advice would be to stop using the TOP 100 PERCENT hack to order the view. The ordering should be done in the select from the view, not the view itself.

Second, run the SELECT in QA and analyze the Execution plan. There may be some problems with indexes not available for all the joins.

Third, are the RIGHT and LEFT joins necessary? They should only be used if needed.