| Speeding up a query [message #370738] |
Wed, 26 January 2000 10:01  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
