Home » RDBMS Server » Server Administration » Speeding up a query
Speeding up a query [message #370738] Wed, 26 January 2000 10:01 Go to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Could someone take a look at the query I've written and tell me if there's a way to speed it up. It's so slow in fact that after the first hour I gave up. As you'll notice I have to go into one table three times. Essentially I'm trying to match up users with their package types and their usage. Ideally I want to group all users by package type. Any help would be greatly appreciated.

Thank You

SELECT
USR."USR_ID",
A."VALUE", B."VALUE", USR."DOM_ID",
USR."BILLINGID", ACCT_BACKUP."TYPE", ACCT_BACKUP."DELTA"

FROM
USR, SVC_DATA A, SVC_DATA B, SVC_DATA C, ACCT_BACKUP

WHERE
A.ATTR_NAME = 'PRICING_PLAN_NUMBER' AND A.ENTITY_ID = 50001042
AND B.ATTR_NAME = 'PRICING_PLAN_TITLE' AND B.ENTITY_ID = 50001042 AND B.SUBSCRIPT = A.SUBSCRIPT
AND C.ATTR_NAME = 'CHOSEN_PRICING_PLAN' AND C.ENTITY_ID = USR.USR_ID AND C.VALUE = A.VALUE
AND USR.DOM_ID = 1278
AND USR.BILLINGID = ACCT_BACKUP.BILLINGID
AND ACCT_BACKUP.TYPE = 2
AND ACCT_BACKUP.CREATED >= '01-JAN-2000' AND ACCT_BACKUP.CREATED <=

ORDER BY
USR."USR_ID" ASC
Re: Speeding up a query [message #370739 is a reply to message #370738] Wed, 26 January 2000 18:01 Go to previous message
Sreeni
Messages: 6
Registered: January 2000
Junior Member
Hi Chris,
Try out this , it should help you better
SELECT /* ALL ROWS */
USR."USR_ID",
A."VALUE", B."VALUE", USR."DOM_ID",
USR."BILLINGID", ACCT_BACKUP."TYPE", ACCT_BACKUP."DELTA"
FROM
ACCT_BACKUP,
USR,
SVC_DATA C,
SVC_DATA B,
SVC_DATA A
WHERE
A.ENTITY_ID = 50001042
and A.ATTR_NAME = 'PRICING_PLAN_NUMBER'
AND A.SUBSCRIPT = B.SUBSCRIPT
AND B.ENTITY_ID = 50001042
AND B.ATTR_NAME = 'PRICING_PLAN_TITLE'

AND A.VALUE = c.VALUE
AND C.ATTR_NAME = 'CHOSEN_PRICING_PLAN'
AND C.ENTITY_ID = USR.USR_ID
AND USR.DOM_ID = 1278
AND USR.BILLINGID = ACCT_BACKUP.BILLINGID
AND ACCT_BACKUP.TYPE = 2
AND ACCT_BACKUP.CREATED BETWEEN '01-JAN-2000' and '05-JAN-2000'
--AND ACCT_BACKUP.CREATED >= '01-JAN-2000'
--AND ACCT_BACKUP.CREATED <= <BR 05-JAN-2000>ORDER BY
USR."USR_ID" ASC
Previous Topic: Sum a char field
Next Topic: SQL PlusPlus 2.0 Released-freeware
Goto Forum:
  


Current Time: Thu Mar 28 17:08:58 CDT 2024