Solved

Performance Procedure

Posted on 2013-01-26
6
228 Views
Last Modified: 2013-01-27
I can win performance on sql server stored procedure. I need use cursor with fast_forward or while @@rowcount>0 i improve most performance?
0
Comment
Question by:fsouza
6 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 38823126
A cursor is usually the slowest way to do anything. Can you please post what you are trying to do?
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38823306
It depends on the situation ... I mostly try to avoid both ... If you explain what you want maybe we can help

P.S. Performance matters if you dealing with large amounts of data
0
 
LVL 6

Accepted Solution

by:
esolve earned 250 total points
ID: 38823540
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:fsouza
ID: 38824177
The correct use is CURSOR LOCAL FAST_FORWARD or CURSOR FAST_FORWARD ???
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38824313
correct use is to avoid both ... atleast read those articles before asking same question again...
it all depends on your scenario which you havent told us yet :-)
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 38824321
The correct use is CURSOR LOCAL FAST_FORWARD or CURSOR FAST_FORWARD ???
If I understand you correctly the question is:

What is the difference between using the keyword LOCAL and not using it?

The answer is nothing.  They are the same.

Here is the definition from SQL Server's BOL (my emphasis):
LOCAL
Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL
Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

Note:  
If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier  
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now