Resulting SQL formatting efficiency

Apr 18, 2012 at 4:34 PM
Edited Apr 18, 2012 at 4:37 PM

My queries feature a large number of nested conditions.  I noticed that your library formats the parenthesis in a semi-inefficient way.

For example:

 

SELECT  TOP 10000 [RecordID] AS [Record ID],  [StartTime] AS [Record ID] FROM [records] WHERE  ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( NOT  ([RecordID] LIKE '%Test0%') )  OR  ( NOT  ([RecordID] LIKE '%Test1%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test2%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test3%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test4%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test5%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test6%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test7%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test8%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test9%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test10%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test11%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test12%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test13%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test14%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test15%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test16%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test17%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test18%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test19%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test20%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test21%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test22%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test23%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test24%') ) ) ;

 

This query resulted from me just adding 25 or'd conditions. The number of parenthesis is rather large.  This isn't a problem until I have around a thousand sub-conditions (in various groupings).  My SQL server instance throws a big error about how deep my nested statements are.  If the resulting sql was flatter, for example:

 

WHERE (( (NOT  ([RecordID] LIKE '%Test0%' ) OR  ( NOT  ([RecordID] LIKE '%Test1%' )  OR  ( NOT  ([RecordID] LIKE '%Test2%' ) OR  ( NOT  ([RecordID] LIKE '%Test3%' ) OR  ( NOT  ([RecordID] LIKE '%Test4%' ) OR  ( NOT  ([RecordID] LIKE '%Test5%' ) OR  ( NOT  ([RecordID] LIKE '%Test6%' ) OR  ( NOT  ([RecordID] LIKE '%Test7%' ) OR  ( NOT  ([RecordID] LIKE '%Test8%' ) OR  ( NOT  ([RecordID] LIKE '%Test9%' ) ))

versus:

WHERE  ( ( ( ( ( ( ( ( ( ( NOT  ([RecordID] LIKE '%Test0%') )  OR  ( NOT  ([RecordID] LIKE '%Test1%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test2%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test3%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test4%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test5%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test6%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test7%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test8%') ) )  OR  ( NOT  ([RecordID] LIKE '%Test9%') ) ) ;

 

Many more conditions could appear because they are nested in a shallow manner. The extra parenthesis are limiting the number of sub-conditions that can be introduced.

 

Thanks for the help - I really enjoy this library and hope we can find a work around on this formatting issue.

Coordinator
Apr 28, 2012 at 5:13 PM
Edited Apr 28, 2012 at 5:14 PM

Wow - Ok.

I have no idea what you are working with, but I have added a couple of methods to improve the framework so that it can support this better.

These are - WhereAny, WhereAll, WhereNone, plus the continuation versions AndWhereAny, AndWhereAll, AndWhereNone.

They all take a params array of comparisons with the xxxAny methods corresponding to OR'd collections, xxxAnd corresponds to AND'd and xxxNone corresponds to AND (NOT ()). This should help.

 

            DBComparison country1 = DBComparison.Equal(DBField.Field(Nw.Orders.ShipCountry),DBConst.String("France"));
            DBComparison country2 = DBComparison.Equal(DBField.Field(Nw.Orders.ShipCountry), DBConst.String("Germany"));
            DBComparison country3 = DBComparison.Equal(DBField.Field(Nw.Orders.ShipCountry), DBConst.String("Spain"));
            DBComparison country4 = DBComparison.Equal(DBField.Field(Nw.Orders.ShipCountry), DBConst.String("Italy"));

            //where any

            DBQuery sel = DBQuery.SelectCount()
                                .From(Nw.Orders.Table)
                                .WhereAny(country1, country2, country3, country4);

            int count = (int)db.ExecuteScalar(sel);
May 9, 2012 at 6:25 PM

I really appreciate your response! This code is being used for collecting evidence (data mining) in large collections.

I hand-wrote a parser, I'll drop in yours as its better written than mine.

Thanks!