RE: [sqr-users] Performance issue with aggregates in SQL Server.

[bknoch@hinda.com]

-S shows the hints in there.  The query runs fast in Query Analyzer whether
the hints are in there or not, so they're probably unnecessary.

In any case, the query runs fast in SQR when you remove the aggregates, so
I'd guess it has something to do with how SQR is processing them.

Thanks,
Brian Knoch



|---------+-------------------------------------------->
|         |           "Alexander, Steve"               |
|         |           <Steven.Alexander@sanjoseca.gov> |
|         |           Sent by:                         |
|         |           sqr-users-bounces+bknoch=hinda.co|
|         |           m@sqrug.org                      |
|         |                                            |
|         |                                            |
|         |           07/21/2004 05:32 PM              |
|         |           Please respond to "This list is  |
|         |           for discussion about the SQR     |
|         |           database reporting    language   |
|         |           from Hyperion Solutions."        |
|         |                                            |
|---------+-------------------------------------------->
  
>----------------------------------------------------------------------------------------------|
  |                                                                             
                 |
  |       To:       "'This list is for discussion about the SQR database 
reportinglangu age      |
  |        from Hyperion Solutions.'" <sqr-users@sqrug.org>                     
                 |
  |       cc:                                                                   
                 |
  |       Subject:  RE: [sqr-users] Performance issue with aggregates in SQL 
Server.             |
  
>----------------------------------------------------------------------------------------------|




It may be that the Query Analyzer is using your hints
("INDEX=PSAORD_HEADER"
and "OPTION(FORCE ORDER)") but SQR is not.  Try running SQR with the "-S"
flag on the command line to get the actual text of the SQL commands in your
log file.

-----Original Message-----
From: bknoch@hinda.com [mailto:bknoch@hinda.com]
Sent: Wednesday, July 21, 2004 1:25 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Performance issue with aggregates in SQL Server.






We're running SQL Server 7 on windows NT.

I run the following query in Query Analyzer and it runs in a second or two:

BEGIN-SELECT
SUBSTRING(STH.ORDER_NO,1,3)
COUNT(DISTINCT STH.ORDER_NO)
COUNT(STL.ORDER_INT_LINE_NO)
SUM(STL.QTY_ORDERED)
SUM(ROUND(STL.QTY_ORDERED * PRICE, 2))


FROM PS_ORD_HEADER STH (INDEX=PSAORD_HEADER), PS_ORD_LINE STL
WHERE STH.BUSINESS_UNIT = $BUSINESS_UNIT
  AND STH.ORDER_DATE BETWEEN $FROM_DATE AND $THRU_DATE
  AND STH.ORDER_STATUS <> 'X'
  AND STH.BUSINESS_UNIT = STL.BUSINESS_UNIT
  AND STH.ORDER_NO = STL.ORDER_NO
  AND STL.ORD_LINE_STATUS <> 'X'
GROUP BY SUBSTRING(STH.ORDER_NO,1,3)
ORDER BY SUBSTRING(STH.ORDER_NO,1,3)
OPTION(FORCE ORDER)

However, when I run it through SQR in PeopleSoft, it takes a minute.

I redid it by removing the SUM's and COUNT's and using counters to do the
work, and it takes around 4 seconds instead of the minute.

I'd rather be able to use the COUNT's and SUM's.  Does anyone happen to
know why I'd get such a large performance hit?

Thanks,
Brian Knoch

This communication is for the exclusive use of addressee and may contain
confidential, proprietary or privileged information which may be used
exclusively for its intended purpose. If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited.  If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication, and
destroy all copies.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users



This communication is for the exclusive use of addressee and may contain
confidential, proprietary or privileged information which may be used
exclusively for its intended purpose. If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited.  If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication, and
destroy all copies.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users