Professional Web Applications Themes

Advanced SQL optimization[VERY LONG, BUT PROBABLY VERY INTERESTING]... - Microsoft SQL / MS SQL Server

I've got this SQL that scans one table(t1) for records containing (somewhere inside of a field) the fields of another table(t2) and then joining the records of a third table(t3, parent to t1) but only if the decimal column of t3 equals the SUM of the records joined in t2... It is a pretty advanced SQL and it takes a ***LONG TIME*** and it probably can't execute within the speed of light anyway (due to the advanced nature of the SQL) but what I would like to do is to try to optimize it, but I am stuck. I have ...

  1. #1

    Default Advanced SQL optimization[VERY LONG, BUT PROBABLY VERY INTERESTING]...

    I've got this SQL that scans one table(t1) for records containing
    (somewhere inside of a field) the fields of another table(t2) and
    then joining the records of a third table(t3, parent to t1) but
    only if the decimal column of t3 equals the SUM of the records
    joined in t2...

    It is a pretty advanced SQL and it takes a ***LONG TIME*** and it
    probably can't execute within the speed of light anyway (due to the
    advanced nature of the SQL) but what I would like to do is to
    try to optimize it, but I am stuck.
    I have maneged to get it down to 55. sek, if I could tune it just a
    bit
    more I would appreciate it a lot.
    The query is a CROSS JOIN in nature (1 item from side A against many
    items on side B)
    So to all Microsoft SQL gurus out there, can you please help me and
    see if I have
    overlooked something obvious?!?

    Here is the (parts that are needed) of the database schema:

    CREATE TABLE [dbo].[BA] (
    [CCL_ID] [int] NOT NULL ,
    [BA_ID] [int] NOT NULL ,
    [BB_ID] [int] NOT NULL ,
    [BANo] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [CUId] [int] NOT NULL ,
    [GenBB] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [FLLK] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OOCId] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [AAUUTTId] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[BU] (
    [TMessage_ID] [int] NOT NULL ,
    [BA_ID] [int] NOT NULL ,
    [BU_ID] [uniqueidentifier] NOT NULL ,
    [Amount] [decimal](18, 2) NOT NULL ,
    [BUDate] [datetime] NULL ,
    [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [FRRTRS] [int] NOT NULL ,
    [State] [int] NULL ,
    [OrderValue] [int] NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[L] (
    [CCL_ID] [int] NOT NULL ,
    [L_ID] [int] NOT NULL ,
    [CCLNK_ID] [int] NOT NULL ,
    [GenL] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[LItem] (
    [Amount] [decimal](18, 2) NOT NULL ,
    [Amount2] [decimal](18, 2) NULL ,
    [MYDATE] [datetime] NULL ,
    [NNVVONo] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL ,
    [KKDD] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CUId] [int] NOT NULL ,
    [RECCGUID] [int] NULL ,
    [Adjusted] [int] NULL ,
    [CreatedGUID] [int] NULL ,
    [ImpFile_ID] [int] NOT NULL ,
    [CSS_ID] [uniqueidentifier] NOT NULL ,
    [LItem_ID] [uniqueidentifier] NOT NULL ,
    [L_ID] [int] NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[P] (
    [BU_ID] [uniqueidentifier] NULL ,
    [P_ID] [uniqueidentifier] NOT NULL ,
    [Amount] [decimal](18, 2) NOT NULL ,
    [OrderValue] [int] NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[SubHKLL] (
    [P_ID] [uniqueidentifier] NOT NULL ,
    [SubP_ID] [uniqueidentifier] NOT NULL ,
    [HKLLType_ID] [int] NOT NULL ,
    [Content] [char] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [SubHKLL_ID] [uniqueidentifier] NOT NULL ,
    [FtxOrder] [int] NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[SubP] (
    [P_ID] [uniqueidentifier] NOT NULL ,
    [SubP_ID] [uniqueidentifier] NOT NULL ,
    [Amount] [decimal](18, 2) NOT NULL ,
    [KKDD] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NNVVONo] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL ,
    [CSSNo] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [RECCGUID] [int] NULL ,
    [RECCId] [int] NULL ,
    [RECCTime] [datetime] NULL ,
    [RECCSplit] [int] NULL ,
    [OrderValue] [int] NOT NULL
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[BA]HKLL ADD
    CONSTRAINT [PK_BA] PRIMARY KEY CLUSTERED
    (
    [BA_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[BU] ADD
    CONSTRAINT [PK_BU] PRIMARY KEY CLUSTERED
    (
    [BU_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[L] ADD
    CONSTRAINT [PK_L] PRIMARY KEY CLUSTERED
    (
    [L_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[LItem] ADD
    CONSTRAINT [PK_LItem] PRIMARY KEY CLUSTERED
    (
    [LItem_ID],
    [L_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[P] ADD
    CONSTRAINT [PK_P] PRIMARY KEY CLUSTERED
    (
    [P_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[SubHKLL] ADD
    CONSTRAINT [PK_SubHKLL] PRIMARY KEY CLUSTERED
    (
    [SubHKLL_ID]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[SubP] ADD
    CONSTRAINT [PK_SubP] PRIMARY KEY CLUSTERED
    (
    [SubP_ID]
    ) ON [PRIMARY]

    CREATE INDEX [IDX_L_CCL_ID] ON [dbo].[L]([CCL_ID]) ON [PRIMARY]

    CREATE INDEX [LItem9] ON [dbo].[LItem]([L_ID], [Amount],
    [NNVVONo], [RECCGUID], [LItem_ID]) ON [PRIMARY]

    ALTER TABLE [dbo].[BU] ADD
    CONSTRAINT [FK_BU_BA] FOREIGN KEY
    (
    [BA_ID]
    ) REFERENCES [dbo].[BA] (
    [BA_ID]
    ) ON DELETE CASCADE

    ALTER TABLE [dbo].[P] ADD
    CONSTRAINT [FK_P_BU] FOREIGN KEY
    (
    [BU_ID]
    ) REFERENCES [dbo].[BU] (
    [BU_ID]
    ) ON DELETE CASCADE ON UPDATE CASCADE

    ALTER TABLE [dbo].[SubHKLL] ADD
    CONSTRAINT [FK_SubHKLL_SubP] FOREIGN KEY
    (
    [SubP_ID]
    ) REFERENCES [dbo].[SubP] (
    [SubP_ID]
    ) ON DELETE CASCADE ON UPDATE CASCADE

    ALTER TABLE [dbo].[SubP] ADD
    CONSTRAINT [FK_SubP_P] FOREIGN KEY
    (
    [P_ID]
    ) REFERENCES [dbo].[P] (
    [P_ID]
    ) ON DELETE CASCADE ON UPDATE CASCADE

    ALTER TABLE [dbo].[LItem] ADD
    CONSTRAINT [FK_LItem_L] FOREIGN KEY
    (
    [L_ID]
    ) REFERENCES [dbo].[L] (
    [L_ID]
    ) ON DELETE CASCADE ON UPDATE CASCADE





    And here is the SQL...



    SELECT s.P_ID, s.SubP_ID, l.L_ID, l.LItem_ID
    FROM SubP s INNER JOIN
    P p ON p.P_ID = s.P_ID INNER JOIN
    BU b ON b.BU_ID = p.BU_ID INNER JOIN
    BA ba ON ba.BA_ID = b.BA_ID CROSS JOIN
    LItem l INNER JOIN
    L led ON l.L_ID = led.L_ID
    WHERE

    -- Within current BA's
    (ba.CCL_ID=15) AND (led.CCL_ID=15)

    -- Type neither OOC nor AAUUTT
    AND b.Type NOT IN('AUT','OOC')

    -- Non RECCD items
    AND (s.RECCGUID IS NULL) AND (l.RECCGUID IS NULL)

    -- SUM(Amount) of LItems == Amount of SubP
    AND (s.Amount IN
    (SELECT SUM(l2.Amount)
    FROM L led2 INNER JOIN
    LItem l2 ON l2.L_ID=led2.L_ID
    WHERE led2.CCL_ID=15 AND l2.RECCGUID IS NULL AND
    l2.NNVVONo <> ''
    AND l2.NNVVONo IN
    (SELECT SUBSTRING(Content,
    CHARINDEX(RTRIM(l2.NNVVONo),
    Content), LEN(l2.NNVVONo))
    FROM SubHKLL
    WHERE P_ID = s.P_ID AND SubP_ID=s.SubP_ID
    AND Content LIKE '%'+RTRIM(l2.NNVVONo)+'%')))

    -- And L NNVVO no in SubP's PAY HKLL
    AND l.NNVVONo != '' AND (l.NNVVONo IN
    (SELECT SUBSTRING(Content, CHARINDEX(RTRIM(l.NNVVONo),
    Content), LEN(l.NNVVONo))
    FROM SubHKLL
    WHERE P_ID = s.P_ID AND SubP_ID=s.SubP_ID AND
    Content LIKE '%'+RTRIM(l.NNVVONo)+'%'))

    ORDER BY s.P_ID, s.SubP_ID


    And here is some sample data

    e.g.
    (some stuff in above tables that makes joins work)


    SIDE A:

    BA.CCL_ID = 1

    BU.Type = 'GGK'

    P.Amount = 500

    SubP.Amount = 500

    SubHKLL
    1. sdfooijsdf THOMAS aosdih
    2. dfoih JOHN dfjsoiap
    3. ANN asdfpij



    SIDE B:

    L.CCL_ID = 1

    LItem
    1. Amount=50, NNVVONo = 'THOMAS'
    2. Amount=150, NNVVONo = 'JOHN'
    3. Amount=300, NNVVONo = 'ANN'


    ....plus all the forreign keys should match...
    This is going to give a "hit" in the query, but the problem is that it
    takes
    too much time
    to produce an output...



    I am not sure if anybody can help me at all, but I would appreciate it
    a lot
    if somebody somehow could at least tune
    it a little bit...

    PS!
    I would rather NOT use indexed views if possible since they come with
    a bit
    overhead and restricts the database
    too much...



    Sincerely

    Thomas Hansen

    PS!
    Due to regulations in the company I work for I had to rename all
    tables and
    columns to something "unrecognizable"...
    --
    "FOOT-AND-MOUTH BELIEVED TO BE FIRST VIRUS
    UNABLE TO SPREAD THROUGH MICROSOFT OUTLOOK"
    Thomas Hansen Guest

  2. #2

    Default Re: Advanced SQL optimization[VERY LONG, BUT PROBABLY VERY INTERESTING]...

    If you post sample data in the form of INSERT statements, I am sure that I
    will be able to help you. It is just too much work trying to create the
    sample data myself.

    Also, It is very important to know how many lines there are in all the
    different tables.

    /SG

    "Thomas Hansen" <thomas.hansenNOSPAMORILLSUEYOURadramatch.co m> wrote in
    message news:ollqgvs23g4ftorgsraivs4ner5m34bknu4ax.com...
    > I've got this SQL that scans one table(t1) for records containing
    > (somewhere inside of a field) the fields of another table(t2) and
    > then joining the records of a third table(t3, parent to t1) but
    > only if the decimal column of t3 equals the SUM of the records
    > joined in t2...
    >

    Stefan Gustafsson Guest

  3. #3

    Default Re: Advanced SQL optimization[VERY LONG, BUT PROBABLY VERY INTERESTING]...

    On Thu, 10 Jul 2003 15:41:51 +0200, there came a drop of sanity from
    Thomas Hansen <com>
    containing:
    [snip]
    I realize that this is a terrible problem to dive into, but I am
    completely stuck still (been on vacation) and I still haven't found
    any adequate solution (even though I have read one very good SQL book
    this month) so...
    ....if someone could either give me some hints on how to do the
    optimizing or at least tell me that it's not possible I would
    appreciate it a lot...

    Thomas Hansen
    --
    "FOOT-AND-MOUTH BELIEVED TO BE FIRST VIRUS
    UNABLE TO SPREAD THROUGH MICROSOFT OUTLOOK"
    Thomas Guest

  4. #4

    Default Re: Advanced SQL optimization[VERY LONG, BUT PROBABLY VERYINTERESTING]...

    Indeed, I don't think it will be possible to get this query lightning
    fast, because of the free format text search.

    It looks like a very strange query to me, because the s.Amount check is
    not related to the l.L_ID and l.LItem_ID that you are selecting in the
    main query.

    But here some tips:
    1. Try the Index Tuning Wizard and/or experiment with adding indexes.
    Make sure you index all foreign keys (SQL-Server will not automatically
    do that)

    2. One of the fragments reads
    AND l2.NNVVONo IN
    (SELECT SUBSTRING(Content,
    CHARINDEX(RTRIM(l2.NNVVONo),Content),
    LEN(l2.NNVVONo))
    FROM SubHKLL
    WHERE P_ID = s.P_ID AND SubP_ID=s.SubP_ID
    AND Content LIKE '%'+RTRIM(l2.NNVVONo)+'%')))

    You could replace the SUBSTRING(Content...) with l2.NNVVONo. This means
    that you can change the IN into an EXIST technique. For example:
    AND EXISTS
    (SELECT *
    FROM SubHKLL
    WHERE P_ID = s.P_ID AND SubP_ID=s.SubP_ID
    AND Content LIKE '%'+RTRIM(l2.NNVVONo)+'%')))

    Of course, the same applies to the second place where you do this.

    3. You need to clean up the quey. For example, if you are joining the
    SubHKLL and SubP table, then joining on the primary key/foreign is
    enough (SubP_ID = SubP_ID). You can remove the redundant expression P_ID
    = s.P_ID.

    4. One of the fragments reads
    AND (s.Amount IN
    (SELECT SUM(l2.Amount)
    ...
    -- NO GROUP BY

    This means that the SUM(l2.Amount) will return only one value. So you
    can change the IN set operation into an = comparison. For example:
    AND s.Amount =
    (SELECT SUM(l2.Amount)
    ....

    Hope this helps,
    Gert-Jan


    Thomas Hansen wrote: 
    Gert-Jan Guest

  5. #5

    Default Re: Advanced SQL optimization[VERY LONG, BUT PROBABLY VERY INTERESTING]...

    On Mon, 04 Aug 2003 19:29:43 +0200, there came a drop of sanity from
    Gert-Jan Strik <nl> containing:
     
    I am not sure what you mean but what the query is trying to achieve
    (and does as far as I know) is to have a number of records matched
    from table A to one record in table B.
    One of the clauses is to have the sum of Amount from table A equal the
    Amount in table B.
    The other clause is that NNVVONo column in table A should be found in
    a "child" table of table B...
    So it's basically a cross join based upong two clauses...
     
    [snip]
    It surely did help!
    :)
    With 11000 records in one side against 800 records in other side and
    163 matches I got it from 42.880 - 43.833 seconds (it ran with those
    times in the profiler every second time) down to 41.090 seconds!
    And that is surely better then what I have accomplished alone trying
    to figure out what's "wrong"...
    The weird thing is that what gave the most performance gain was the
    "cleaning up part" or removing the redundant and clauses which wasn't
    part of the forreign key...
    Obviously MSSQL is not capable of optimizing away clauses which aren't
    part of FK-PK relationship (I am not even sure if it should
    neither...)

    Then when I ran through the indexes on the whole database (I thought a
    bit about it and realized that you should probably allmost never have
    a forreign key without an index on the column in the child table) I
    got it down to *** 34.630 seconds *** !!!!!!
    :)


    Thank you for taking time to dive into this extremely long posting, I
    am eternally in debt to you!!



    Sincerely

    Thomas Hansen
    --
    "FOOT-AND-MOUTH BELIEVED TO BE FIRST VIRUS
    UNABLE TO SPREAD THROUGH MICROSOFT OUTLOOK"
    Thomas Guest

Similar Threads

  1. pdf optimization
    By palaksha@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: July 18th, 08:01 AM
  2. 3D optimization
    By DaveRaith in forum Macromedia Director 3D
    Replies: 6
    Last Post: March 20th, 02:06 PM
  3. FTP Performance Optimization
    By JennyPoo in forum Macromedia Contribute Connection Administrtion
    Replies: 0
    Last Post: November 10th, 04:02 PM
  4. Index optimization ?
    By Bo Lorentsen in forum PostgreSQL / PGSQL
    Replies: 32
    Last Post: January 19th, 06:50 AM
  5. Looking for Optimization
    By Thomas Neumann in forum PHP Development
    Replies: 1
    Last Post: October 15th, 06:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139