Long time for Validating connection

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Long time for Validating connection

Postby kaoru » Tue Dec 18, 2012 5:14 am

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 :roll:

thank u
kaoru
 
Posts: 8
Joined: Tue Dec 18, 2012 5:01 am

Re: Long time for Validating connection

Postby Moderator » Tue Dec 18, 2012 8:45 am

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.
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Long time for Validating connection

Postby kaoru » Tue Dec 18, 2012 11:07 am

OK will do that, thank u for the response ;)
kaoru
 
Posts: 8
Joined: Tue Dec 18, 2012 5:01 am


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 0 guests

cron