SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How could I simplify this Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitche027
Starting Member

Philippines
3 Posts

Posted - 03/26/2014 :  09:56:46  Show Profile  Reply with Quote
I have a table that goes like this.. It's quite simple but then It output huge amount of data (20,000) and has a lot of joins... can you kindly help me break it up.. also I would somehow like to improve it's performance..




    Declare @QUERY varchar(max)
      
      -- First I have to create 1 temporary table and 1 actual table
      IF object_id('tempdb..#TempTable') IS NOT NULL
      BEGIN
      DROP TABLE #TempTable  
      END
      
      CREATE TABLE #TempTable
      (
      DataA nvarchar(50),DataB nvarchar(50),DataC nvarchar(50),DataD nvarchar(50),DataE nvarchar(50),
      DataF nvarchar(50),DataG nvarchar(50),DataH nvarchar(50),DataI nvarchar(50),DataJ nvarchar(50),
      DataK nvarchar(50),DataL nvarchar(50),DataM nvarchar(50),DataN nvarchar(50),DataO nvarchar(50)
      )
      
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTableCount]') AND type in (N'U'))                                                  
      BEGIN  
      DROP TABLE RealTable
      END
      
      CREATE TABLE RealTable
      (
      DataA nvarchar(50),DataB nvarchar(50),DataC nvarchar(50),DataD nvarchar(50),DataE nvarchar(50),
      DataF nvarchar(50),DataG nvarchar(50),DataH nvarchar(50),DataI nvarchar(50),DataJ nvarchar(50),
      DataK nvarchar(50),DataL nvarchar(50),DataM nvarchar(50),DataN nvarchar(50),DataO nvarchar(50)
      )
      
      -- now I declare the content of my QUERY variable
      
      SET @Query =
    	'SELECT
    	DataA = a.content,
    	DataB = b.content,
    	DataC = c.content,
    	DataD = d.content,
    	DataE = e.content,
    	DataF = f.content,
    	DataG = g.content
    	
    	INTO #DummyTable -- put the content into a temporary table
    	FROM TableA a 
    	INNER JOIN TableB b ON a.ID=b.ID
    	INNER JOIN TableC c ON b.ID=c.ID
    	INNER JOIN TableD d ON c.ID=d.ID
    	LEFT JOIN TableE e ON d.ID=e.ID
    	LEFT JOIN TableF f ON e.ID=f.ID
    	LEFT JOIN TableG g ON f.ID=g.ID
    	
    	WHERE'
    	
    -- 3 CONDITIONS TO ADD INTO WHERE CLAUSE	
    IF (1st Condition)
    BEGIN
    SET @QUERY += 'ADD CONDITION ON THE QUERY WHERE CLAUSE '
    END
    
    	
    IF (2st Condition)
    BEGIN
    SET @QUERY += 'ADD CONDITION ON THE QUERY WHERE CLAUSE '
    END
    
    	
    IF (3st Condition)
    BEGIN
    SET @QUERY += 'ADD CONDITION ON THE QUERY WHERE CLAUSE '
    END
    
    -- Add data to temptable table
    SET @QUERY += '
    INSERT INTO #TempTable
      (
      DataA,DataB,DataC,DataD,DataE,
      DataF,DataG,DataH,DataI,DataJ,
      DataK,DataL,DataM,DataN,DataO
      )
    SELECT DISTINCT
    	DataA = X.content,
    	DataB = X.content,
    	DataC = X.content,
    	DataD = X.content,
    	DataE = X.content,
    	DataF = X.content,
    	DataG = X.content,	
    	DataH = h.content,
    	DataI = i.content,
    	DataJ = j.content,
    	DataK = k.content,
    	DataL = l.content,
    	DataM = m.content,
    	DataN = n.content,
    	DataO = o.content
    	
    	
    	FROM #DummyTable X
    	INNER JOIN TableH h ON X.ID=h.ID
    	INNER JOIN TableI i ON h.ID=i.ID
    	INNER JOIN TableJ j ON i.ID=j.ID
    	LEFT JOIN TableK k ON j.ID=k.ID
    	LEFT JOIN TableL l ON k.ID=l.ID
    	LEFT JOIN TableM m ON l.ID=m.ID
    	LEFT JOIN TableN n ON m.ID=n.ID
    	LEFT JOIN TableO o ON n.ID=o.ID
    	
    	-- now putting content an the realTable
    	
    	INSERT INTO RealTable
    	(
    	DataA,DataB,DataC,DataD,DataE,
        DataF,DataG,DataH,DataI,DataJ,
        DataK,DataL,DataM,DataN,DataO
    	)
    	VALUES -- this text are use as headers for report since this data is copied to excel i guess...
    	(
    	''DataA'',''DataB'',DataC'',''DataD,DataE'',
        ''DataF'',''DataG'',''DataH'',''DataI'',''DataJ'',
        ''DataK'',''DataL'',''DataM'',''DataN'',''DataO''
    	)
    	
    	INSERT INTO RealTable
    	(
    	DataA,DataB,DataC,DataD,DataE,
        DataF,DataG,DataH,DataI,DataJ,
        DataK,DataL,DataM,DataN,DataO
    	)
    	VALUES
    	(
    	DataA,DataB,DataC,DataD,DataE,
        DataF,DataG,DataH,DataI,DataJ,
        DataK,DataL,DataM,DataN,DataO
    	)
    	FROM #TempTable

kingroon
Starting Member

United Kingdom
29 Posts

Posted - 04/08/2014 :  11:24:30  Show Profile  Reply with Quote
Something like this..?

BEGIN TRAN

    SET NOCOUNT ON

    -- Set Up
    CREATE TABLE RealTable ( DataA nvarchar(50), DataB nvarchar(50), DataC nvarchar(50), DataD nvarchar(50), DataE nvarchar(50),
                            DataF nvarchar(50), DataG nvarchar(50), DataH nvarchar(50), DataI nvarchar(50), DataJ nvarchar(50),
                            DataK nvarchar(50), DataL nvarchar(50), DataM nvarchar(50), DataN nvarchar(50), DataO nvarchar(50) )
    CREATE TABLE TableH ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableI ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableJ ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableK ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableL ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableM ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableN ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableO ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )
    CREATE TABLE TableX ( ID INT IDENTITY(1,1), Content NVARCHAR(50) )

    -- Test Data
    INSERT INTO TableH ( Content ) SELECT 'Test Data H'
    INSERT INTO TableI ( Content ) SELECT 'Test Data I'
    INSERT INTO TableJ ( Content ) SELECT 'Test Data J'
    INSERT INTO TableH ( Content ) SELECT 'Test Data HH'
    INSERT INTO TableI ( Content ) SELECT 'Test Data II'
    INSERT INTO TableJ ( Content ) SELECT 'Test Data JJ'
    INSERT INTO TableH ( Content ) SELECT 'Test Data HHH'
    INSERT INTO TableI ( Content ) SELECT 'Test Data III'
    INSERT INTO TableJ ( Content ) SELECT 'Test Data JJJ'
    INSERT INTO TableK ( Content ) SELECT 'Test Data K'
    INSERT INTO TableL ( Content ) SELECT 'Test Data L'
    INSERT INTO TableM ( Content ) SELECT 'Test Data M'
    INSERT INTO TableN ( Content ) SELECT 'Test Data N'
    INSERT INTO TableO ( Content ) SELECT 'Test Data O'
    INSERT INTO TableX ( Content ) SELECT 'Test Data X'
    INSERT INTO TableX ( Content ) SELECT 'Test Data Y'
    INSERT INTO TableX ( Content ) SELECT 'Test Data Z'

    -- Conditionals
    DECLARE @OneCondition INT; SET @OneCondition = 0;
    DECLARE @TwoCondition INT; SET @TwoCondition = 0;
    DECLARE @ThreeCondition INT; SET @ThreeCondition = 0;

    -- Main SELECT statement
    INSERT INTO RealTable ( DataA, DataB, DataC, DataD, DataE, DataF, DataG,
                            DataH, DataI, DataJ, DataK, DataL, DataM, DataN, DataO )
    SELECT X.Content, X.Content, X.Content, X.Content, X.Content, X.Content, X.Content,
        H.Content, I.Content, J.Content, K.Content, L.Content, M.Content, N.Content, O.Content
    FROM TableX X
        JOIN TableH H ON X.ID = H.ID
        JOIN TableI I ON H.ID = I.ID
        JOIN TableJ J ON I.ID = J.ID
        LEFT JOIN TableK K ON J.ID = K.ID
        LEFT JOIN TableL L ON K.ID = L.ID
        LEFT JOIN TableM M ON L.ID = M.ID
        LEFT JOIN TableN N ON M.ID = N.ID
        LEFT JOIN TableO O ON N.ID = O.ID
    WHERE H.Content = CASE
                        WHEN @OneCondition = 1 THEN 'Test Data H'
                        ELSE H.Content
                        END
        AND I.Content = CASE
                        WHEN @TwoCondition = 2 THEN 'Test Data II'
                        ELSE I.Content
                        END
        AND J.Content = CASE
                        WHEN @ThreeCondition = 3 THEN 'Test Data JJJ'
                        ELSE J.Content
                        END

    SELECT * FROM RealTable

ROLLBACK


With the above statement and the conditionals as they are, all records returned..

Results:



Hasta Luego..
KingRoon
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000