Sunday, March 25, 2012

Cursor for MSAccess table

I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!
This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>
|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

No comments:

Post a Comment