.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Cursor Takes Too much time any Solution Please???

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
 

Hii,

       I have 2 multiple Cursors below i shows code of cursors. I have 8000 records in one cursor it's shown below.so when i execute  this sp it will take more then 7 minutes is there any solution to decrease that time.


Declare Asset_Cursor Cursor LOCAL  For   Select Acc_Id,Shift_1_Per,Shift_2_Per,Shift_3_Per From Tbl_Acc_Master Where CoCode=@CoCode  (Cursor1 only 4 to 5 records)
            Open Asset_Cursor
            Fetch Next From Asset_Cursor Into @Acc_Id_Cur,@Shift_1_Per,@Shift_2_Per,@Shift_3_Per
            While @@Fetch_Status = 0                   
            Begin

Declare Item_Cursor Cursor LOCAL  For

                                Select AssetCode,PlacedIntoService,BookValue From dbo.tblAssetItemMst Where Acc_Id=@Acc_Id_Cur And BranchCode=@BranchCode And CoCode=@CoCode And LocationCode = IsNull(@LocationCode,IsNull(LocationCode,0)) 


View Complete Post


More Related Resource Links

Security: Safer Authentication with a One-Time Password Solution

  

One-time passwords offer solutions to dictionary attacks, phishing, interception, and lots of other security breaches. Here's how it all works.

Dan Griffin

MSDN Magazine May 2008


WCF Service: takes very long time to start up

  
Hi, What could be the reasons, why my WCF service takes up to 40 seconds to get started? My service class is set to [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)] I am running a thread inside this class. The WCF service gets initialized and opened within the context of this thread. The Initialization: private void InitializeWCFService() {      // initialize servcie base addrresses      this.SetupBaseAddresses();      //MetaData behaviour : allow HttpGet      ServiceMetadataBehavior metaDataBehavior = new ServiceMetadataBehavior();      metaDataBehavior.HttpGetEnabled = true;      // initialize service host instance      this.seviceHost = new ServiceHost(this, this.httpBaseAddress, this.tcpBaseAddress);      // add service endpoints and behavior.      this.seviceHost.AddServiceEndpoint(typeof(IUpdateServer), this.GetServerBinding(BindingType.Tcp), EndpointName);      this.seviceHost.AddServiceEndpoint(typeof(IUpdateServer), this.GetServerBinding(BindingType.Http), EndpointName);      this.seviceHost.Description.Behaviors.Add(metaDataBehavior);      // MEX endpoint      this.seviceHost.AddServic

is there alternate solution for CURSOR?

  
/* CREATE TABLE X (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES (3, 'S_NAME', 'VARCHAR(50)') INSERT INTO X VALUES (5, 'S_ROLL', 'DECIMAL(15, 0)') INSERT INTO X VALUES (8, 'S_MARKS', 'INT') INSERT INTO X VALUES (9, 'S_SCHOOL', 'VARCHAR(100)') SELECT * FROM X CREATE TABLE Y (UID INT IDENTITY(1,1), S_NAME VARCHAR(50), S_ROLL DECIMAL(15, 0), S_MARKS INT, S_SCHOOL VARCHAR(100)) INSERT INTO Y VALUES ('AYAZ', 10, 99, 'ZINQ') SELECT * FROM Y CREATE TABLE Z (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50), COL_V VARCHAR(50)) */ DECLARE @vSQL NVARCHAR(MAX) DECLARE @UID INT DECLARE @COL_N VARCHAR(50) DECLARE @COL_D VARCHAR(50) DECLARE cur CURSOR FOR SELECT UID, COL_N, COL_D FROM X ORDER BY UID OPEN cur FETCH cur INTO @UID, @COL_N, @COL_D WHILE(@@FETCH_STATUS=0) BEGIN SET @vSQL = 'INSERT INTO Z (UID, COL_N, COL_D, COL_V) SELECT ' + CAST(@UID AS VARCHAR) + ',' + '''' + @COL_N + '''' + ',' + '''' + @COL_D + '''' + ',' + @COL_N + ' FROM Y ' EXEC SP_EXECUTESQL @vSQL PRINT @vSQL FETCH cur INTO @UID, @COL_N, @COL_D END CLOSE cur DEALLOCATE cur SELECT * FROM Z

WCF - First call to Callback instance from Server takes considerable time to make it to the client.

  
Hello. There's a performance problem we are facing that we are unable to fix using DuplexChannels. The first call made by the client to the server reaches the server instantly. The first call made by the server to the first client using a Callback proxy will take several seconds to reach the client. All subsequent calls to the client (as well as other clients) will reach and execute instantly. The client connects to the Server using a proxy to IServiceProvision, supplies an instance to an implementation of IClientCallback and calls IServiceProvision.Start(). The server instantly processes the call and makes an asynchronous call to IClientCallback.DoWork(), which takes several seconds to enter the client's DoWork() implementation. The remainder of the execution then runs smoothly. Also, the server will experience such a slowdown only for the very first call of the first client that connects to it. Is it normal for the Callback interface to take so much time to warmup, only on the first call to the first client? Below is the full source code. Thanks for any information you may provide. Cheers Mike Shared Class Library using System.ServiceModel; namespace Test { [ServiceContract(Namespace = "http://test/IService", CallbackContract = typeof(IClientCallback))] public interface IServiceProvision { [OperationContract] void Start(); [OperationContract] void

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

  
All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

Why does it takes such a long time to load a parameter?

  
Hi all,   I have a parameter which is filled with a set of about 90.000 rows. When running the report, it takes a long time to view the report. While it loads the report the CPU on the client is getting up with 50% and the memory stacks 300 MB. I think that I am doing something wrong as when I run the query directly at the cube, it takes just 9 seconds. Does anybody have coped with this problem? If so, what are the best practices in this? Your help will be greatly appreciated! Daniel

Determine time it takes to copy a datatable into another datatable

  

I'm copying one datatable into another like this:  

temporaryTreeData = treeViewData.Copy();

How could I figure out how much time it takes for the copy to complete?

 

Thanks


Closing a WCF connection takes a long time when using SSL

  

I have a very puzzling problem closing WCF connections. We utilize several different protocols for different customers. The most common is net.tcp. We now have a customer that requires SSL encryption of their WCF calls. It does seem to work, but exhibits some strange behavior when we close the connection.

Closing the connection takes a long time. This could be caused by any number of problems, but the strange thing is that the more data that was transmitted on the connection, the longer it takes to close. Connections that get a small amount of data close reasonably fast, but if a large amount of data was transmitted, closing the connection can take as long as actually retrieving the data. It almost seems as if the data is being retransmitted back to the server for verification before the connection is closed.

We are using reliable sessions and Binary over HTTP protocol.


treeview node selection takes a lot of time

  

Hi All,

I have a tree-view which doesn't have any events and AutoPostBack property is also false. But whenever the treeview node is selected,its taking a lot of time...The tree view is placed inside update panel...I have an update progress and selection of any node shows the progress for atleast 5-6 seconds.Please suggest what can be done....

The code is as below :

<asp:UpdatePanel ID="updatePnlListBoxes" runat="server">
        <ContentTemplate>
<table align="center">
  <tr>
 <td>
<asp:UpdateProgress ID="UpdateProgress2" runat="server" AssociatedUpdatePanelID="updatePnlListBoxes">
 <ProgressTemplate>
 <img id="Img2" src="~/images/ajax-loader.gif" alt="" runat="server" />
 </ProgressTemplate>
 </asp:UpdateProgress>
 </td>
</tr>
<tr>
<td>
 <asp:TreeView ID="tvNodes" runat="server" NodeIndent="15" ShowExpandCollapse="true"
ShowLines="True" Width="181px">
<ParentNodeStyle Font-Bold="False" />
<HoverNodeStyle Font-Underline="True&

SSIS 2008 - Fuzzy lookup warming index takes too much time

  

Hello All,

We are using Fuzzy lookup transformation in our SSIS package.

We have selected option to store and maintain index for first time. And for subsequent executions, we use option to use existing index. So it will not create new index each time. We are doing fuzzy lookup on single source table with single column. The source table contains more than 2.5 million rows.

So while executing the package, although its not taking time to build complete index. But still it takes considerable time and uses large memory whenever we execute it.

We have also set the WarmCache option to true. Although all data is loaded into memory and removed after execution.

Is there any other way so we can keep the data in memory, so for subsequent execution, it gets result directly from memory?

Please let me know if you have any suggestion? Its urgent.

 

Thanks in advance.



Darshan Vyas

Accessing solution properties in code at design time

  

Hello all,

We have a project working with WCF embedded in a WindowsService. To facilitate testing, we have a Client Form and a Host Form. This project has multiple WCF services so we have 4 ClientForms and 4 HostForms. We are using multiple start up projects in the solution which is accessed by right clicking the Solution file at design time and selecting multiple clients and hosts. I would like to put that functionality on a form to allow dynamic switching (eg a series of check boxes for Client1, Host1, Client3, Host3, etc).

I'm guessing this pertains to using EVNDTE but I have not used that very much recently. Can this be done fairly easily, and if so, would someone be kind enough to post some code to get me started? Thanks in advance for any ideas and / or suggestions!


Have a great day!

SSIS - OLE DB from VIEW takes long time

  

Hi,

I have a question regarding SSIS 2008 and how it handles views. I hope the answer for this question will be a quick "Get a hotfix" heh.

 

I am experiencing a very odd behavior with setting up a View as a data-source in OLE DB. I have set up a a very simple package (for experimenting) with a Data Flow Task that has only two components, Data-Source and Data-Destination. I have set the data-source to a view and the destination to a table in the same database.

Note: I am developing the package on the server itself.

Issues:

  • While trying to preview data-source I get an error, "Query Timeout expired (Microsoft SQL Server Native Client 10.0)
  • When executing the package Validation, Pre-Execute phases are complete but no rows are passing through the buffer. I have waited over 12 hours before stop the job. Checking the monitoring verified that IO was on 1. And that no object was locked , which may have caused the package to run so long or not to retrieve data.
  • When connecting to SSMS and doing export of data from the view - it also retrieves no data and the process seems to be stuck.

 

Additional data:

  • T

first login takes a long time

  

hi all,

i have set up a moss site, and i notice an interesting thing that the first login of every day usually takes a long time, while subsequent logins of the same day  take  much less time. what could be the problem? is it related to caching behavior?

any suggestions on how to improve the response speed?

thanks


Andrew Chen
BI, Data Mining, Analytical CRM

Why WPF bug fixes takes so long time?

  

Hello!

Why the feedbacking takes so long time to get official comments? I'm not speaking about getting the issues to be fixed as it takes even more.

The perfect sample:

http://connect.microsoft.com/VisualStudio/feedback/details/483010/wpf-gridsplitter-randomly-jumps-when-resizing

It is a known bug for a three years (there is a link to a first discussion in a feedback comment), and the offical answer is:

Posted by Microsoft on 8/21/2009 at 2:35 PM
I do understand your concern, but we are beyond our ZBB date, therefore only ship-blocker bugs make the bar at this point. Considering that we have shipped this bug before, it is hard to make a case that we need to fix it in this release since it did not block our previous release. Thank you.

So, the official policy is "Now, we have a six months before RTM but we will not fix any bugs but those that blocks tth product shipment"? Perfect!

I've a bunch of bugs/suggestions submitted and (for most of them) there's no single comment neither reaction for a six month for now. I'm

How to look a single MSSQL query takes how much cpu time for completation

  
How to look a single MSSQL query takes how much cpu time for completation

when i run the query via sql server managament studio i want to see how much cpu time did this query consumed

it will be pretty low but i need to see for comparing different query loads

and it would be even greater if it is possible to run query 1000 times and see total consumed time

thank you
Best of Pokemon Games PokemonCraft Developer Used asp.net 4.0 routing at it's Pokedex

Publish a Workflow from SharePoint Designer takes more and more time

  

Hi,

I started a workflow from scratch, and often while designing my workflow I save it & publish it to test it on my dev environment. However, the more I had actions, the longer it takes. I have now roughly 30 actions and condition (including Approval Process, Workflow Status and collect data), and it takes about 6 or 7 minutes to publish! Is this normal?

Thanks

 


Julien http://www.sharepointofview.fr/Julien

Closing the download stream (got from HttpWebResponse.GetResponseStream()) takes some time if we clo

  

Hi,

I am using HttpWebRequest to download a file from server. When I get the response stream and start reading from it, I need to cancel reading and close this stream in between (this is something like pause download). Please see the code below.

 

 

Stream serverStream = resp.GetResponseStream();

Stream localStream = new FileStream(file_path, FileMode.Open, FileAccess.Write);


 

try{

            HttpWebRequest getrequest;

            getrequest = (HttpWebRequest)HttpWebRequest.Create(download_uri);

            getrequest.Headers.Add(HttpRequestHeader.AcceptEncoding, "gzip,deflate");

            getrequest.Timeout = System.Threading.Timeout.Infinite;

      

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend