openquery to oracle: how to set arraysize?
-
sexta-feira, 8 de junho de 2012 05:37
In sqlplus, we can use
set arraysize 5000
That command will ask oracle to pack 5000 rows into a data package and send it to client. But for openquery, how can I do that?
I have 11M rows send from oracle to sql server, and from oracle running statistics, I can see it send less than 100 rows one round trip over network, and if the network latency is long, then the query is very slow.
any way help to set the array size?
Todas as Respostas
-
sexta-feira, 8 de junho de 2012 21:33
You could use openrowset better since it has more rich powers of :
- Remote collating data entity at Oracle end
- Moreover , you could composite of lots of table in one query to minimize BW consumption and overload on SQL Server
Thereby you might work the below query :
SELECT * FROM openrowset('MSDASQL', 'DRIVER={Microsoft ODBC for Oracle}; UID=USERID; PWD=PASSWORD; SERVER=SERVERNAME;',
'select * from TABLENAME')
For patching your script , I would like to give preference for using bulk merge commands of 2008 with patching them to packets of size of each 5000 records as requested , thereby you may have a look at my blog series for this regards:
Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities
-
segunda-feira, 11 de junho de 2012 00:05
I assume that "set arraysize" is used to send a subset of rows to client till it's processed then send next ones over next network packets.
This is beneficial for applications like reports ; JDBC driver for SQL server has this capability too
http://msdn.microsoft.com/en-us/library/aa342326(v=sql.105).aspx
Question here is : do you process this data at SQL server side ASAP or you just want to move them ? it looks to me you want to move 11M rows from Oracle to SQL server and then I'd suggest looking at SSIS
http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx
just a though
Please mark as answer if you think this answers your questions
- Sugerido como Resposta amber zhangModerator segunda-feira, 11 de junho de 2012 02:18
- Marcado como Resposta amber zhangModerator segunda-feira, 25 de junho de 2012 02:29
.png)
