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

[bknoch@hinda.com]

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