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.
| Author |
Topic |
|
gunjan.sh
Starting Member
1 Post |
 Posted - 2008-04-04 : 00:16:50
|
| Hi, I have a view which returns around 2.8 million records. And is taking more time to execute.The View has around 11 tables in which some of the tables are left joined... This is resulting in a huge data..... Is there any way I can make the execution faster? Here is my view CREATE VIEW dbo.vw_model_invoicedetail AS SELECT a.INVOICE_DETAIL_ID, b.INVOICE_HEADER_ID, a.LINE_ITEM_CODE, c.LINE_ITEM_DESCRIPTION, d.GROUP_TITLE, a.QUANTITY, a.RATE, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 1)) AS date_ordered, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_5 WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 2)) AS date_completed, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_4 WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 3)) AS date_cancelled, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_3 WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 4)) AS date_service_begin, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_2 WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 5)) AS date_service_end, (SELECT DATE_OCCURRED FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_1 WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 6)) AS date_open_date, h.NOTES, a.QUANTITY * a.RATE AS TotalBeforeAdjustments, f.DISPLAY_NAME AS created_by, a.CREATED, g.ADJUST_AMOUNT_BY, g.REASON_FOR_ADJUSTMENT, a.QUANTITY * a.RATE + ISNULL(g.ADJUST_AMOUNT_BY, 0) AS GrandTotal, g.ADJUSTED, i.DISPLAY_NAME AS adjusted_by, k.ADJUSTMENT_REASON FROM dbo.INVOICE_DETAIL AS a WITH (nolock) INNER JOIN dbo.INVOICE_HEADER AS b WITH (nolock) ON a.INVOICE_HEADER_ID = b.INVOICE_HEADER_ID INNER JOIN dbo.LINE_ITEMS AS c WITH (nolock) ON a.LINE_ITEM_CODE = c.LINE_ITEM_CODE INNER JOIN dbo.LINE_ITEM_GROUPS AS d WITH (nolock) ON a.GROUP_ID = d.GROUP_ID INNER JOIN dbo.INVOICE_DETAIL_DATES AS e WITH (nolock) ON a.INVOICE_DETAIL_ID = e.INVOICE_DETAIL_ID INNER JOIN ICLEAR_CENTRALDB.dbo.USER_CONTACT_INFO AS f WITH (nolock) ON a.CREATED_BY = f.CONTACT_INFO_ID LEFT OUTER JOIN dbo.INVOICE_ADJUSTMENTS AS g WITH (nolock) ON a.INVOICE_DETAIL_ID = g.INVOICE_DETAIL_ID LEFT OUTER JOIN dbo.INVOICE_NOTES AS h WITH (nolock) ON a.INVOICE_DETAIL_ID = h.INVOICE_DETAIL_ID LEFT OUTER JOIN ICLEAR_CENTRALDB.dbo.USER_ACCOUNTS AS j WITH (nolock) ON g.ADJUSTED_BY = j.ACCOUNT_ID LEFT JOIN ICLEAR_CENTRALDB.dbo.USER_CONTACT_INFO AS i WITH (nolock) ON j.CONTACT_INFO_ID = i.CONTACT_INFO_ID LEFT JOIN dbo.INVOICE_ADJUSTMENT_REASONS AS k WITH (nolock) ON g.ADJUSTMENT_REASON_ID = k.ADJUSTMENT_REASON_ID The Tables are having indexes. Is there any way to reduce execution time without splitting the view into two views .. one with subqueries and the other with remaining columns.Kindly Help!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-04 : 00:20:52
|
| How about you filter the data with a WHERE clause? You can't expect any query to run fast when it is returning a large record set, no matter how many indexes you add!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
bbasir
Yak Posting Veteran
76 Posts |
Posted - 2008-04-04 : 14:12:08
|
| You can try creating Materialized Views that may help... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-04 : 14:18:46
|
| Look 'Indexed views' in BOL. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-04 : 14:27:43
|
| Nothing will make this fast. You can't return 2.8 million records fast.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
View returns huge data,taking more time