Implemented UseQueryText (as InlineParameters)

[lvaleriu]

Implemented UseQueryText (as InlineParameters) in order to generate a sql query instead of using command parameters. Useful for optimisations in select queries in oracle when having partitions over one column

[ili]

Can you please provide an example? Am I right in idea that all tests now should be done twice: UseQueryText = true & UseQueryText = false?

[lvaleriu]

Yes, of course.

For instance, the following query

            var begin = DateTime.Today;
            var end = DateTime.Now;

            var nb = db.GetTable<DbDataEvalFr>().Count(id =>

id.DateMedia >= begin && id.DateMedia <= end);

should give as db.LastQuery: 1) when using UseQueryText = true

SELECT Count(*) as cnt FROM PITAFR01.DATA_EVALIANT id WHERE id.date_media >= TO_TIMESTAMP('2015-03-06 00:00:00.0000000', 'YYYY-MM-DD HH24:MI:SS.FF7') AND id.date_media <= TO_TIMESTAMP('2015-03-06 09:36:55.8294336', 'YYYY-MM-DD HH24:MI:SS.FF7')

2)* when using UseQueryText = false*

SELECT Count(*) as cnt FROM PITAFR01.DATA_EVALIANT id WHERE id.date_media >= :begin1 AND id.date_media <= :end1

In Oracle, when having a partition over a date column in a table for fast querying you need to have plain text queries instead of command parameters. Otherwise Oracle doesnt build its optimized execution plan.

Best, Valeriu

On Fri, Mar 6, 2015 at 6:40 AM, Ilya Chudin notifications@github.com wrote:

Can you please provide an example? Am I right in idea that all tests now should be done twice: UseQueryText = true & UseQueryText = false?

Reply to this email directly or view it on GitHub https://github.com/igor-tkachev/bltoolkit/pull/350#issuecomment-77509737 .

[ili]

Has some troubles for now. Should be used carefully. I'm going to fix it ASAP

[ili]

Yah... while working on this issue I do think that it would be better to have little bit different solution, like:

var begin = DateTime.Today;
var end = DateTime.Now;

var nb = db.GetTable<DbDataEvalFr>().Count(id => 
      id.DateMedia >= begin/**/.AsConstant() /**/ 
&& id.DateMedia <= end  /**/.AsConstant()/**/);

As for me it looks like more flexible & manageable

How do you think?

[lvaleriu]

In Linq2Db, Igor and I have choosed a global property on the DbConnection class (which is the equivalent of the DbManager class from BlToolkIt). What are the issues remaining? I could try to help you eventually. Setting a AsConstant everytime you want an Inline Parameter makes it tedious. There could be 10, 15 parameters per query. And many queries in a program. And when you want to switch back, how can you do it without modifying every line of code? This could be interesting to have this property AsConstant, but I think we still need to have in the same time the UseQueryText property.

Regards, Valeriu

On Sun, Mar 22, 2015 at 7:33 AM, Ilya Chudin notifications@github.com wrote:

Yah... while working on this issue I do think that it would be better to have little bit different solution, like:

var begin = DateTime.Today;var end = DateTime.Now; var nb = db.GetTable().Count(id => id.DateMedia >= begin//.AsConstant() // && id.DateMedia <= end //.AsConstant()//);

As for me it looks like more flexible & manageable

How do you think?

Reply to this email directly or view it on GitHub https://github.com/igor-tkachev/bltoolkit/pull/350#issuecomment-84533494 .

[ili]

I should look what is done in linq2db :) what is properties name? :)

[lvaleriu]

Source/IDataContext.cs https://github.com/linq2db/linq2db/blob/57323974c0a1d75b769b4117d95292efde36dae1/Source/IDataContext.cs

bool InlineParameters { get; set; }

Source/Data/DataConnection.cs https://github.com/linq2db/linq2db/blob/70e40184730201789499e01c9b49afce92ccec1d/Source/Data/DataConnection.cs

public bool InlineParameters { get; set; }

On Sun, Mar 22, 2015 at 6:08 PM, Ilya Chudin notifications@github.com wrote:

I should look what is done in linq2db :) what is properties name? :)

Reply to this email directly or view it on GitHub https://github.com/igor-tkachev/bltoolkit/pull/350#issuecomment-84655878 .

[ili]

for now open issues are (i'v not tested with all dbs yet): 1) Access - doesn't support ms... so I do have some mind break:

        [Test]
        public void DateTimeArray1()
        {
            ForEachProvider(db =>
                AreEqual(
                    from t in    Types2 where new DateTime?[] { new DateTime(2001, 1, 11, 1, 11, 21, 100) }.Contains(t.DateTimeValue) select t,
                    from t in db.Types2 where new DateTime?[] { new DateTime(2001, 1, 11, 1, 11, 21, 100) }.Contains(t.DateTimeValue) select t));
        }

why does it works with parameners & does not with inline

2) no support for WCF

3) sql injection - we should process inline strings to avoid sql injection

[ili]

4) i don't know how to inline binary values

[lvaleriu]

Hello. Sorry for the delay. 1) I know that DataProvider class has some virtual methods for building DateTime values that can be override for the different providers 2) No pb for me, since this whole history of inline parameters is a optimisation for Oracle Execution Plan 3) Yes, thats true. 4) There's no need to inline binary values. So we need to let them as sql parameters

On Sun, Mar 22, 2015 at 6:58 PM, Ilya Chudin notifications@github.com wrote:

4) i don't know how to inline binary values

Reply to this email directly or view it on GitHub https://github.com/igor-tkachev/bltoolkit/pull/350#issuecomment-84667231 .

[ili]

1) yes, they are. but I found no way to write access datetime literal with milliseconds.. so datetime with ms is not inlined for access 2) may be for somebody else :) but anyway it is done 3) it was done already :) 4) yes, they are not inlined now

for now in dev brunch is fully functional code tested with all dataproviders in all modes I need to make some cleanup and I want to rename UseQueryText as InlineParameters, and i'll merge it in master... today, I suppose

[lvaleriu]

These are good news! Thanks for you help!

On Wed, Mar 25, 2015 at 2:11 PM, Ilya Chudin notifications@github.com wrote:

1) yes, they are. but I found no way to write access datetime literal with milliseconds.. so datetime with ms is not inlined for access 2) may be for somebody else :) but anyway it is done 3) it was done already :) 4) yes, they are not inlined now

for now in dev brunch is fully functional code tested with all dataproviders in all modes I need to make some cleanup and I want to rename UseQueryText as InlineParameters, and i'll merge it in master... today, I suppose

Reply to this email directly or view it on GitHub https://github.com/igor-tkachev/bltoolkit/pull/350#issuecomment-86017311 .

[ili]

it is done