Re: Access very slow extracting data from Sql

[dr223]

Re: Access very slow extracting data from Sql

Hallo,

I have an access database which connects to a Sql server 2005 via an ODBC to extract data from tables. The problem is one of the table's is built with 100,000,000 records. Therefore, just to extract 7 fields for one record it takes 20 minutes, which is really so inconvenient.
Please could anyone advise me what to do so that to stimulate a quick extract.

Things to consider;

- the space available on the drive where sql server is planted is around 1.8 GB.. Is space an issue?
- the database in sql server has only two tables and the recovery mode has been sent to simple.. N/B - the database is static and was created ONLY to store the two tables, the data on these tables will not be updated nor deleted.
Any ideas to improve the performnace?

Thank you very much

 

[DCrake]

Re: Access very slow extracting data from Sql

How are you processing the request what is the syntax of the filtering.

One thing to consider it is far quicker to Select * from Table as opposed to Select Field1, Field3, Field 20, Etc From Table as the processor has to not only filter teh records but filter the fields as well.

As a matter of interest what kind of tables contains 1bn records?

David

 

[dr223]

Re: Access very slow extracting data from Sql

Thanks for the reply.
Select * selects every field.... However, I need only select certain columns.

Secondly, I think it is to do with the table indexing however I dont know where to start....

The tables contains records which have been there since the establishment of the organisation including historical data.

Any help!!

 

[DCrake]

Re: Access very slow extracting data from Sql

Nobody said you have to use every column you retrieve from sql you can simply use the ones you want once you have brought the entire record accross.

 

[namliam]

Re: Access very slow extracting data from Sql

Yes indexes....

Are you using the Primary key to retrieve your information?
Are you using the Index? Is there an Index?

 

[dr223]

Re: Access very slow extracting data from Sql

Hallo Namliam,

The tables have no primary key, and am not using any index (need some informtion here)..

Please direct me where to look to fix the probem.

Indexable in the Tabe Designer is set to YES


Thanks

 

[namliam]

Re: Access very slow extracting data from Sql

I would create an index on the column you use to lookup a record.... That should speed up things considerably....

 

[dr223]

Re: Access very slow extracting data from Sql

How do you do that?

Thanks

 

[namliam]

Re: Access very slow extracting data from Sql

For help on creating indexes in SQL server please refer here...

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Or contact your DBA... He should be able to do so without much problems.