Post reply

Speedy Sub-Queries??

  • September 2, 2005 at 8:41 am

    #168589

    We have a VB application that is used by all our employees. Some of the info that is on the main screen is produced based on a few sub-queries in our main view. Problem is the sub-queries slow down the population of data. There is a dramatic difference when we remove the sub-queries. Is there a way to improve the performance of sub-queries??  Any suggestions to documentation,resources or general ideas is appreciated!

  • September 2, 2005 at 8:46 am

    #587247

    Move to derived tables/joins.

    Can we see the view definition?

  • September 2, 2005 at 9:08 am

    #587260

    rqR'us here ya go:  (view definition I'll have to remember that one!)

    CREATE VIEW dbo.v_MTSTM_Main

    as

    select    

    ord.ID as ORDER_ID,

    ord.NUMBER as ORDER_NUMBER,

    ord.ENTERED_ID,

    ord.CANCELLED,

    ord.BROKER_ID,

    ord.AGENT_ID,

    ord.BILLTO_ID,

    ord.STATUS as ORDER_STATUS,

    ORDERSTATUSTABLE.STATUS_NAME as ORDER_STATUS_NAME,

    ord.EQUIPTYPE_REQUESTED_ID as EQUIPMENT_REQUESTED_ID,

    ord.EQUIPTYPE_REQUESTED_SIZE as EQUIPMENT_SIZE,

    ord.MASTER_BOL,

    (select top 1 a.BP_VALUE from t_MTSTM_STOP_BOLPO a join (select ID from t_MTSTM_STOP where ACTIVE = 1 and ORDER_ID = ord.ID) b on a.STOP_ID = b.ID where BP_TYPE = 'P' and BP_VALUE is not null and BP_VALUE <> '' and ACTIVE = 1) as MASTER_PO,

    ord.CONTAINER_NO,

    ord.DIVISION_ID,

    shp.ID as SHIPMENT_ID,

    shp.ORIGDEST,

    shp.CARRIER_ID,

    shp.MODE_ID,

    shp.STATUS as SHIPMENT_STATUS,

    SHIPMENTSTATUSTABLE.STATUS_NAME as SHIPMENT_STATUS_NAME,

    shp.EQUIPMENT_ACTUAL_ID,

    shp.EQUIPMENT_ACTUAL_SIZE,

    shp.NUMBER as SHIPMENT_NUMBER,

    shp.REMITTO_ID,

    shp.CARRIER_REFERENCE,

    CARRDIRTABLE.CITY_ID as CARRIER_CITY_ID,

    CARRDIRTABLE.STATE_ID as CARRIER_STATE_ID,

    CARRDIRTABLE.COUNTRY_ID as CARRIER_COUNTRY_ID,

    CARRDIRTABLE.KEYCONTACT_ID as CARRIER_KEYCONTACT_ID,

    ord.CALLED,

    ord.ENTERED,

    CONFIRMTABLE.CONFIRMED,

    convert(nvarchar, stpShpr.NEEDED_FROM, 1) as PICKUPDATE,

    stpShpr.NEEDED_FROM as PICKUPTIMEFROM,

    stpShpr.NEEDED_TO as PICKUPTIMETO,

    stpShpr.ACTUAL_IN as PICKUPACTUALIN,

    stpShpr.ACTUAL_OUT as PICKUPACTUALOUT,

    convert(nvarchar, stpCons.NEEDED_FROM, 1) as DELIVERDATE,

    stpCons.NEEDED_FROM as DELIVERTIMEFROM,

    stpCons.NEEDED_TO as DELIVERTIMETO,

    stpCons.ACTUAL_IN as DELIVERACTUALIN,

    stpCons.ACTUAL_OUT as DELIVERACTUALOUT,

    SHIPDIRTABLE.ID as SHIPPER_ID,

    SHIPDIRTABLE.CITY_ID as SHIPPER_CITY_ID,

    SHIPDIRTABLE.STATE_ID as SHIPPER_STATE_ID,

    SHIPDIRTABLE.COUNTRY_ID as SHIPPER_COUNTRY_ID,

    SHIPDIRTABLE.ZIP as SHIPPER_ZIP,

    SHIPDIRTABLE.KEYBILLTO_ID as SHIPPER_BILLTO_ID,

    SHIPSTOPTABLE.ID as SHIPPER_STOP_ID,

    SHIPDIRTABLE.KEYCONTACT_ID as SHIPPER_KEYCONTACT_ID,

    ORIGDIRTABLE.ID as ORIGIN_ID,

    ORIGDIRTABLE.CITY_ID as ORIGIN_CITY_ID,

    ORIGDIRTABLE.STATE_ID as ORIGIN_STATE_ID,

    ORIGDIRTABLE.COUNTRY_ID as ORIGIN_COUNTRY_ID,

    ORIGDIRTABLE.ZIP as ORIGIN_ZIP,

    ORIGDIRTABLE.KEYBILLTO_ID as ORIGIN_BILLTO_ID,

    ORIGSTOPTABLE.ID as ORIGIN_STOP_ID,

    ORIGDIRTABLE.KEYCONTACT_ID as ORIGIN_KEYCONTACT_ID,

    CONSDIRTABLE.ID as CONSIGNEE_ID,

    CONSDIRTABLE.CITY_ID as CONSIGNEE_CITY_ID,

    CONSDIRTABLE.STATE_ID as CONSIGNEE_STATE_ID,

    CONSDIRTABLE.COUNTRY_ID as CONSIGNEE_COUNTRY_ID,

    CONSDIRTABLE.ZIP as CONSIGNEE_ZIP,

    CONSDIRTABLE.KEYBILLTO_ID as CONSIGNEE_BILLTO_ID,

    CONSSTOPTABLE.ID as CONSIGNEE_STOP_ID,

    CONSDIRTABLE.KEYCONTACT_ID as CONSIGNEE_KEYCONTACT_ID,

    DESTDIRTABLE.ID as DESTINATION_ID,

    DESTDIRTABLE.CITY_ID as DESTINATION_CITY_ID,

    DESTDIRTABLE.STATE_ID as DESTINATION_STATE_ID,

    DESTDIRTABLE.COUNTRY_ID as DESTINATION_COUNTRY_ID,

    DESTDIRTABLE.ZIP as DESTINATION_ZIP,

    DESTDIRTABLE.KEYBILLTO_ID as DESTINATION_BILLTO_ID,

    DESTSTOPTABLE.ID as DESTINATION_STOP_ID,

    DESTDIRTABLE.KEYCONTACT_ID as DESTINATION_KEYCONTACT_ID,

    CONSSTOPTABLE.MILES_SHIPMENT,

    CARGOTABLE.CARGOTYPE_ID as CARGO_ID

    from t_MTSTM_ORDER ord

    left outer join t_MTSTM_SHIPMENT shp on shp.ORDER_ID = ord.ID

    left outer join (select ORDER_ID, DIRECTORY_ID, ID from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID = 8583) SHIPSTOPTABLE on SHIPSTOPTABLE.ORDER_ID = ord.ID

    left outer join (select top 100 percent ORDER_ID, SHIPMENT_ID, DIRECTORY_ID, ID from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID IN (8583, 7923) order by STOPTYPE_ID) ORIGSTOPTABLE on ORIGSTOPTABLE.SHIPMENT_ID = shp.ID

    left outer join (select ORDER_ID, DIRECTORY_ID, MILESTONEXTSTOP as MILES_SHIPMENT, ID from t_MTSTM_STOP where ACTIVE = 1 AND STOPTYPE_ID = 8584) CONSSTOPTABLE on CONSSTOPTABLE.ORDER_ID = ord.ID

    left outer join (select top 100 percent ORDER_ID, SHIPMENT_ID, DIRECTORY_ID, ID from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID IN (8584, 7924) order by STOPTYPE_ID) DESTSTOPTABLE on DESTSTOPTABLE.SHIPMENT_ID = shp.ID

    left outer join (select ID, COMPANY, ZIP, KEYCONTACT_ID, CITY_ID, STATE_ID, COUNTRY_ID, KEYBILLTO_ID from t_MTSTM_DIRECTORY) SHIPDIRTABLE on SHIPDIRTABLE.ID = SHIPSTOPTABLE.DIRECTORY_ID

    left outer join (select ID, COMPANY, ZIP, KEYCONTACT_ID, CITY_ID, STATE_ID, COUNTRY_ID, KEYBILLTO_ID from t_MTSTM_DIRECTORY) ORIGDIRTABLE on ORIGDIRTABLE.ID = ORIGSTOPTABLE.DIRECTORY_ID

    left outer join (select ID, COMPANY, ZIP, KEYCONTACT_ID, CITY_ID, STATE_ID, COUNTRY_ID, KEYBILLTO_ID from t_MTSTM_DIRECTORY) CONSDIRTABLE on CONSDIRTABLE.ID = CONSSTOPTABLE.DIRECTORY_ID

    left outer join (select ID, COMPANY, ZIP, KEYCONTACT_ID, CITY_ID, STATE_ID, COUNTRY_ID, KEYBILLTO_ID from t_MTSTM_DIRECTORY) DESTDIRTABLE on DESTDIRTABLE.ID = DESTSTOPTABLE.DIRECTORY_ID

    left outer join (select ID, COMPANY, ZIP, KEYCONTACT_ID, CITY_ID, STATE_ID, COUNTRY_ID, KEYBILLTO_ID from t_MTSTM_DIRECTORY) CARRDIRTABLE on CARRDIRTABLE.ID = shp.CARRIER_ID

    left outer join (select STATUS_NO, STATUS_NAME from t_MTSTM_STATUS where ACTIVE = 1 and TYPE = 'ORDER') ORDERSTATUSTABLE on ORDERSTATUSTABLE.STATUS_NO = ord.STATUS

    left outer join (select STATUS_NO, STATUS_NAME from t_MTSTM_STATUS where ACTIVE = 1 and TYPE = 'SHIPMENT') SHIPMENTSTATUSTABLE on SHIPMENTSTATUSTABLE.STATUS_NO = shp.STATUS

    left outer join (select ORDER_ID, NEEDED_FROM, NEEDED_TO, ACTUAL_IN, ACTUAL_OUT from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID = 8583) stpShpr on stpShpr.ORDER_ID = ord.ID

    left outer join (select ORDER_ID, NEEDED_FROM, NEEDED_TO, ACTUAL_IN, ACTUAL_OUT from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID = 8584) stpCons on stpCons.ORDER_ID = ord.ID

    left outer join (select c.ID as ORDER_ID,max(e.CARGOTYPE_ID) as CARGOTYPE_ID from t_MTSTM_ORDER as c join (select ORDER_ID, max(CARGOTYPE_ID) as CARGOTYPE_ID from t_MTSTM_CARGO group by ORDER_ID) as f on c.id = f.ORDER_ID join t_MTSTM_CARGO as e on e.ORDER_ID = f.ORDER_ID and e.CARGOTYPE_ID = f.CARGOTYPE_ID group by c.ID) CARGOTABLE on CARGOTABLE.ORDER_ID = ord.ID

    left outer join (select ORDER_ID,min(ENTERED) as CONFIRMED from t_MTSTM_SHIPMENT_LOG where LOGTYPE_ID = 8648 group by ORDER_ID) CONFIRMTABLE on CONFIRMTABLE.ORDER_ID = ord.ID

    where (ord.ACTIVE = 1) and (shp.ACTIVE = 1)

     

     

  • September 2, 2005 at 9:09 am

    #587261

    I haven't heard of derived table/joins.

  • September 2, 2005 at 9:15 am

    #587268

    Do you have Indexes on all these tables you LEFT OUTER JOIN?  Also, would it be quicker to make these LEFT OUTER JOINs their own Views as those are already compiled?  Obviously, that would make for a lot of Views and someone who knows better than I may already know that is a bad approach... 

    I wasn't born stupid - I had to study.

  • September 2, 2005 at 9:18 am

    #587270

    This is what I meant but since you are already doing it...

    (left outer join (select ORDER_ID, DIRECTORY_ID, ID from t_MTSTM_STOP where ACTIVE = 1 and STOPTYPE_ID = 8583) SHIPSTOPTABLE on SHIPSTOPTABLE.ORDER_ID = ord.ID).

    Is there any way in the world you could swap the left joins to inner joins (at least some of them)?

    Can you run this and send me the execution plan?

    SET SHOWPLAN_TEXT ON

    GO

    Select 'query here'

    GO

    SET SHOWPLAN_TEXT OFF

  • September 2, 2005 at 9:20 am

    #587273

    Too many views can cause problems on the long run.. and that's a very long run .

  • September 2, 2005 at 9:29 am

    #587283

    rqR'us I ran your query:

     

      |--Compute Scalar(DEFINE[a].[BP_VALUE]=[a].[BP_VALUE], [Expr1042]=Convert([t_MTSTM_STOP].[NEEDED_FROM]), [Expr1043]=Convert([t_MTSTM_STOP].[NEEDED_FROM])))

           |--Nested Loops(Left Outer Join, OUTER REFERENCES[ord].[ID]))

                |--Hash Match(Right Outer Join, HASH[t_MTSTM_SHIPMENT_LOG].[ORDER_ID])=([ord].[ID]), RESIDUAL[t_MTSTM_SHIPMENT_LOG].[ORDER_ID]=[ord].[ID]))

                |    |--Hash Match(Aggregate, HASH[t_MTSTM_SHIPMENT_LOG].[ORDER_ID]), RESIDUAL[t_MTSTM_SHIPMENT_LOG].[ORDER_ID]=[t_MTSTM_SHIPMENT_LOG].[ORDER_ID]) DEFINE[Expr1008]=MIN([t_MTSTM_SHIPMENT_LOG].[ENTERED])))

                |    |    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_SHIPMENT_LOG].[PK_T_SHIPMENT_LOG]), WHERE[t_MTSTM_SHIPMENT_LOG].[LOGTYPE_ID]=8648))

                |    |--Hash Match(Right Outer Join, HASH[c].[ID])=([ord].[ID]))

                |         |--Hash Match(Aggregate, HASH[c].[ID]) DEFINE[Expr1005]=MAX([e].[CARGOTYPE_ID])))

                |         |    |--Hash Match(Inner Join, HASH[e].[ORDER_ID])=([c].[ID]))

                |         |         |--Hash Match(Inner Join, HASH[t_MTSTM_CARGO].[ORDER_ID], [Expr1002])=([e].[ORDER_ID], [e].[CARGOTYPE_ID]), RESIDUAL[t_MTSTM_CARGO].[ORDER_ID]=[e].[ORDER_ID] AND [e].[CARGOTYPE_ID]=[Expr1002]))

                |         |         |    |--Hash Match(Aggregate, HASH[t_MTSTM_CARGO].[ORDER_ID]), RESIDUAL[t_MTSTM_CARGO].[ORDER_ID]=[t_MTSTM_CARGO].[ORDER_ID]) DEFINE[Expr1002]=MAX([t_MTSTM_CARGO].[CARGOTYPE_ID])))

                |         |         |    |    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_CARGO].[PK_T_CARGO]))

                |         |         |    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_CARGO].[PK_T_CARGO] AS [e]))

                |         |         |--Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_ORDER].[NUMBER] AS [c]))

                |         |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[ORDER_ID])=([ord].[ID]), RESIDUAL[t_MTSTM_STOP].[ORDER_ID]=[ord].[ID]))

                |              |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND [t_MTSTM_STOP].[STOPTYPE_ID]=8584))

                |              |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[ORDER_ID])=([ord].[ID]), RESIDUAL[t_MTSTM_STOP].[ORDER_ID]=[ord].[ID]))

                |                   |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND [t_MTSTM_STOP].[STOPTYPE_ID]=8583))

                |                   |--Hash Match(Right Outer Join, HASH[Expr1049])=([shp].[STATUS]), RESIDUAL[Expr1049]=[shp].[STATUS]))

                |                        |--Compute Scalar(DEFINE[Expr1049]=Convert([t_MTSTM_STATUS].[STATUS_NO])))

                |                        |    |--Filter(WHERE[t_MTSTM_STATUS].[TYPE]='SHIPMENT'))

                |                        |         |--Bookmark Lookup(BOOKMARK[Bmk1031]), OBJECT[MTS].[dbo].[t_MTSTM_STATUS]))

                |                        |              |--Index Seek(OBJECT[MTS].[dbo].[t_MTSTM_STATUS].[ACTIVE]), SEEK[t_MTSTM_STATUS].[ACTIVE]=1) ORDERED FORWARD)

                |                        |--Hash Match(Right Outer Join, HASH[Expr1050])=([ord].[STATUS]), RESIDUAL[Expr1050]=[ord].[STATUS]))

                |                             |--Compute Scalar(DEFINE[Expr1050]=Convert([t_MTSTM_STATUS].[STATUS_NO])))

                |                             |    |--Filter(WHERE[t_MTSTM_STATUS].[TYPE]='ORDER'))

                |                             |         |--Bookmark Lookup(BOOKMARK[Bmk1029]), OBJECT[MTS].[dbo].[t_MTSTM_STATUS]))

                |                             |              |--Index Seek(OBJECT[MTS].[dbo].[t_MTSTM_STATUS].[ACTIVE]), SEEK[t_MTSTM_STATUS].[ACTIVE]=1) ORDERED FORWARD)

                |                             |--Hash Match(Right Outer Join, HASH[t_MTSTM_DIRECTORY].[ID])=([shp].[CARRIER_ID]), RESIDUAL[t_MTSTM_DIRECTORY].[ID]=[shp].[CARRIER_ID]))

                |                                  |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_DIRECTORY].[PK_T_DIRECTORY]))

                |                                  |--Hash Match(Right Outer Join, HASH[t_MTSTM_DIRECTORY].[ID])=([t_MTSTM_STOP].[DIRECTORY_ID]), RESIDUAL[t_MTSTM_DIRECTORY].[ID]=[t_MTSTM_STOP].[DIRECTORY_ID]))

                |                                       |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_DIRECTORY].[PK_T_DIRECTORY]))

                |                                       |--Hash Match(Right Outer Join, HASH[t_MTSTM_DIRECTORY].[ID])=([t_MTSTM_STOP].[DIRECTORY_ID]), RESIDUAL[t_MTSTM_DIRECTORY].[ID]=[t_MTSTM_STOP].[DIRECTORY_ID]))

                |                                            |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_DIRECTORY].[PK_T_DIRECTORY]))

                |                                            |--Hash Match(Right Outer Join, HASH[t_MTSTM_DIRECTORY].[ID])=([t_MTSTM_STOP].[DIRECTORY_ID]), RESIDUAL[t_MTSTM_DIRECTORY].[ID]=[t_MTSTM_STOP].[DIRECTORY_ID]))

                |                                                 |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_DIRECTORY].[PK_T_DIRECTORY]))

                |                                                 |--Hash Match(Right Outer Join, HASH[t_MTSTM_DIRECTORY].[ID])=([t_MTSTM_STOP].[DIRECTORY_ID]), RESIDUAL[t_MTSTM_DIRECTORY].[ID]=[t_MTSTM_STOP].[DIRECTORY_ID]))

                |                                                      |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_DIRECTORY].[PK_T_DIRECTORY]))

                |                                                      |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[SHIPMENT_ID])=([shp].[ID]), RESIDUAL[t_MTSTM_STOP].[SHIPMENT_ID]=[shp].[ID]))

                |                                                           |--Sort(ORDER BY[t_MTSTM_STOP].[STOPTYPE_ID] ASC))

                |                                                           |    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND ([t_MTSTM_STOP].[STOPTYPE_ID]=7924 OR [t_MTSTM_STOP].[STOPTYPE_ID]=85

                |                                                           |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[ORDER_ID])=([ord].[ID]), RESIDUAL[t_MTSTM_STOP].[ORDER_ID]=[ord].[ID]))

                |                                                                |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND [t_MTSTM_STOP].[STOPTYPE_ID]=8584))

                |                                                                |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[SHIPMENT_ID])=([shp].[ID]), RESIDUAL[t_MTSTM_STOP].[SHIPMENT_ID]=[shp].[ID]))

                |                                                                     |--Sort(ORDER BY[t_MTSTM_STOP].[STOPTYPE_ID] ASC))

                |                                                                     |    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND ([t_MTSTM_STOP].[STOPTYPE_ID]=7923 OR [t_MTSTM_STOP].[STOPT

                |                                                                     |--Hash Match(Right Outer Join, HASH[t_MTSTM_STOP].[ORDER_ID])=([ord].[ID]), RESIDUAL[t_MTSTM_STOP].[ORDER_ID]=[ord].[ID]))

                |                                                                          |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[PK_T_STOP]), WHERE[t_MTSTM_STOP].[ACTIVE]=1 AND [t_MTSTM_STOP].[STOPTYPE_ID]=8583))

                |                                                                          |--Merge Join(Inner Join, MERGE[ord].[ID])=([shp].[ORDER_ID]), RESIDUAL[ord].[ID]=[shp].[ORDER_ID]))

                |                                                                               |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_ORDER].[PK_T_ORDER] AS [ord]),  WHERE[ord].[ACTIVE]=1) ORDERED FORWARD)

                |                                                                               |--Sort(ORDER BY[shp].[ORDER_ID] ASC))

                |                                                                                    |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_SHIPMENT].[PK_T_SHIPMENT] AS [shp]), WHERE[shp].[ACTIVE]=1))

                |--Hash Match(Cache, HASH[ord].[ID]), RESIDUAL[ord].[ID]=[ord].[ID]))

                     |--Top(1)

                          |--Compute Scalar(DEFINE[a].[BP_VALUE]=[a].[BP_VALUE]))

                               |--Nested Loops(Inner Join, WHERE[t_MTSTM_STOP].[ID]=[a].[STOP_ID]))

                                    |--Filter(WHERE[t_MTSTM_STOP].[ACTIVE]=1))

                                    |    |--Bookmark Lookup(BOOKMARK[Bmk1038]), OBJECT[MTS].[dbo].[t_MTSTM_STOP]))

                                    |         |--Index Seek(OBJECT[MTS].[dbo].[t_MTSTM_STOP].[ORDER_ID]), SEEK[t_MTSTM_STOP].[ORDER_ID]=[ord].[ID]) ORDERED FORWARD)

                                    |--Table Spool

                                         |--Clustered Index Scan(OBJECT[MTS].[dbo].[t_MTSTM_STOP_BOLPO].[PK_T_STOP_BOLPO] AS [a]), WHERE(([a].[ACTIVE]=1 AND [a].[BP_TYPE]='P') AND [a].[BP_VALUE]<>NULL) AND [a].[BP_VALUE]<>''))

  • September 2, 2005 at 9:31 am

    #587287

    ha look at all those smillies! I'm not too famliar with execution plans either!! I better add it to the list!

  • September 2, 2005 at 9:32 am

    #587290

    How long does it take to run this monster?

  • September 2, 2005 at 9:32 am

    #587289

    Seriously, you only have 5-6 seeks in there... when you should have 100% seeks. Is there any way this query can work with inner joins?

  • September 2, 2005 at 9:45 am

    #587298

    I understand the need for a one size fits all view, but one of this size with all the left joins is definatelly not going to help performance. You'll find that the performance will rapidly degrade as the size of these tables grows.

    All I can suggest is try to break this up into a few smaller views where inner joins can be used, and make sure you have indexes on any of the join operators.

  • September 2, 2005 at 9:46 am

    #587299

    I think the problem is the way that you wrote your query ...you have so many joins with the same table.

    Probably it will be better to post the DDL and sample data

    t_MTSTM_ORDER ord left outer join t_MTSTM_SHIPMENT shp

    on shp.ORDER_ID = ord.ID

     left outer join

      (select ORDER_ID, DIRECTORY_ID, ID

      from t_MTSTM_STOP

      where ACTIVE = 1 and STOPTYPE_ID = 8583) SHIPSTOPTABLE

     on SHIPSTOPTABLE.ORDER_ID = ord.ID

     left outer join

      (select top 100 percent ORDER_ID, SHIPMENT_ID, DIRECTORY_ID, ID

      from t_MTSTM_STOP

      where ACTIVE = 1 and STOPTYPE_ID IN (8583, 7923)

      order by STOPTYPE_ID) ORIGSTOPTABLE

     on ORIGSTOPTABLE.SHIPMENT_ID = shp.ID

     

    I ve selected your tables that you join as they appear :

    t_MTSTM_STOP

    t_MTSTM_STOP

    t_MTSTM_STOP

    t_MTSTM_STOP

    t_MTSTM_DIRECTORY

    t_MTSTM_DIRECTORY

    t_MTSTM_DIRECTORY

    t_MTSTM_DIRECTORY

    t_MTSTM_DIRECTORY

    t_MTSTM_STATUS

    t_MTSTM_STATUS

    t_MTSTM_STOP

    t_MTSTM_STOP

    t_MTSTM_ORDER

    t_MTSTM_CARGO

    t_MTSTM_CARGO

    t_MTSTM_SHIPMENT_LOG

    you have like 8 tables but many joins

     

    Kindest Regards,

    Vasc

  • September 2, 2005 at 2:53 pm

    #587411

    From the Query Plan, the SQL performs:

    18 Clustered Index Scans (this is bad)

    3 Index Seeks (this is good)

    21 Hash Matchs (this may be bad)

    1 Merge Join (this may be bad)

    Here are some questions and solutions for tuning the physical database for a single table:

    First, establish a baseline by setting statistics io on, run the query and save the io messages which will look like:

    Table '??'. Scan count 2, logical reads 46, physical reads 0, read-ahead reads 0.

    Second, the the Query Plan shows a Clustered Index Scan on table T_MTSTM_STOP using the clustered index PK_T_STOP with a where for columns ACTIVE = 1 AND STOPTYPE_ID = 8583. There is also a join on T_MTSTM_STOP.ORDER_ID = t_MTSTM_ORDER.ID

    Is there an index T_MTSTM_STOP for columns ACTIVE, STOPTYPE_ID and ORDER_ID ?

    If not, determine the columns with the most values by running sql like: "select count(distinct ORDER_ID ) from T_MTSTM_STOP "

    for each of the 3 columns.

    Create an index on T_MTSTM_STOP for the 3 columns with the column having the most values first.

    Re-run the query and compare the io statistics.

    If the index already exists:

    1. run DBCC SHOW_STATISTICS on the index to see the last time the statistics were updated

    2. Do you know when the index was last reorganized ?

    Repeat this process for each table/where/join criteria.

    SQL = Scarcely Qualifies as a Language

  • September 2, 2005 at 3:06 pm

    #587418

    Thanks all for your replies. I belive that most if not all these tables are indexed. I'd have to go thru em all but they should be. I re-indexed the whole DB about 5-6 months ago, but we have tripled the size of our DB since a year ago (now it's a whopping 1.5 GBs huge huh? LOL)  and it probably needs to be re-indexed. Perfomance tuning is beyond me (aside a little experience with DBCC) I am going to have to really dig into it. I'll make this a project so I can really understand the perfomance gains of efficent T-SQL writing and DB maintance. I didn't write this query another programmer did but I'll re-work it. Thanks for the suggestions, I'm all ears if you got more!!!!!!!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply