ODBC Driver generates a too big "windows swap file" and it's too slow - Mailing list pgsql-admin

Hi List,

   First of all, I tried to subcribe the ODBC list but it seems that the
subscription's link is broken ! So here it goes:

   I have a delphi software use ttable components that converts dbf information
to PostgreSQL an Oracle Databases. My problem is that PostgreSQL is too slow,
the oracle db makes this convertion in 3.45 min and the Pg db makes int 29 min.
   The software is the same ( only the database reference is diferent ) , this
sotware uses BDE to access the database with oracle native driver and using
postgreSQL odbc driver version 5. Both databases are in the same machine (
Pentium 4 1.8Ghz, 384MB RAM DDR ) running RH 9 , Oracle 9i and PostgreSQL 7.3.2-
3.
   When I ran this conversion I "snorted" the communication between the server
and the station to see how it does the sql requests , here it goes:

ORACLE :

- select owner, object_name, object_type, created from sys.all_objects where
object_type in ('TABLE', 'VIEW' )  and owner = 'VENDAS' and object_name
= 'FTCOFI00' order by 1 ASC, 2 ASC

- select owner, index_name, uniqueness from sys.all_indexes where table_owner
= 'VENDAS' and table_name = 'FTCOFI00' order by owner ASC, index_name ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCOFI01' order by column_position ASC

-
SELECT "EMP" ,"FIL" ,"CODIGO_FISCAL" ,"CODIGO_FISCAL_ESTORNO" ,"DESCRICAO_FISCAL
" ,"CODIGO_OPERACIONAL" ,"DESCRICAO_USUARIO" ,"COD_NATIPI" ,"COD_NATIBGE" ,"EXTO
_NF1" ,"TEXTO_NF2" ,"NF_NORMALDIF" ,"NF_TRANSFILIAL" ,"COD_FILIAL" ,"COD_LANCTO_
FILIAL" ,"NF_EXPORTACAO_DIRETA" ,"NF_EXPORTACAO_INDIRETA" ,"NF_SIMPREMESSA" ,"NF
_DEVOLUCAO" ,"NF_ENTRADA" ,"NF_REPOSICAO" ,"NF_OUTRASERIE" ,"NF_CONSIGNACAO" ,"N
F_PRODGRATIS" ,"NF_FATURANTECIP" ,"NF_DIFBASEICM" ,"NF_DIF_VALORICM" ,"NF_DIFBAS
EIPI" ,"NF_DIFVALORIPI" ,"NF_DIFPRECO" ,"BLOQ_CREDITO" ,"LIBERA_CREDITO" ,"VER_P
ARAM_VENDAS" ,"ENTRA_COBRANCA" ,"BASECALC_VLRBRUTO" ,"DESCNF_REFICM" ,"ALIQICM_I
GUALEST" ,"COD_TRIBICM" ,"COD_TRIBIPI" ,"ATUAL_ESTOQUE" ,"ATUAL_FABRICACAO" ,"AT
UAL_FATURA" ,"ATUAL_OUTENTR" ,"ATUAL_OUTSAIDA" ,"ATUAL_TRANFIL" ,"ATUAL_SEMIACAB
" ,"ATUAL_CARTPED" ,"ATUAL_ENTRSAID" ,"REV_CUSTMEDIO" ,"DIGITAR_FISICO" ,"DIGITA
R_FINANCEIRO" ,"USAR_CUSTO_CMU_INFORMAR" ,"GRUPO_FATURAMENTO" ,"TIPO_NF" ,"RESUM
O_FISCAL_CODIGO" ,
"ATUAL_DISTRIB" ,"IMPR_OBS_NF_REG_ES" ,"DIFE_RECEITA" ,"COD_LANCTO" ,"SITUACAO"
 FROM "FTCOFI00" ORDER BY  "EMP" ASC , "FIL" ASC , "CODIGO_FISCAL" ASC

- select owner, object_name, object_type, created from sys.all_objects where
object_type in ('TABLE', 'VIEW')  and owner = 'VENDAS' and object_name
= 'FTCLCR00' order by 1 ASC, 2 ASC

- select owner, index_name, uniqueness from sys.all_indexes where table_owner
= 'VENDAS' and table_name = 'FTCLCR00' order by owner ASC, index_name ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR01' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR02' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR03' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR04' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR05' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR06' order by column_position ASC

- select column_name from sys.all_ind_columns where index_owner = 'VENDAS' and
index_name = 'FTCLCR07' order by column_position ASC

-
SELECT "EMP" ,"FIL" ,"TIPO_CADASTRO" ,"CODIGO" ,"RAZAO_SOCIAL" ,"NOME_FANTASIA"
,"EMP_ENDERECO" ,"EMP_NRO" ,"EMP_COMPLEMENTO" ,"EMP_BAIRRO" ,"EMP_CIDADE" ,"EMP_
ESTADO" ,"EMP_CEP" ,"EMP_PAIS" ,"EMP_EAN" ,"COB_ENDERECO" ,"COB_NRO" ,"COB_COMPL
EMENTO" ,"COB_BAIRRO" ,"COB_CIDADE" ,"COB_ESTADO" ,"COB_CEP" ,"COB_PAIS" ,"COB_E
AN" ,"ENT_ENDERECO" ,"ENT_NRO" ,"ENT_COMPLEMENTO" ,"ENT_BAIRRO" ,"ENT_CIDADE" ,"
ENT_ESTADO",
"ENT_CEP" ,"ENT_PAIS" ,"ENT_EAN" ,"LOJA_EAN" ,"TELEFONE" ,"CELULAR" ,"FAX" ,"EMA
IL" ,"SITE" ,"CONTATO_NOME" ,"CONTATO_TELEFONE" ,"CONTATO_EMAIL" ,"CONTATO_DDMM_
ANIV" ,"SITUACAO_CADASTRO" ,"OBSERVACOES" ,"DATA_CADASTRO" ,"DATA_ALTERACAO" ,"T
IPO_CONTRIBUINTE" ,"CODIGO_CONTRIBUINTE" ,"TIPO_INSCRICAO" ,"CODIGO_INSCRICAO","
CODIGO_REDE" ,"CODIGO_TIPO_CLIENTE" ,"CODIGO_GRUPO_CLIENTE" ,"CODIGO_SUFRAMA" ,"
DATA_VALIDADE_SUFRAMA" ,"LIMITE_CREDITO" ,
"MARCA" ,"CLASSE" ,"BANDEIRA_CLIENTE" ,"CODIGO_TIPO_CREDOR" ,"NOME_REPRESENTANTE
" ,"TIPO_CONDICAO_PGTO" ,"PRAZO_PGTO_01" ,"PRAZO_PGTO_02" ,"PRAZO_PGTO_03" ,"COD
IGO_MOEDA_COMPRA" ,"FATOR_QUALIDADE" ,"DESPESA_FINANCEIRA" ,"CODIGO_DARF" ,"CODI
GO_NATUREZA_RENDIMENTO" ,"CONTA_CORRENTE_BANCO" ,"CONTA_CORRENTE_AGENCIA" ,"CONT
A_CORRENTE_NUMERO" ,"FORNECEDOR_SULPLASTIC" ,"SUFRAMA_TRIB_ICM" ,"SUFRAMA_TRIB_I
PI" ,"CONTA_CORRENTE_AGENC_DC" ,
"CONTA_CORRENTE_NUM_DC" ,"CONTA_COR_FORMA_PAGTO" ,"FORMA_CREDITO" ,"SENHA" ,"LIM
ITE_CREDITO_PUIG" ,"COD_REPRES" ,"COD_CLIENTE_TEP" ,"EDI_MERCADOR" ,"TIPO_NF" ,"
BONIFIC_BALCAO"  FROM "FTCLCR00" ORDER BY  "EMP" ASC , "FIL"
ASC , "TIPO_CADASTRO" ASC , "CODIGO" ASC


PostgreSQL:

- select relname, nspname, relkind from pg_catalog.pg_class,
pg_catalog.pg_namespace where relkind in ('r', 'v') and nspname like 'vendas'
and relname like 'ftcofi00' and relname !~ '^pg_|^dd_' and pg_namespace.oid =
relnamespace order by nspname, relname

- select u.nspname, c.relname, a.attname, a.atttypid, t.typname,a.attnum,
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind from
pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and
c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname
like 'ftcofi00' and u.nspname like 'vendas' order by u.nspname, c.relname,
attnum

- select u.nspname, c.relname, a.attname, a.atttypid, t.typname,a.attnum,
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind from
pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and
c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname
= 'ftcofi00'and u.nspname = 'vendas' order by u.nspname, c.relname, attnum

- select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
c.relhasrules, n.nspname from pg_catalog.pg_index i, pg_catalog.pg_class c,
pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n where
d.relname = 'ftcofi00' and n.nspname = 'vendas' and n.oid = d.relnamespace and
d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by
i.indisprimary desc, i.indisunique, n.nspname, c.relname

-
SELECT "emp" ,"fil" ,"codigo_fiscal" ,"codigo_fiscal_estorno" ,"descricao_fiscal
" ,"codigo_operacional" ,"descricao_usuario" ,"cod_natipi" ,"cod_natibge" ,"text
o_nf1" ,"texto_nf2" ,"nf_normaldif" ,"nf_transfilial" ,"cod_filial" ,"cod_lancto
_filial" ,"nf_exportacao_direta" ,"nf_exportacao_indireta" ,"nf_simpremessa" ,"n
f_devolucao" ,"nf_entrada" ,"nf_reposicao" ,"nf_outraserie" ,"nf_consignacao" ,"
nf_prodgratis" ,"nf_faturantecip" ,"nf_difbaseicm" ,"nf_dif_valoricm" ,"nf_difba
seipi" ,"nf_difvaloripi" ,"nf_difpreco" ,"bloq_credito" ,"libera_credito" ,"ver_
param_vendas" ,"entra_cobranca" ,"basecalc_vlrbruto" ,"descnf_reficm" ,"aliqicm_
igualest" ,"cod_tribicm" ,"cod_tribipi" ,"atual_estoque" ,"atual_fabricacao" ,"a
tual_fatura" ,"atual_outentr" ,"atual_outsaida" ,"atual_tranfil" ,"atual_semiaca
b" ,"atual_cartped" ,"atual_entrsaid" ,"rev_custmedio" ,"digitar_fisico" ,"digit
ar_financeiro","usar_custo_cmu_informar" ,"grupo_faturamento" ,"tipo_nf" ,"resum
o_fiscal_codigo" ,"atual_distrib" ,
"impr_obs_nf_reg_es" ,"difer_receita" ,"cod_lancto" ,"situacao"
FROM "vendas"."ftcofi00" ORDER BY  "emp" ASC , "fil" ASC , "codigo_fiscal" ASC

- select relname, nspname, relkind from pg_catalog.pg_class,
pg_catalog.pg_namespace where relkind in ('r', 'v') and nspname like 'vendas'
and relname like 'ftclcr00' and relname !~ '^pg_|^dd_' and pg_namespace.oid =
relnamespace order by nspname, relname

- select u.nspname, c.relname, a.attname, a.atttypid, t.typname,a.attnum,
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind from
pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and
c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname
like 'ftclcr00' and u.nspname like 'vendas' order by u.nspname, c.relname,
attnum

- select u.nspname, c.relname, a.attname, a.atttypid, t.typname,a.attnum,
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind from
pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and
c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname
= 'ftclcr00'and u.nspname = 'vendas' order by u.nspname, c.relname, attnum

- select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
c.relhasrules, n.nspname from pg_catalog.pg_index i, pg_catalog.pg_class c,
pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n where
d.relname = 'ftclcr00' and n.nspname = 'vendas' and n.oid = d.relnamespace and
d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by
i.indisprimary desc, i.indisunique, n.nspname, c.relname

-
SELECT "emp" ,"fil" ,"tipo_cadastro" ,"codigo" ,"razao_social","nome_fantasia" ,
"emp_endereco" ,"emp_nro" ,"emp_complemento" ,"emp_bairro" ,"emp_cidade" ,"emp_e
stado" ,"emp_cep" ,"emp_pais","emp_ean" ,"cob_endereco" ,"cob_nro" ,"cob_complem
ento" ,"cob_bairro" ,"cob_cidade" ,"cob_estado" ,"cob_cep" ,"cob_pais" ,"cob_ean
" ,"ent_endereco" ,"ent_nro" ,"ent_complemento" ,"ent_bairro" ,"ent_cidade" ,"en
t_estado" ,"ent_cep" ,"ent_pais" ,"ent_ean" ,"loja_ean" ,"telefone" ,"celular" ,
"fax" ,"email" ,"site" ,"contato_nome" ,"contato_telefone" ,"contato_email" ,"co
ntato_ddmm_aniv" ,"situacao_cadastro" ,"observacoes" ,"data_cadastro" ,"data_alt
eracao" ,"tipo_contribuinte" ,"codigo_contribuinte" ,"tipo_inscricao" ,"codigo_i
nscricao" ,"codigo_rede" ,"codigo_tipo_cliente" ,"codigo_grupo_cliente" ,"codigo
_suframa" ,"data_validade_suframa" ,"limite_credito" ,"marca" ,"classe" ,"bandei
ra_cliente" ,"codigo_tipo_credor" ,"nome_representante" ,"tipo_condicao_pgto" ,"
prazo_pgto_01" ,"prazo_pgto_02" ,"prazo_pgto_03" ,
"codigo_moeda_compra" ,"fator_qualidade" ,"despesa_financeira" ,"codigo_darf" ,"
codigo_natureza_rendimento" ,"conta_corrente_banco" ,"conta_corrente_agencia" ,"
conta_corrente_numero" ,"fornecedor_sulplastic" ,"suframa_trib_icm" ,"suframa_tr
ib_ipi" ,"conta_corrente_agenc_dc" ,"conta_corrente_num_dc" ,"conta_cor_forma_pa
gto" ,"forma_credito" ,"senha" ,"limite_credito_puig" ,"cod_repres" ,"cod_client
e_tep" ,"edi_mercador" ,"tipo_nf" ,"bonific_balcao"  FROM "vendas"."ftclcr00"
ORDER BY  "emp" ASC , "fil" ASC , "tipo_cadastro" ASC , "codigo" ASC

     So , this snort generated a 3MB file for Oracle and it didn't request a
bigger windows swap file but PostgreSQL generated a 153 MB file and I needed a
700 MB windows swap file ( this is unacceptable !!!! ).
     I tried changing the ttables components to a SQL Query but Pg did it in
49min an Oracle in 29min ( it looks like a index problem but there's no way to
force an index in Pqsql ).
     I don't know whate else to do , and I really want to use PgSQL instead of
Oracle but to do this I must PgSQL working in a compatibile time !
     Any suggestions ?

Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122