DataSource Controls :: Performance In Stored Procdure With Cursor?
Apr 8, 2010
I have created the stored procedure in SQL server 2005. i have used nested cusrsors in this SP. also many records are there. So, its performance is too low.
i forced to use cursor, because the this is calcualtion process. i have to fetch the records one by one, do the calculation and insert the result to the another relevent tables.
So it cause big performance issue.this is my sample scanario.
First i have to take the products from the customerforecast for the certain duration. (i used cursor to fetch the products)
then i need to get the list of machines can run for the each product. (i used cursor to fetch the machines)
then i have to fetch the Unit per hour value for the combination of machine and prduct.then i have to use formula and do the calculation and store the result to the 3 different tables.
View 2 Replies
Similar Messages:
May 15, 2010
I have a stored procedure that uses a cursor and also uses another stored procedure to return a column from the returned records.
[Code]....
See that part with 'exec dirinfo.dbo.d_searchempybyname @TicketEmail'? It works and returns the records but what I really need is to return a COLUMN from the record and store that column value in a variable which I can use - for example 'PRINT @VariableName'.
View 2 Replies
Feb 3, 2010
I am using Sql server 2005. I have a stored procedure, which has a select statement to retrieve thousands of data from the table.
The time taken to execute is longer and hence affecting the performance.
[Code]....
Here, id is the primary key. Clustered Index is also set.
How to improve the performance of the stored procedure?
View 13 Replies
Apr 29, 2010
I am not sure if this is the right forum. I can not find a forum for LINQ.
I am working on an application using LINQ. Application performance is not up to par and my tests show that it is LINQ queries that are slow. I was wondering if anybody can recommend where I can find an article about optimizing LINQ performance maybe by compilation or other methods.
View 1 Replies
Mar 11, 2010
Running a select * from my table give me data like this:
Field1 Field2 Field3 Field4 Field5 Field6
NULL NULL NULL J 4GG4K C46D
DRIVE 0155 1 NULL W6665 NULL (C46D)
TEST 01444 5 NULL W4434 NULL (C46D)
NULL NULL NULL 1 WGG4K AAVE
HOME 01444 3 NULL 5HTYL NULL (AAVE)
NEW 09888 9 NULL 8HTTTE NULL (AAVE)
The problem is Field6 for the rows that are bolded. I need Field6 to repeat for each row until the next non null value is found in Field6, than I need that value to repeat again. I placed in parenthesis outside the NULL for Field6 what I need to show which is the value of Field6 above the records which Field6 is null.
I know this is confusing, but it's actually pretty simple to understand. I just need to get the first NON NULL value of Field6 and UPDATE the records below it where Field6 IS NULL to that value, then when it reached the next bolded row there is a new value for Field6, I then need to get that value and UPDATE the records below it which are null with the value.
View 7 Replies
May 13, 2010
Tag TableName FieldNameabc wc s_namelbl wc s_deslb2 wc s_prodin above fieldname(s_name,s_des,s_prod) column represent field in wc table.How can i bring this result. is this possible without using cursor. i was struggling past two days. i cant able to bring result for above.
View 17 Replies
Apr 24, 2010
I have a requirement where I need to declare a Sql Statement dynamically in a cusror. When I declare dynamic Sql Statement and execute the SP, it throws an error message. The Cursor is not recognizing the dynamic Sql command.
As per the requirement, the sql statement will change according to the input parameter and it can't be declared as a static sql as shown below. Can anyone tell me, how this can be achieved any other alternative to do?
View 1 Replies
May 18, 2010
I am curious, if it's better to iterate through a set of records using cursor or using a WHILE (SELECT ..) ? I have read that cursors can be expensive, so may be WHILE (SELECT..) is a better approach.But I am not sure ?
EXAMPLE OF WHILE (SELECT..) is as below
DECLARE @warehouseId INT
DECLARE @warehouseIds TABLE ( WarehouseId INT )
[code]...
View 2 Replies
Feb 22, 2010
I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents.
OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");
View 5 Replies
Mar 19, 2010
how can i find the total null values in a table without using sql cursor .
i want to find the percentage of the data which is avaibale in the table and how much percentage of data is unavailable ( or null). to make a comparision chart.
i have 6000 rows and 200 columns in a table , if i use sql cursor , it is taking too much time to execute ( 6000 * 200 ) loops. is there any inbuilt-function in sql to do it .?
View 19 Replies
Apr 27, 2010
I am using Visual Studio 2008 and SQL Server 2008 and have added a SQL datasource to my form.
When I configure the datasource, I choose my connection string which looks like this in my webconfig:
<connectionStrings>
<add
name="PSFSPRD_ConnectionString"
connectionString="Data
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
View 3 Replies
Apr 17, 2010
I have got a table in SQL Severs as following, which contains sales data (approx 10m records), the indexes and primary keys are SalesDate, StoreNo and ItemNoSalesDate StoreNo ItemNo SalesQtyWhen I am querying the table for a given item and store's sales for the last 7 days, if I use the query:
[Code]....
The query runs almost instantly, obviously using IN is the faster mehtod here, but I have read elsewhere that using BETWEEN is the quicker method, can anyone confirm?
View 2 Replies
May 5, 2010
I have one online school management application developed on asp.net 3.5 using Linq,
Now in my database i have main three talbes which uses in many store procedures,functions,linq join query
CourseMst - Master Table Have CourseID P.K.
StudentMst - Master Table Have StudentID P.K.
Student_Course - Child Table Have StudentCourseID P.K
StudentID F.K. Reference To StudentMSt
CourseID F.K. Reference To CourseMSt
Now i want to increse speed of my application for those page which have query or sp related to above tables,
I want to know about indexing procedure that how to apply indexing and on which field i have to apply indexing
View 5 Replies
Apr 6, 2010
i am calling a scalar function from a stored procedure. Does using user defined scalar finction has a negative impact on performance.
View 1 Replies
Jun 5, 2010
I had created a web site with asp.net and sqlserver. How can i improve the my website's performance in with regard sqlserver connection?What's your mind about use pool connection? In this regard what the defualt values is set for 'Max Pool Size' and 'Min Pool Size'?What's your mind about use Asynchronous access to data? What's your mind about other ways that you think?
View 3 Replies
Oct 2, 2010
I have written one store procedure for fetching huge data like this. there are nearly 2000 records. It is taking nealy 50 sec to execute. Could you tell me best method to fetch large no of data ?
ALTER PROCEDURE bulls_orbit.BuySellUpdateFetchData
AS
Select Id,Message,EndDate,RefId from tblBuySellUpdate Order By Id Desc
RETURN
View 6 Replies
Mar 21, 2010
I have a select query where iam fetching the data from minimum of 7 different tables by using innerjoin. I have 3 lakhs of records in that table.BY using cache Iam fetching all 3lakhs of records at a time and put in cache. by using the rowfilter i use the records.firsttime when iam fetching these 3lakhs records it takes 3min. want to improve the performance of time.How to improve the performance of time?
View 1 Replies
Feb 2, 2010
I have web application in ASP.NET 2.0 and C#.I have a gridview and i have a checkbox in the gridview. I am trying to save the checked record from the gridview to the database. The time taken to save the data is becoming huge and causing performance issues.
[Code]...
View 8 Replies
Apr 29, 2010
I have a dataset that comprises of multiple tables (about 20) linked together with constraints to reflect the relational database. My question(s):
1) Will having multiple tables affect performance? I'm asking this as there are often times I would not need to get data for all the tables, but only for specific tables.
2) If it does affect performance, would breaking up the tables into their own datasets be the best solution. It seems to me that by doing that, you would lose the ability to apply constraints among tables and tables that might exist in multiple datasets will be subjected to replication.
I would like to have everything in one dataset as I get a view of the related tables and their relationships and also because the Fill method allow me to fill tables, maintaining the hierarchy of the data, but performance is still a key factor.
View 5 Replies
Apr 25, 2010
how can i use cursor in this function ?
ALTER FUNCTION [dbo].[GetCatIDChilds]
(
@CatID [code].....
View 1 Replies
Feb 16, 2011
I have a query that I want to test the performance of when the query is included directly in the SqlDataSource versus when I call a stored procedure. Does anyone have suggestions on how I would be able to closely watch the actual steps here? I was thinking of turning on tracing and adding Trace.Write for each of the events that fire along the way. This doesn't seem efficient and I wanted to see if there was a better approach.
View 2 Replies
Apr 2, 2010
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
View 4 Replies
Dec 9, 2010
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 Replies
Dec 16, 2010
public void ModifyEnter()
{
combo1.Attributes.Add("onkeydown", "if(event.which || event.keyCode)" +
[code]...
View 1 Replies
Jan 18, 2010
Does anybody if it is possible that a stored procedure returns rows which is the result of the execution of another sp? Something like..
[Code]....
View 11 Replies
