pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Azlin Ghazali <azlin_ghazali@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: PostgreSQL is slow...HELP
Date: Wed, 3 Sep 2003 06:08:57 -0700 (PDT)
Message-ID: <20030903130857.5986.qmail@web40401.mail.yahoo.com> (raw)

Hi,

I'm working on a project to make an application run on MySQL and PostgreSQL.
I find that PostgreSQL runs up to 10 times slower than MySQL.  For small records
it is not much problems.  But as the records grew (up to 12,000 records) the
difference is quite significant.  We are talking about 15s (MySQL) vs 111s 
(PostgreSQL).  Someone suggest that my way of implementing PostgreSQL is not
efficient and someone out there might be able to help.

FYI, I running the application on ASP, XP Professional and Pentium 4 machine.

Below is the exact statement I used:
        
        strSQL = "CREATE TABLE temp1 SELECT accposd.item,items.name,Sum(accposd.qty)
as Quantity " & _
             ",accposd.loose,AVG(accposd.price) as price, Sum(accposd.amount) as
sum_amount  " & _
             ",Sum(accposd.cost) as sum_cost FROM  " & _
             "accposd left join items on accposd.item = items.fcc " & _
             "where accposd.date between '" &  varStartDate & "' AND '" &
varStopDate & "'"  & _
             " GROUP by accposd.item,items.name,accposd.loose ORDER by items.name"

Below is the information about the fields:

CREATE TABLE accposd (
  fcc double precision default NULL,
  date date default NULL,
  recvbch double precision default NULL,
  type int default NULL,
  item double precision default NULL,
  qty double precision default NULL,
  price double precision default NULL,
  amount double precision default NULL,
  discamt double precision default NULL,
  cost double precision default NULL,
  loose varchar(10) default NULL,
  discflg varchar(10) default NULL,
  hour smallint default NULL,
  min smallint default NULL,
  sec smallint default NULL,
  who varchar(50) default NULL,
  promoter varchar(50) default NULL,
  userID double precision default '0',
  batchno double precision default '0'
);


CREATE TABLE items (
  fcc serial,
  code varchar(20) default NULL,
  name varchar(40) default NULL,
  description varchar(255) default NULL,
  barcode varchar(15) default NULL,
  brand varchar(30) default NULL,
  sub_category double precision default NULL,
  schedule char(1) default NULL,
  price double precision default NULL,
  lprice double precision default NULL,
  avgcost double precision default NULL,
  gname varchar(40) default NULL,
  strength varchar(10) default NULL,
  packsize double precision default NULL,
  whspack varchar(15) default NULL,
  packing varchar(10) default NULL,
  lowstock double precision default NULL,
  lstockls double precision default NULL,
  orderqty double precision default NULL,
  creation date default NULL,
  shelfno varchar(8) default NULL,
  status char(1) default NULL,
  q_cust double precision default NULL,
  ql_cust double precision default NULL,
  qoh double precision default NULL,
  qohl double precision default NULL,
  poison double precision default NULL,
  candisc double precision default NULL,
  maxdisc double precision default NULL,
  chkdate date default NULL,
  chkby varchar(5) default NULL,
  isstock double precision default NULL,
  wprice double precision default '0',
  wlprice double precision default '0',
  PRIMARY KEY  (fcc)
);


I appreciate your advice.  Thank you.

Regards,
AZLIN.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com



view thread (5+ messages)  latest in thread

Message-ID: <20030903130857.5986.qmail@web40401.mail.yahoo.com>
Permalink:  ../20030903130857.5986.qmail@web40401.mail.yahoo.com/
Also on:    postgresql.org/message-id/20030903130857.5986.qmail@web40401.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: azlin_ghazali@yahoo.com
  Subject: Re: PostgreSQL is slow...HELP
  In-Reply-To: <20030903130857.5986.qmail@web40401.mail.yahoo.com>
  References: <20030903130857.5986.qmail@web40401.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox