Forum

I need to understand the load of database to see bottlenecks. For that I want to create a frequency distribution by hours for all documents. Can someone help with that? The problem is that the Data composition schema does not allow to sel ect from all documents, but from a specific one only. Here is my query:

SEL ECT
   HOUR(CustomerOrder.Date) AS Hour,
   COUNT(CustomerOrder.Ref) AS Count
FR OM
   Document.CustomerOrder AS CustomerOrder

GROUP BY
   HOUR(CustomerOrder.Date)

I made it myself:

SEL ECT
   0 AS Hour
INTO TTHours

UNI ON

SEL ECT
   1

UNI ON

SEL ECT
   2

UNI ON

SEL ECT
   3

UNI ON

SEL ECT
   4

UNI ON

SEL ECT
   5

UNI ON

SEL ECT
   6

UNI ON

SEL ECT
   7

UNI ON

SEL ECT
   8

UNI ON

SEL ECT
   9

UNI ON

SEL ECT
   10

UNI ON

SEL ECT
   11

UNI ON

SEL ECT
   12

UNI ON

SEL ECT
   13

UNI ON

SEL ECT
   14

UNI ON

SEL ECT
   15

UNI ON

SEL ECT
   16

UNI ON

SEL ECT
   17

UNI ON

SEL ECT
   18

UNI ON

SEL ECT
   19

UNI ON

SEL ECT
   20

UNI ON

SEL ECT
   21

UNI ON

SEL ECT
   22

UNI ON

SEL ECT
   23
;

///
SEL ECT
   Hours.Hour,
   SUM(ISNULL(Documents.Count, 0)) AS Count
FROM
   TTHours AS Hours
      LEFT JOIN (SELECT
         AllDocuments.Hour AS Hour,
         AllDocuments.Count AS Count
      FR OM
         (SELECT
            HOUR(CustomerOrder.Date) AS Hour,
            COUNT(CustomerOrder.Ref) AS Count
         FR OM
            Document.CustomerOrder AS CustomerOrder
         
         GROUP BY
            HOUR(CustomerOrder.Date)
         
         UNION
         
         SEL ECT
            HOUR(Invoice.Date),
            COUNT(Invoice.Ref)
         FR OM
            Document.Invoice AS Invoice
         
         GROUP BY
            HOUR(Invoice.Date)
         UNION
         ...
      ) AS AllDocuments) AS Documents
      ON (Documents.Hour = Hours.Hour)

GROUP BY
   Hours.Hour

I try it, and get the next (add attachments). And there are not second problem. I attach report.

> Data composition schema does not allow to select from all documents
Yes, it so. In such cases I make query for schema programmatically

Hi, Ivan!
I tried to open your external report, but got the following error:
Error performing file operation 'C:\Users\Stephanie\Desktop\ExternalReport1.erf'
because:
Invalid data storage format 'file://C:/Users/Stephanie/Desktop/ExternalReport1.erf'

Stephanie, Are you sure that file download correctly?
Try open file in Designer. May be we have different versions of platform.
I check in version: 1C:Enterprise 8.2 (8.2.16.368)

Yes, redownloaded it using FF, it works. And I see no difference from what I did. The second problem I have solved myself using the TTHours with 0-23 values for hours.
You said you can solve the first problem: to generate a query programmatically for Data composition schema. Can you show an example?

I made an example. See "Procedure OnComposeResult" in "Object module"
And also changed a query in "MainDataCompositionSchema"

Thank you, Ivan. I couldn't understand the comments, but it works like a charm!

By the way, I've got where's the problem: I have to click Download instead of clicking a file name.

> it works like a charm!
Great! i'm happy) There is nothing in the comments, I forgot delete them.