Sql-server – SQL CTE Expression with Cross Join – Can it be simplified

cteinsertjoin;query-performancesql server

I am looking to simplify the below SQL Query. Currently it's taking 44 mins to run.
The main issue seems to be the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE) statement.
What I'd like to do is filter out the [d_bpc] table data by the max([Version]), which would give me a smaller data set to work with, then CROSS JOIN it with the [d_bpcyearmonth] data.


With BPC_CTE ([id] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
      ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
      ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
      ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], [Versions])
AS
(
SELECT DISTINCT [idbpc] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
      ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
      ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
      ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], a.[Version]
FROM [dbo].[d_bpc] a
LEFT JOIN [d_bpchilo] b ON b.idhl = a.idbpc
WHERE PATINDEX('%TOTAL%', bpc_reporting) = 0 
      AND bpc_reporting<>'' 
      AND PATINDEX('%_NBC%', idbpc) = 0 
      AND b.idhl IS NULL
)
SELECT * 
FROM BPC_CTE AS BPC_CTE1
CROSS JOIN [dbo].[d_bpcyearmonth]
WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE);

I tried running the base query, which took 14 mins without adding the WHERE [Versions] = (SELECT max([Versions]) from BPC_CTE); part.

The base query resulted in about 4,000 rows of data. Adding the cross join resulted in about 470,000 rows of data.

In addition to producing this query as a SELECT DISTINCT statement, I have now added INSERT functionality. Here it is:


With BPC_CTE ([idhl] ,[hl_desc] ,[hl_active_ingredient] ,[hl_ai_content] ,[hl_business_nature]
      ,[hl_brand] ,[hl_product_category] ,[hl_final_fp_code] ,[hl_formulation_type] ,[hl_min_pack]
      ,[hl_npl] ,[hl_conversion_ratio] ,[hl_segment] ,[hl_status] ,[hl_product_type] ,[hl_launch_year]
      ,[hl_scaling] ,[hl_nbc] ,[hl_sku] ,[hl_reporting] ,[hl_last_update] ,[hl_latest], [Version])
AS
(
SELECT DISTINCT [idbpc] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
      ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
      ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
      ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], a.[Version]
FROM [dbo].[d_bpc] a
LEFT JOIN [d_bpchilo2] b ON b.idhl = a.idbpc
WHERE PATINDEX('%TOTAL%', bpc_reporting) = 0 
      AND bpc_reporting<>'' 
      AND PATINDEX('%_NBC%', idbpc) = 0 
      AND b.idhl IS NULL
)
INSERT INTO [dbo].[d_bpchilo2] ([idhl] ,[hl_desc] ,[hl_active_ingredient] ,[hl_ai_content] ,[hl_business_nature]
      ,[hl_brand] ,[hl_product_category] ,[hl_formulation_type] ,[hl_min_pack] ,[hl_conversion_ratio] ,[hl_segment]
      ,[hl_product_type] ,[hl_launch_year] ,[hl_scaling] ,[hl_nbc] ,[hl_sku] ,[hl_reporting] ,[hl_year_month]
      ,[hl_latest] ,[Version] ,[hl_last_update] ,[hl_modified_by])
SELECT [idhl] ,[hl_desc] ,[hl_active_ingredient] ,[hl_ai_content] ,[hl_business_nature] ,[hl_brand] ,[hl_product_category]
      ,[hl_formulation_type] ,[hl_min_pack] ,[hl_conversion_ratio] ,[hl_segment] ,[hl_product_type] ,[hl_launch_year]
      ,[hl_scaling] ,[hl_nbc] ,[hl_sku] ,[hl_reporting] ,[YearMonth] ,[hl_latest] ,[Version] ,[hl_last_update]
      ,'Auto-Update' as [hl_modified_by]
FROM BPC_CTE AS BPC_CTE1
CROSS JOIN [dbo].[d_bpcyearmonth]
WHERE [Version] = (SELECT max([Version]) from BPC_CTE)
;

Some people asked about the table structure. Here's the d_bpc table:
The table structure is as follows:

USE [DWH]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[d_bpc](
    [idbpc] [nchar](25) NOT NULL,
    [bpc_desc] [nvarchar](120) NULL,
    [bpc_active_ingredient] [nvarchar](120) NULL,
    [bpc_ai_content] [nchar](23) NULL,
    [bpc_business_nature] [nchar](10) NULL,
    [bpc_brand] [nvarchar](80) NULL,
    [bpc_product_category] [nchar](25) NULL,
    [bpc_final_fp_code] [nchar](25) NULL,
    [bpc_formulation_type] [nchar](3) NULL,
    [bpc_min_pack] [nchar](38) NULL,
    [bpc_npl] [nchar](20) NULL,
    [bpc_conversion_ratio] [nchar](6) NULL,
    [bpc_segment] [nchar](3) NULL,
    [bpc_status] [nchar](50) NULL,
    [bpc_product_type] [nchar](3) NULL,
    [bpc_launch_year] [nchar](4) NULL,
    [bpc_scaling] [nchar](3) NULL,
    [bpc_nbc] [nchar](19) NULL,
    [bpc_sku] [nchar](19) NULL,
    [bpc_reporting] [nchar](19) NULL,
    [bpc_last_update] [datetime] NULL,
    [bpc_latest] [int] NULL,
    [Version] [datetime] NULL
) ON [PRIMARY]
GO

Many thanks for your help in advance.
BB.

Best Answer

Try this; I added a DENSE_RANK() and another CTE to get just the latest version of the data (BEFORE the cross-join). Although with such a prolific explosion of data (from 4k to 470K rows), I'd be curious if your wait wasn't on tempdb to make enough space.

Still, this should at least prevent querying the BPC_CTE more than once, so might be faster.

;With BPC_CTE ([id] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
          ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
          ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
          ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], [Versions], [RN])
    AS
    (
    SELECT DISTINCT [idbpc] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
          ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
          ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
          ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], a.[Version]
        , RN = ROW_NUMBER() OVER (PARTITION BY a.[Version] ORDER BY a.[Version] DESC)
    FROM [dbo].[d_bpc] a
    LEFT JOIN [d_bpchilo] b ON b.idhl = a.idbpc
    WHERE PATINDEX('%TOTAL%', bpc_reporting) = 0 
          AND bpc_reporting<>'' 
          AND PATINDEX('%_NBC%', idbpc) = 0 
          AND b.idhl IS NULL
    )
    , CTE_LatestBPC AS  
        (
        SELECT [id] ,[bpc_desc] ,[bpc_active_ingredient] ,[bpc_ai_content] ,[bpc_business_nature]
          ,[bpc_brand] ,[bpc_product_category] ,[bpc_final_fp_code] ,[bpc_formulation_type] ,[bpc_min_pack]
          ,[bpc_npl] ,[bpc_conversion_ratio] ,[bpc_segment] ,[bpc_status] ,[bpc_product_type] ,[bpc_launch_year]
          ,[bpc_scaling] ,[bpc_nbc] ,[bpc_sku] ,[bpc_reporting] ,[bpc_last_update] ,[bpc_latest], [Versions]
         FROM BPC_CTE AS B 
         WHERE B.RN = 1
         )
    SELECT * 
    FROM CTE_LatestBPC AS BPC_CTE1
    CROSS JOIN [dbo].[d_bpcyearmonth]

SUGGESTION

Perhaps you should start with a GROUP BY ... HAVING query like this

SELECT table_name,COUNT(1) table_count
FROM information_schema.tables
WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
GROUP BY table_name HAVING COUNT(1) > 1;

This will definitely give all tables whose name appears in multiple databases

Form that as a subquery and join it to gather all databases the table appears in

SELECT
    A.table_name,
    GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
FROM
(
    SELECT table_name,COUNT(1) table_count
    FROM information_schema.tables
    WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_name HAVING COUNT(1) > 1
) A INNER JOIN information_schema.tables B
USING (table_name) GROUP BY A.table_name;

Please notice that I use INNER JOIN rather than LEFT JOIN because it will really form a Cartesian product (2704 X 2704) and then perform comparisons.

I know this works because I tried it out in MySQL 5.5.12 on my Windows7 machine

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>     SELECT
    ->         A.table_name,
    ->         GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
    ->     FROM
    ->     (
    ->         SELECT table_name,COUNT(1) table_count
    ->         FROM information_schema.tables
    ->         WHERE table_schema NOT IN
    ->         ('information_schema','performance_schema','mysql')
    ->         GROUP BY table_name HAVING COUNT(1) > 1
    ->     ) A INNER JOIN information_schema.tables B
    ->     USING (table_name) GROUP BY A.table_name;
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| table_name               | TheTableAppearsInTheseDatabases                                                                                   |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| a                        | junk,test,robottinosino                                                                                           |
| acl                      | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blocks                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog                     | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_category            | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog_entrycat            | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_meta                | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_archive,weisci_jaws_staging2                                     |
| blog_trackback           | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_events          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_meta            | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_questions       | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_tickets         | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_transactions    | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| captcha_complex          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| change_log               | test,junk                                                                                                         |
| chat_staff               | test,junk                                                                                                         |
| comments                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| donations_charities      | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| donations_charities_meta | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| donations_donations      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| foo_reference1           | timpost1,timpost2                                                                                                 |
| foo_reference2           | timpost1,timpost2                                                                                                 |
| foo_reference3           | timpost1,timpost2                                                                                                 |
| groups                   | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| ipvisitor                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| job_post                 | giannosfor,test                                                                                                   |
| layout                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| listeners                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_files       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                                         |
| mediamanager_group       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_photos      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_video       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| menus                    | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| menus_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| mytable                  | ryanzec,user1267617,cabita,dotancohen,johnlocke,neeraj,test,user391986,cool_cs,javier,mathieu                     |
| mytext                   | jakobud,newstuff                                                                                                  |
| occupation_field         | giannosfor,test                                                                                                   |
| policy_agentblock        | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| policy_ipblock           | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| prova                    | veto,vito                                                                                                         |
| registry                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| registry_bk              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| session                  | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages_translation | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| t                        | preeti,rollup_test                                                                                                |
| t1                       | abidibo,test                                                                                                      |
| t2                       | test,abidibo                                                                                                      |
| t3                       | test,abidibo                                                                                                      |
| table1                   | table_test,supercoolville,test                                                                                    |
| table2                   | supercoolville,test,table_test                                                                                    |
| tags                     | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tags_content             | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tbl_banner_position      | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tbl_banner_upload        | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| tbl_global_banner        | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tms_authors              | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_repositories         | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_themes               | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| updates                  | test,junk                                                                                                         |
| url_aliases              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| url_maps                 | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users                    | weisci_jaws_archive,weisci_jaws_staging,giannosfor,veto,weisci_jaws_live,weisci_jaws_staging2,friends,sample,vito |
| users_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users_meta               | weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live                                     |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
65 rows in set (0.91 sec)

mysql>

Give it a Try !!!

Sql-server – Alternative query to this (avoid DISTINCT)

The two scripts in RThomas' answer are both useful. You could also use GROUP BY, which gives a similar advantage to RThomas' methods, but keeping a similar form to your original query.

select country 
from Users inner join
countries on users.CountryID=countries.CountryID
GROUP BY countries.CountryID, countries.country;

The reason why you group by CountryID is that it's the primary key of your countries table, giving the Query Optimizer some better options.

...except that it's not in your scripts.

Put PKs (with Clustered Indexes) on your tables, and a FK relationship between them. Index CountryID in the Users table, and put a Unique Index on the Country field.

Once you've done all that, using DISTINCT how you have will actually give you the ideal execution plan.

Best Answer

Related Solutions

Mysql – Should I use left join to do the job in this scenario

SUGGESTION

Sql-server – Alternative query to this (avoid DISTINCT)

Related Question