Board index » delphi » Data Transformation Services (DTS) versus ADO query

Data Transformation Services (DTS) versus ADO query

Good day to all.

I have an ADO query that does a select from a Progress database (using
Merant's ODBC driver).
It returns approx. 600 rows, then waits about a minute before the next 600
rows are available.

I have also built a DTS script, that uses the identical query, and the same
ODBC driver.
It returns the full 63,000 rows of data (and inserts them to a SQL2000
table) in 55 seconds.

Anyone have any ideas as to what's causing the discrepancy between the two
methods?

Thanks in advance for your assistance.

 

Re:Data Transformation Services (DTS) versus ADO query


On the loading front DTS uses OLE DB's IRowsetFastload interface for high
performance loading, so this gives it a perf bump as this is not available
under ADO.

On the fetch side, DTS is optimised for handling millions of records and it
is all done through OLE DB and not ADO, so there are many optimisations to
cater for that scenario that you are probbaly not doing.

Why not just use DTS? It can be build and executed from inside Delphi.

-Euan

Quote
"Gary Hinkel" <Gary_Hin...@KalTire.com> wrote in message

news:3dff7cfe$1@newsgroups.borland.com...
Quote
> Good day to all.

> I have an ADO query that does a select from a Progress database (using
> Merant's ODBC driver).
> It returns approx. 600 rows, then waits about a minute before the next 600
> rows are available.

> I have also built a DTS script, that uses the identical query, and the
same
> ODBC driver.
> It returns the full 63,000 rows of data (and inserts them to a SQL2000
> table) in 55 seconds.

> Anyone have any ideas as to what's causing the discrepancy between the two
> methods?

> Thanks in advance for your assistance.

Re:Data Transformation Services (DTS) versus ADO query


Thanks Euan, didn't know that was possible.  Do you know of anywhere I could
get more info on that?

Thanks again.

Gary.

Quote
"Euan Garden" <euan_gar...@spicedham.hotmail.com> wrote in message

news:3dff81c5$2@newsgroups.borland.com...
Quote
> On the loading front DTS uses OLE DB's IRowsetFastload interface for high
> performance loading, so this gives it a perf bump as this is not available
> under ADO.

> On the fetch side, DTS is optimised for handling millions of records and
it
> is all done through OLE DB and not ADO, so there are many optimisations to
> cater for that scenario that you are probbaly not doing.

> Why not just use DTS? It can be build and executed from inside Delphi.

> -Euan

> "Gary Hinkel" <Gary_Hin...@KalTire.com> wrote in message
> news:3dff7cfe$1@newsgroups.borland.com...
> > Good day to all.

> > I have an ADO query that does a select from a Progress database (using
> > Merant's ODBC driver).
> > It returns approx. 600 rows, then waits about a minute before the next
600
> > rows are available.

> > I have also built a DTS script, that uses the identical query, and the
> same
> > ODBC driver.
> > It returns the full 63,000 rows of data (and inserts them to a SQL2000
> > table) in 55 seconds.

> > Anyone have any ideas as to what's causing the discrepancy between the
two
> > methods?

> > Thanks in advance for your assistance.

Re:Data Transformation Services (DTS) versus ADO query


Great, thanks.  Where do I find ".attachments" ?

Quote
"Euan Garden" <euan_gar...@spicedham.hotmail.com> wrote in message

news:3e019b9d$1@newsgroups.borland.com...
Quote
> Dug out an old sample, posted in .attachements

> -Euan

> "Gary Hinkel" <Gary_Hin...@KalTire.com> wrote in message
> news:3dff83b6$1@newsgroups.borland.com...
> > Thanks Euan, didn't know that was possible.  Do you know of anywhere I
> could
> > get more info on that?

> > Thanks again.

> > Gary.

> > "Euan Garden" <euan_gar...@spicedham.hotmail.com> wrote in message
> > news:3dff81c5$2@newsgroups.borland.com...
> > > On the loading front DTS uses OLE DB's IRowsetFastload interface for
> high
> > > performance loading, so this gives it a perf bump as this is not
> available
> > > under ADO.

> > > On the fetch side, DTS is optimised for handling millions of records
and
> > it
> > > is all done through OLE DB and not ADO, so there are many
optimisations
> to
> > > cater for that scenario that you are probbaly not doing.

> > > Why not just use DTS? It can be build and executed from inside Delphi.

> > > -Euan

> > > "Gary Hinkel" <Gary_Hin...@KalTire.com> wrote in message
> > > news:3dff7cfe$1@newsgroups.borland.com...
> > > > Good day to all.

> > > > I have an ADO query that does a select from a Progress database
(using
> > > > Merant's ODBC driver).
> > > > It returns approx. 600 rows, then waits about a minute before the
next
> > 600
> > > > rows are available.

> > > > I have also built a DTS script, that uses the identical query, and
the
> > > same
> > > > ODBC driver.
> > > > It returns the full 63,000 rows of data (and inserts them to a
SQL2000
> > > > table) in 55 seconds.

> > > > Anyone have any ideas as to what's causing the discrepancy between
the
> > two
> > > > methods?

> > > > Thanks in advance for your assistance.

Re:Data Transformation Services (DTS) versus ADO query


In article <3e021ff...@newsgroups.borland.com>, Gary_Hin...@KalTire.com
says...
Quote
> Great, thanks.  Where do I find ".attachments" ?

        borland.public.attachments

        -Craig

--
Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Data Transformation Services (DTS) versus ADO query


-Gary

"Craig Stuntz [TeamB]" <cstu...@nospamplease.com (but use vertexsoftware to

Quote
email)> wrote in message

news:MPG.186beeaac670def998a45e@newsgroups.borland.com...
Quote
> In article <3e021ff...@newsgroups.borland.com>, Gary_Hin...@KalTire.com
> says...
> > Great, thanks.  Where do I find ".attachments" ?

> borland.public.attachments

> -Craig

> --
> Craig Stuntz [TeamB] Vertex Systems Corp. Columbus, OH
> Delphi/InterBase Weblog : http://delphi.weblogs.com
> InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
> InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Other Threads