Biggest view ever - how can I reduce it?..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Biggest view ever - how can I reduce it?..

Author

Topic

naorye
Starting Member

1 Post

Posted - 2010-12-12 : 20:19:58

Hi,
Once upon a time there was a View with many many Joins:
CREATE VIEW [dbo].[V_BIGGEST_VIEW_EVER]
AS
SELECT {many many columns}
FROM (SELECT * FROM dbo.T_CUS_TSK_TASK WHERE is_deleted=0) T
INNER JOIN dbo.V_CUS_GRP_GROUP G ON (T.group_id = G.group_id)
INNER JOIN dbo.T_BKK_DISCOUNT_TYPE DT ON (DT.discount_type_id=T.discount_type_id)
INNER JOIN dbo.T_BKK_CURRENCY DC ON (T.debit_currency_id=DC.currency_id)
INNER JOIN dbo.T_BKK_CURRENCY PC ON (T.payback_currency_id=PC.currency_id)
INNER JOIN dbo.T_BKK_CURRENCY FC ON (T.final_debit_currency_id=FC.currency_id)
INNER JOIN dbo.T_GLOBAL_COUNTER D1C ON (D1C.company_id=T.company_id AND
D1C.counter_name='PROFORMA_INVOICE_COUNTER')
INNER JOIN dbo.T_GLOBAL_COUNTER D2C ON (D2C.company_id=T.company_id AND
D2C.counter_name='TAX_INVOICE_COUNTER')
INNER JOIN dbo.T_GLOBAL_COUNTER D3C ON (D3C.company_id=T.company_id AND
D3C.counter_name='INVOICE_RECEIPT_COUNTER')
INNER JOIN dbo.T_GLOBAL_COUNTER D4C ON (D4C.company_id=T.company_id AND
D4C.counter_name='DELIVERY_NOTE_COUNTER')
INNER JOIN dbo.T_GLOBAL_COUNTER D5C ON (D5C.company_id=T.company_id AND
D5C.counter_name='BILL_OF_LADING_COUNTER')
INNER JOIN dbo.T_GLOBAL_COUNTER D6C ON (D6C.company_id=T.company_id AND
D6C.counter_name='CREDIT_INVOICE_COUNTER')
LEFT JOIN dbo.V_SYS_BRANCH BR ON (T.branch_id = BR.branch_id)
LEFT JOIN dbo.T_CUS_TSK_TASKS_ARRAY AR ON (T.array_id = AR.array_id)
LEFT JOIN dbo.T_DRIVER D ON (T.driver_id = D.driver_id)
LEFT JOIN dbo.T_VEHICLE V ON (T.vehicle_id = V.vehicle_id)
LEFT JOIN dbo.T_STF_INVITER I ON (T.inviter_id = I.inviter_id)
LEFT JOIN dbo.T_STF_SUBCONTRACTOR SC1 ON (SC1.subcontractor_id = D.subcontractor_id)
LEFT JOIN dbo.T_STF_SUBCONTRACTOR SC2 ON (SC2.subcontractor_id = T.subcontractor_id)
LEFT JOIN dbo.T_CUS_TSK_TASK_STATUS S ON (S.task_status_id=T.task_status_id)
LEFT JOIN dbo.V_STF_SUB_LOCATION SL1 ON (SL1.sub_location_id=T.start_sub_location_id)
LEFT JOIN dbo.V_STF_SUB_LOCATION SL2 ON (SL2.sub_location_id=T.end_sub_location_id)
LEFT JOIN dbo.T_STF_CUSTOMER CU ON (CU.customer_id=T.customer_id)
LEFT JOIN dbo.T_STF_CUSTOMER_SPLITTING_CODE SP ON (SP.splitting_id=T.splitting_id)
LEFT JOIN dbo.V_CUS_TSK_CREDIT_FOR_TASK CR ON CR.task_id=T.task_id
LEFT JOIN dbo.T_BKK_PROFORMA_INVOICE D1 ON (T.proforma_invoice_id=D1.proforma_invoice_id)
LEFT JOIN dbo.T_BKK_TAX_INVOICE D2 ON (T.tax_invoice_id=D2.tax_invoice_id)
LEFT JOIN dbo.T_BKK_INVOICE_RECEIPT D3 ON (T.invoice_receipt_id=D3.invoice_receipt_id)
LEFT JOIN dbo.T_BKK_DELIVERY_NOTE D4 ON (T.delivery_note_id=D4.delivery_note_id)
LEFT JOIN dbo.T_BKK_BILL_OF_LADING D5 ON (T.bill_of_lading_id=D5.bill_of_lading_id)
LEFT JOIN dbo.V_CUS_TSK_CONTAINER CONTAINER1 ON (CONTAINER1.container_id=T.container1_id)
LEFT JOIN dbo.V_CUS_TSK_CONTAINER CONTAINER2 ON (CONTAINER2.container_id=T.container2_id)
LEFT JOIN dbo.V_STF_TRAILER TRAILER1 ON (TRAILER1.trailer_id=T.trailer1_id)
LEFT JOIN dbo.V_STF_TRAILER TRAILER2 ON (TRAILER2.trailer_id=T.trailer2_id)
LEFT JOIN dbo.T_STF_LUGGAGE_TYPE LUGGAGE_TYPE ON (LUGGAGE_TYPE.luggage_type_id=T.luggage_type_id)

One day the user asked the view for a query:

SELECT {many many columns}
FROM V_BIGGEST_VIEW_EVER
WHERE {column1}=1 AND
{column2}=2 AND
.......
{and so and so}
.......
{columnN}=N

And the lazy biggest view ever worked and worked and after
5 minutes (!!) and not less it returns results.

Those tables had primary keys and foreign keys.

How can I reduce the time the query executes??..
How can I reduce this view??..

I was searching on google fut he couldn't help me.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-12 : 21:10:02

This looks like it's worse than you think. It's a view of views - wonder how deep it goes.

You can lok at the query plane but I would say you should get rid of the views - try putting the logic in a stored procedure then you can optimise for user queries.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources