Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.05 seconds. Powered By: Snitz Forums 2000