SQL Server Performance Forum – Threads Archive
cursor location and stored procedures
Hi I have written a stored procedure that bulk inserts from a text file into a temporary table in the tempdb, compares the content of this temporary table to a table in another database for any duplicates, deletes duplicates in the temporary table and then inserts all the remaining records from the temp table into the table in the other database. I am calling my stored procedure from a VB application – would it be recommended to set the curosor location to server-side or does not this matter when you are excuting stored procedures as they are executed on the server-side anyway (don;t know if I’m right or not on the last statement) ? ThanksCE
It depends on how much data you work with. If it’s a lot of data it’s often better to keep the data on the server with server-side cursors. If it’s not much data you generally get better performance with ha client side ADO cursor. You have different types of server-side cursors too and different lock types that all impact performance.
Just to clarify the stored procedure that i am running does not pass back a recordset or anything so all the work is done on the server side as such – i thought when you run a stroed procedure that all the work was done on the server side anyway so does it make a difference (if you are not returning any data) if you set the cursor location of the connection object in VB to server side instead of client side? CE
If you’re not returning any data the ADO cursor type or location won’t matter. You could exectue the stored procedure with the adExecuteNoRecords option so that ADO knows that no results will coming back. You save some overhead and memory usage by doing this.
]]>