Postgres - possible speed improvement on conversion for Time datatype

Hi Giorgio,

in general, PostgreSQL answers time values in a more complicated format than HH:MM:SS. E.g., if I evaluate the following code snippet, I get something like '17:10:19.952667+01', i.e. with sub-second precision and a timezone offset:

session := aPostgreSQLEXDIConnection getSession.

answer := session prepare: 'Select current_time'; execute; answer.

answer next first inspect

Which version of PostgreSQL do you use? AFAIK, version 7.1 and earlier answered time values in integer seconds only. Also, if you store values with integer second precision, you may not see fractions of a second in your query results.

A PostgreSQL client connection can be configured such that it uses one of four formats for output: ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German, where ISO 8601 is the default (see http://www.postgresql.org/docs/9.2/static/datatype-datetime.html and http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-DATESTYLE). The style can be set with the SET datestyle command. The default translation blocks for times and timestamps in PostgreSQLEXDISession don't take this into account and may have to be replaced if you use a particular datestyle. The replacement block can be specialized for the respective datestyle, which may of course be faster than the default translation.

HTH

Joachim

Von: giorgio ferraris <[hidden email]>
Datum: Sonntag, 25. November 2012 16:26
An: Visualworks Mailing List <[hidden email]>, Bruce Badger <[hidden email]>
Betreff: [vwnc] Postgres - possible speed improvement on conversion for Time datatype

Hi, Bruce 

I'm currently doing test on some huge tables in postgres, with many Time columns, and I found that a lot of time was spent on 

PostgresSQLEXDISession>>valueFrom: aString ofType: aSymbol ,

and, for my case, on the fromTimeBlock method.

This method is pretty simple:

fromTimeBlock

"^a Block 

I return the block that can pull a Time from a stream, where that stream contains a string in the 

form defined for the PostgreSQL type 'time'. The block returns a Time."

^[:aSession :aStream | Time readFrom: aStream]

Now, I looked at what was happenings, and the readFrom: in time is quite general, complex and goes quite deep before getting to the real place where translation is done.

I haven't looked deeply to understand the various intricacy, but on  my case, I noted that I always had to translate a string of this form: HH:MM:SS.

So I did a simple test:

try := 100000.

Time millisecondsToRun: [

try timesRepeat: [

time := Time readFrom: (ReadStream on: '11:35:47')]]  

Time millisecondsToRun: [

try timesRepeat: [

        stream := ReadStream on: '11:35:47'.

time := Time 

   hours:  (stream upTo: $: ) asInteger

   minutes: (stream upTo: $: ) asInteger

   seconds: (stream upToEnd ) asInteger

]]

with 4400 mseconds on first case and around 200 mseconds on second case (20 times less).

I have VW 7.9 with the postgres parcel that comes with the distribution cd, and I'm on a Macbook pro with osx.

If the same results on your pc, my question is: Is it because of my postgres  setting  that I get always the simple HH:MM:SS string for time column, or is this the standard Postgres behavior?

And, if yes, cannot be the second implementation being used? Just a change on timeBlock as here:

fromTimeBlock

"^a Block 

I return the block that can pull a Time from a stream, where that stream contains a string in the 

form defined for the PostgreSQL type 'time'. The block returns a Time."

" ^[:aSession :aStream | Time readFrom: aStream]"

^

[:aSession :aStream | 

Time 

hours:  (aStream upTo: $: ) asInteger

minutes: (aStream upTo: $: ) asInteger

seconds: (aStream upToEnd ) asInteger]

ciao and thanks 

Giorgio

_______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc