Fastest means to extracting data?

[WSF]

Access97

I am trying to create a table in a local MDB by extracting a range of
records from an BE SQL table with 15 million records.

I have linked this table with: (watch for this message wrap)

DoCmd.TransferDatabase acLink, "ODBC", _
"ODBC;DRIVER=SQL
Server;SERVER=X555;UID=Readonly;PWD=onlyread;APP=Microsoft® Access;
WSID=X123;DATABASE=StockTracking;Network=DBMSSOCN;TABLE=dbo." & SrcTable, _
acTable, "dbo." & SrcTable, DestTable, False, False

[SrcTable] and [DestTable] are set as variables as I use this function
for other data.

I extract data from the SQL source table into the local MDB using:

DoCmd.RunSQL "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"


This works okay but seems very slow.
It extracts approx 120,000 records of 12 fields, being the latest group
of records in the source table for the given date range.

This takes several minutes and causes issues with other contemporaneous
users of the database - users with their own FE connecting to the shared
BE (of which the sql table is a part) . They suffer a considerable
slowdown in their read/write etc

I hope I have made it clear.
Is there a better (quicker and less inconvenient to other users) way of
doing this?

TIA

WSF

 

[Ron Weiner]

Very first thing would be to make sure that you have an index on
DestTable.InDate on the Sql side. Otherwise Access will have to examine
each of those 15 million rows (dragging them across your network cable) to
determine which ones meet the criteria.

If things are still to slow then the next thing I would try would be to
offload as much of the processing to Sql as possible. You could start
testing by creating Views, Sproc's, and UDF's to see how these might speed
things along. Lastly you might want to consider using Sql DTS to directly
export the table to an Access container. I suspect DTS will provide the
best performance as all of the processing will be done on the server.

I doubt that there is ONLY ONE FASTEST way to export 120K rows from Sql to
Access. You will have to start testing to see what works best in your
environment.

Ron W
www.WorksRite.com

 

[Guest]

In addition to Ron Weiner's suggestion regarding indexing, I would also
suggest you replace the RunSQL with Execute. It is usually about 3 time
faster.
DoCmd.RunSQL "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"
Change to
CurrentDB.Execute "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"

 

[WSF]

Thanks Ron and Klatuu,
I have tried the .Execute and yes, it is much faster and looks like the
fix. I will look at the other options too.
Your help appreciated.

WSF

In addition to Ron Weiner's suggestion regarding indexing, I would also
suggest you replace the RunSQL with Execute. It is usually about 3 time
faster.
DoCmd.RunSQL "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"
Change to
CurrentDB.Execute "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"
:

Access97

I am trying to create a table in a local MDB by extracting a range of
records from an BE SQL table with 15 million records.

I have linked this table with: (watch for this message wrap)

DoCmd.TransferDatabase acLink, "ODBC", _
"ODBC;DRIVER=SQL
Server;SERVER=X555;UID=Readonly;PWD=onlyread;APP=Microsoft® Access;
WSID=X123;DATABASE=StockTracking;Network=DBMSSOCN;TABLE=dbo." & SrcTable, _
acTable, "dbo." & SrcTable, DestTable, False, False

[SrcTable] and [DestTable] are set as variables as I use this function
for other data.

I extract data from the SQL source table into the local MDB using:

DoCmd.RunSQL "SELECT DestTable.* INTO tblStockData FROM DestTable WHERE
(((DestTable.InDATE)>=#07/01/2005#));"


This works okay but seems very slow.
It extracts approx 120,000 records of 12 fields, being the latest group
of records in the source table for the given date range.

This takes several minutes and causes issues with other contemporaneous
users of the database - users with their own FE connecting to the shared
BE (of which the sql table is a part) . They suffer a considerable
slowdown in their read/write etc

I hope I have made it clear.
Is there a better (quicker and less inconvenient to other users) way of
doing this?

TIA

WSF