SQL Server Performance

T-SQL experts please help correcting it !

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by raagi2000, Feb 26, 2008.

  1. raagi2000 New Member

    Here is the original scriptselect count(*) FROM (select * from aecprda where AECPRDA.sales_cat_cd in ('02','10') and (create_dt > '2008-02-17 18:10:22.000' or price_chg_dt > '2008-02-17 18:10:22.000') ) AS AECPRDA_1 left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode and z.Vendorname = N'Alliance' LEFT OUTER JOIN AECMCAT aecmcat_c3 ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd I want to add a condition [(substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or(AECPRDA_1.product_id = z.vendorcode))] instead of [AECPRDA_1.product_id = z.vendorcode] TO THE ABOVE STATEMENT.It is a secondary match condition 1. If aecprda_1.upc_1 doesn’t find a match on zfmt.upc then2. Match on aecprda_1. product_id = zfmt. Vendorcode3. I also want to add a condition if there is a product match on both 1 & 2 , then take only the first one and don’t take the second one. Something looks like below statement but needs one more condition mentioned at item 3 to be added. select count(*) FROM (select * from aecprda where AECPRDA.sales_cat_cd in ('02','10') and (create_dt > '2007-10-22 00:00:00.000' or price_chg_dt > '2007-10-22 00:00:00.000') ) AS AECPRDA_1 left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on (substring(AECPRDA_1.upc_1,1,11) = substring(z.upc,1,11) or(AECPRDA_1.product_id = z.vendorcode)) and z.Vendorname = N'Alliance' LEFT OUTER JOIN AECMCAT aecmcat_c3 ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd --- I ran this query executing takes hour eventhough appropriate indices were added. Any help is greatly appreciated.
  2. FrankKalis Moderator

    Consider reformatting your post. It is not very readable.
  3. Madhivanan Moderator

    Also how many rows are there in each table?
  4. Adriaan New Member

    Is this a query that was "upgraded" from MS Access? SQL Server doesn't need the brackets for joins.
    Also note that with criteria in the ON clause of an outer join, the query returns rows that match the given criteria for the outside table, but also rows that do not have a match on the outside table (returning a NULL for the column). If those are the results that you need, that's fine - if not, then your COUNT(*) result will be wrong.
    If you only need the rows that have a match on the outside table, then change the outer join to an inner join and move the criteria to the WHERE clause. This should help performance.
    I also spot a SELECT * in the first derived table: replace the asterisk with a specific column from the table the columns that you're referring to in the joins and/or the WHERE clause.
    Funny join here: AECPRDA_1.product_id = z.vendorcode.
    Finally, the SUBSTRING criteria mean that any index will get ignored, because SQL Server has to apply the function to each row from the underlying tables before it can start filtering.

Share This Page