c# - Read bytea data is slow in PostgreSQL -


i store data in bytea column in postgresql 9.5 database on windows.

the data transmission speed lower expect : 1.5mb per second.

the following code 

        using (var conn = connectionprovider.getopened())         using (var comm = new npgsqlcommand("select mycolumn mytable", conn))         using (var dr = comm.executereader())         {             var clock = stopwatch.startnew();             while (dr.read())             {                 var bytes = (byte[])dr[0];                 debug.writeline($"bytes={bytes.length}, time={clock.elapsed}");                 clock.restart();             }         }

produces following output

bytes=3895534, time=00:00:02.4397086 bytes=4085257, time=00:00:02.7220734 bytes=4333460, time=00:00:02.4462513 bytes=4656500, time=00:00:02.7401579 bytes=5191876, time=00:00:02.7959250 bytes=5159785, time=00:00:02.7693224 bytes=5184718, time=00:00:03.0613514 bytes=720401, time=00:00:00.0227767 bytes=5182772, time=00:00:02.7704914 bytes=538456, time=00:00:00.2996142 bytes=246085, time=00:00:00.0003131 total: 00:00:22.5199268

the strange thing reading last 246kb took less millisecond, , reading 720kb in middle took 22ms.

is reading speed 5mb per 3 sec normal? how can increase reading speed?

details.

my application starts postgresql server on startup , shut downs on exit.

i start server following code 

public static void startserver(string datadirectory, int port) {           invoke("pg_ctl", $"start -w  -d \"{datadirectory}\" -m fast -o \"-b 512mb -p {port} -c temp_buffers=32mb -c work_mem=32mb\""); }

also, change storage type column :

alter table mytable alter column mycolumn set storage external;

i use npgsql 3.0.4.0 , postgresql 9.5 on windows 10

running here npgsql 3.0.8 (should same), postgresql 9.5, windows 10 , npgsql don't results @ all:

bytes=3895534, time=00:00:00.0022591 bytes=4085257, time=00:00:00.0208912 bytes=4333460, time=00:00:00.0228702 bytes=4656500, time=00:00:00.0237144 bytes=5191876, time=00:00:00.0317834 bytes=5159785, time=00:00:00.0268229 bytes=5184718, time=00:00:00.0159028 bytes=720401, time=00:00:00.0130150 bytes=5182772, time=00:00:00.0153306 bytes=538456, time=00:00:00.0021693 bytes=246085, time=00:00:00.0005174

first, server running against, on localhost or on remote machine?

the second thing comes mind, stopping , starting server part of test. slow performance you're seeing may part of warm-up on postgresql's side. try remove , see if results can reproduced after several times of running tests.

otherwise looks environmental problem (client machine, server machine or network). i'd try reproduce problem on different machine or in different setting , go there.


-

Comments

Post a Comment

Popular posts from this blog

scala - 'wrong top statement declaration' when using slick in IntelliJ -

Image
i'm porting project scala 2.9 2.10, therefore have use slick instead of scalaquery. i'm using slick 2.1.0 since supports ms access. according this tutorial , upgrade guide changed robs object class , added val robs : why error message wrong top statement declaration , how rid of it? edit: i'm new scala... according this question seems can't put val outside of methods or classes, right? code above directly in packge. right approach slick then? should move code in class or trait? according this changed val robs = tablequery[robs] to object robs extends tablequery(new robs(_)) {} . no warnings or errors anymore. :)
Read more

c# - DevExpress.Wpf.Grid.InfiniteGridSizeException was unhandled -

Image
when using devexpress, see error: devexpress.wpf.grid.infinitegridsizeexception unhandled message="by default, infinite grid height not allowed since grid rows rendered , hence grid work slowly. fix issue, should place grid container give finite height grid, or should manually specify grid's height or maxheight. note can avoid exception setting gridcontrol.allowinfinitegridsize static property true, in case grid run slowly." the problem dxgrid has infinite height. to fix, set height non-infinite. snoop absolutely invaluable this: if "height" xaml element infinite (i.e. 0 or nan ), can set using one of following: option 1: height="{binding path=actualheight, relativesource={relativesource mode=findancestor, ancestortype=uielement}}" option 2: verticalalignment="stretch" option 3: height="auto" hint: use verticalalignment="stretch" if child of grid <rowdefinition height=
Read more

PySide and Qt Properties: Connecting signals from Python to QML -

i trying run simple pyside appliccation design made in qml. call function in qml python in separated thread. i have following qml view: import qt 4.7 rectangle { visible: true width: 100; height: 100 color: "lightgrey" text { id: datestr objectname: "datestr" x: 10 y: 10 text: "init text" font.pixelsize: 12 function updatedata(text) { datestr.text = qstr(text) console.log("you said: " + text) } } } here application: some imports.... #!/usr/bin/python import sys import time pyside import qtdeclarative, qtcore pyside.qtgui import qapplication pyside.qtdeclarative import qdeclarativeview the qthread worker.... #worker thread class worker(qtcore.qthread): updateprogress = qtcore.signal(int) def __init__(self, child): qtcore.qthread.__init__(self) self.child = child def run(self):
Read more