UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Views And Report Performance, Any Versions    
 
   
BruceM
post Oct 11 2017, 01:14 PM
Post#1


UtterAccess VIP
Posts: 6,899
Joined: 24-May 10
From: Downeast Maine


I am continuing to slog through the process of converting an Access database to SQL Server as the back end. I have sorted out a number of things, but am defeated on the current problem. Perhaps somebody will be able to help me avoid a few weeks of trial and error.

As I said, this started as an Access database. The reports are in Access. I know that SQL Server has reporting services, but three things: I should be able to continue to use Access reports; I don't know if I can do the formatting and layout I want with reporting services; and I don't have the time it would require to figure out SQL Server reporting services, if the difficulty of the process so far is any indication.

To summarize the situation, we have documents that describe how to apply coatings to machine parts. Each document has an initial revision level, with updates as needed. A document may be used to describe the process for one or several parts. The parts processed may vary by revision.

tblProcess >> tblRevision >> tblRevParts

tblRevParts is a junction table between tblRevision and the master listing of parts (tblParts).

It may be like this:
CODE
Process 999
    Rev. 0    06/01/2015
        PartNumber1
    Rev. 1    06/30/2016
        PartNumber1  
        PartNumber2
    Rev. 2    10/01/2016
        PartNumber1
        PartNumber3


For the Access report I created a view that includes tblProcess and tblRev, and a third table with a right join to tblProcess. Please accept that the third table is needed. Its purpose is difficult to explain.
For the Part Number information I am using a subreport. I should mention that there is another table at the same level in the hierarchy as tblRevPart. For this reason, and for reasons of the report layout and formatting, I need to use subreports rather than grouping.

So, tblProcess and tblRev are combined in one SQL Server view, with tblRevPart another view. The main report is bound to the ProcessRev view, and grouped on Process, then Rev. Both linked views open quickly from Access when opened on their own.

This is essentially the structure I used with the Access database. The report took several seconds to load, which is OK (it is not run often).

However, with SQL Server as the back end it is taking a very long time. It is OK without the subreports, but I need those. The query runs well enough, but then the report gets bogged down formatting the pages. This happened even though I removed the Format event code, of which there was very little.

I noticed that SQL Server views can be indexed (the tables are all indexed, and foreign keys established as needed). I wondered if that was the problem, so I tried to create an index, only to be informed that the view was not bound to a schema. So I figured out how to do that, and tried again. This time the problem was the outer join, so I go rid of that as a test. Then the problem was it couldn't create an index on a view with a subquery.

I give up. I need the subquery. Anyhow, even if I got rid of it I am sure there would be another reason I can't create an index, such as the view using two tables or something. And I don't even know if an index will help.

I changed the report to get rid of Keep Together choices for report sections. That was one of the suggestions I found to solve sluggish formatting of reports. No help there.

Summary: the main report is based on a view that uses a subquery. The subquery is used to return just the latest revision from tblRev. The subreports are also based on views, and have the correct Link Child and Link Master fields. In Access the same structure worked well enough.

Is there something fundamentally wrong with my approach? Should I not be using views? Maybe passthrough queries would be better? If indexing the view will help maybe I can create the view without a subquery, index it, then create another view or a passthrough query to add the subquery to the indexed view. Or something like that.

One more thing I need to mention. I expect I can create a grouping query selecting the Max combination of Process and Rev, based on Rev Date, to select the latest revision. However, another requirement is to see a listing of all non-current revisions. That is where the subquery comes in, with the criteria NOT IN (SELECT TOP 1 RevID...).
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 02:24 PM
Post#2


UtterAccess VIP
Posts: 21,362
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Rather than a subquery, can you using LEFT JOIN to the table of non-current revisions and only select those rows with a Null value?

--------------------
Go to the top of the page
 
BruceM
post Oct 11 2017, 03:06 PM
Post#3


UtterAccess VIP
Posts: 6,899
Joined: 24-May 10
From: Downeast Maine


I have a table of all revisions, not specifically one for non-current ones. I could create a view of non-current revisions, then do Not In ViewName as the criterium, but I don't think I would be able to index a view that is based in part on another view. I can get the view of non-current revisions well enough. It opens quite quickly. However, it seems the

However, I should be able to create a view of current revisions only, without using a subquery, then index that view and see if I get better results when running a report of current revisions. If so, at least I will know there is a path forward.

If nothing else I can write from the view(s) to temp table(s) in the front end, and base the report on those. As a general thing I would rather not do that, but it could be the best choice here.

Part of this is that I am trying to use each obstacle as an opportunity to learn what I can about SQL Server. Considering the number of obstacles I am on track to be a genius by year's end.
Go to the top of the page
 
Doug Steele
post Oct 11 2017, 03:28 PM
Post#4


UtterAccess VIP
Posts: 21,362
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What's the SQL for your view?


--------------------
Go to the top of the page
 
BruceM
post Oct 12 2017, 07:12 AM
Post#5


UtterAccess VIP
Posts: 6,899
Joined: 24-May 10
From: Downeast Maine


Code is posted below for the main view. The view opens in good time in both SSMS and when opening the linked view directly from Access. A report based only on the view opens well enough (I suppose about 5 seconds, maybe a little more). The delay, with a stalled or very slow "Formatting Page" notification in the task bar, comes when there are subreports. The Link Child and Link Master properties are set properly. The document, with subreports, eventually opens, but much slower than the Access version.

The subreports are also based on views in which the Part and Customer lists are likewise limited. The Customer list, for instance, is of customers associated with older revisions only; for current Process records it lists only the customers associated with current revisions of active (non-archived) Process records.

The Part list is a little more complicated. Most of the time there are 1-5 Parts per Process revision, but sometimes there are more. The Part list is the TOP 5 (alphabetical) for each Revision, with another record "3 Other Parts" when there are eight (for instance) parts. A subquery causes the view to return only the TOP 5.

I realize this is all rather complicated and fussy, but that's the way it has to be to produce the required report output.

It occurs to me that having the main report based on a view with a subquery is one thing, but to have the views for related records also include subqueries may be the source of the bottleneck, as it involves a lot of processing for each record. With that in mind I am going to try basing the subqueries on tables. For the Customer listing perhaps I would do better to use the whole customer table, which would be more customer records than are needed, but this could be a case where limiting the recordset is counterproductive. I don't know if I explained that well, but I'll leave it at that.

On to the view. tblPBreak is because the report (an index) accompanies printed copies of the Process documents, which are stored in binders. tblPBreak lists the Process documents at which index goes to a new page for the next binder. Code in the Format event makes a manual page break visible (in effect, this "activates" the page break) for the record at which a new page is started. Archive documents are stored. It's more complicated than that since revisions more than three years old are moved out of binders and into more long term storage, and other details. It is a highly regulated industry, which necessitates the need to store old documents.

The idea is to list all revisions, their part number lists, and also the customers for whom the parts are processed. That list, typically just 1-3 customers, can change from one revision to the next. The part number list likewise can change from one revision to the next.

The listing is of all revisions but the most recent one for archived process documents, and all revisions for archived documents. For current revisions it is the opposite: just the latest revision of active (not archived) Process records.
CODE
SELECT
    PR.ProcID,
    PR.SME,
    R.RevLevel,
    R.RevDate,
    R.RevID,
    PR.Archived,
    PB.PB_ProcID
FROM
    (
        dbo.tblPBreak AS PB
            RIGHT JOIN dbo.tblProc AS PR
            ON PB.PB_ProcID = PR.ProcID
    )
    INNER JOIN dbo.tblRev AS R
    ON PR.ProcID = R.R_PlanID
WHERE
    (
        R.RevID Not In
            (
                SELECT TOP 1 RevID
                FROM dbo.tblRev AS R2
                WHERE R2.R_ProcID = R.R_ProcID
                ORDER BY R2.RevDate DESC
            )
            AND
        PR.Archived = 0
    )
        OR
    PR.Archived = 1
Go to the top of the page
 
Doug Steele
post Oct 12 2017, 07:55 AM
Post#6


UtterAccess VIP
Posts: 21,362
Joined: 8-January 07
From: St. Catharines, ON (Canada)


See whether this works:

CODE
SELECT
    PR.ProcID,
    PR.SME,
    R.RevLevel,
    R.RevDate,
    R.RevID,
    PR.Archived,
    PB.PB_ProcID
FROM
    (
        dbo.tblPBreak AS PB
            RIGHT JOIN dbo.tblProc AS PR
            ON PB.PB_ProcID = PR.ProcID
    )
    INNER JOIN dbo.tblRev AS R
    ON PR.ProcID = R.R_PlanID
    LEFT JOIN
    (
         SELECT TOP 1 RevID
         FROM dbo.tblRev AS R2
         WHERE R2.R_ProcID = R.R_ProcID
         ORDER BY R2.RevDate DESC
     ) AS C
    ON R.RevID = C.RecID
WHERE
    (
        C.RevID IS NULL
        AND
        PR.Archived = 0
    )
        OR
    PR.Archived = 1

--------------------
Go to the top of the page
 
Minty
post Oct 12 2017, 08:04 AM
Post#7



Posts: 65
Joined: 5-July 16



I have had a similar problem with a Hierarchical equipment listing, originally a crystal report embedded into access.
Due to version changes and us moving away from crystal, I converted it to an access report.

However because the hierarchy was done initially as sub reports it began to take 5 minutes plus to format and export to pdf if there were more than 100 or so items in the listing.

My solution was to create the data in the required order and format as a temp table, and include a hierarchy "level" and simply indent the lines and hide information based on the level to give the required look.
The table is stored locally from a stored procedure, so all the leg work is done on the server, but if it's not query time but the sub-report formatting a gazzillion times (it will be reformatted every time its called) that appears to be causing problems, you can create the temp table in access.
This may be a solution for you.
Go to the top of the page
 
BruceM
post Oct 12 2017, 12:30 PM
Post#8


UtterAccess VIP
Posts: 6,899
Joined: 24-May 10
From: Downeast Maine


Dennis, I renamed some of the columns to make the intent clearer, and misnamed one of them. This:

ON PR.ProcID = R.R_PlanID

should have been:

ON PR.ProcID = R.R_ProcID

I think I see what you're getting at. However, I received an error that the multi-part identifier R.R_ProcID could not be bound. I tried using a different field name in each place in the view (one of which is the mistakenly named R_PlanID, which I changed to R_ProcID) where R.R_ProcID is used. This wouldn't return any records if the query compiled correctly, but I didn't expect that. Rather, I used the rename to let me know where the problem occurred. It turns out to have been the instance of R.R_ProcID within the TOP 1 subquery. I can't sort out how to fix it.

Anyhow, the current view returns results well enough, so I am going to conduct more experiments to figure out how best to approach this. I expect the answer will apply to other databases, which is how I try to encourage myself as I wade through the difficult conversion.

Minty, it may well turn out that temp tables (or more likely, a front end table that is populated temporarily) are the way to go. That will be among the things I test.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2017 - 12:31 PM