Long time for Validating connection

More
9 years 6 months ago #9855 by kaoru
Hi,

I have a problem in migrating data from MS SQL 2008 into Greenplum (PosgresSql)..
I have a condition where the pg_catalog size is bloated until more than 50GB.
It has 36 schema, and have 2000+ tables inside each schema.

I'm using SSIS (Import Export Data Wizard) to move the data.
it takes a long time for loading the table list for mapping.
If I edit the parameters via Visual Studio it will be "validating data flow task" and caused Visual Studio hang.
If I managed to get Visual Studio to run the package, the validating step will take a long time also (aprox 1 hour till the package actually start copying the data).

If I capture the script that fired to Greenplum at the validation steps, it returns this query :
select *
from
(select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION",
A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE",
TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL", A.attlen as "FIELD_LENGTH",
A.atttypmod as "FIELD_MOD", D.adsrc as "COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_tables T on (C.relname=T.tablename)
inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f'

union all

select T.schemaname as "TABLE_SCHEMA", T.viewname as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION", A.atthasdef as "COLUMN_HASDEFAULT",
A.atttypid as "DATA_TYPE", TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL",
A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as "COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_views T on (C.relname=T.viewname)
inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f') s

where "TABLE_SCHEMA"='ads' and "TABLE_NAME"='deposit_master'
order by "TABLE_SCHEMA", "TABLE_NAME", "ORDINAL_POSITION";

Because our system tables bloated.. this query is taking a long time to run.
Do you have any fixes or suggestion on this issue ?
Pls, i need the info asap :rolleyes:

thank u

Please Log in or Create an account to join the conversation.

More
9 years 6 months ago #9856 by Moderator
There are different ways to speed-up queries like that. One way is to use "Query Optimizer" (currently undocumented). You will need to create special views for fast metadata retrieval, and configure optimizer hints in the PGNP_OPTIMIZER table. Please communicate to support for more details.

Please Log in or Create an account to join the conversation.

More
9 years 6 months ago #9858 by kaoru
OK will do that, thank u for the response ;)

Please Log in or Create an account to join the conversation.

Time to create page: 0.063 seconds
Powered by Kunena Forum