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


Post New Web Links

Succeeding loop of the same query takes longer than the previous one

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :ASP.Net
 

Hi, I'm having trouble with an itermittent problem I'm facing with our site. See I'm running an sql query that accepts date range as variable. When I run the query in sql manager it runs in less than a second no matter what date range I put in. I have a function that loops a dozen times to get records for the past days getting a whole day's record for every loop. It would usually work fine but there are times that the succeeding loops slows down. I made it print out the duration for each loop and this is what I got in seconds.

start: 10/5/2010 12:00:00 AM|End: 10/5/2010 11:59:59 PM|0.5460009 <-- should be more or less the normal speed of each loop
start: 10/4/2010 12:00:00 AM|End: 10/4/2010 11:59:59 PM|14.3208252
start: 10/3/2010 12:00:00 AM|End: 10/3/2010 11:59:59 PM|14.5860256
start: 10/2/2010 12:00:00 AM|End: 10/2/2010 11:59:59 PM|14.1024248
start: 10/1/2010 12:00:00 AM|End: 10/1/2010 11:59:59 PM|4.6956082
start: 9/30/2010 12:00:00 AM|End: 9/30/2010 11:59:59 PM|5.4444096
start: 9/29/2010 12:00:00 AM|End: 9/29/2010 11:59:59 PM|8.3304146
start: 9/28/2010 12:00:00 AM|End: 9/28/2010 11:59:59 PM|11.0760195
start: 9/27/2010 12:00:00 AM|End: 9/27/2010 11:59:59 PM|20.1708354
start: 9/26/2010 12:00:00 AM|End: 9/26/2010 11:59:59 PM|20.8884367
start: 9/25/2010 12:00:00 AM|End: 9/25/2010 11:59:59 PM|15.444027


View Complete Post


More Related Resource Links

Stored Procedure takes longer than the query.

  

Hi,

I'm stuck up in a situation where my sp takes longer time but the query takes lesser time. I went through all the posts mentioned in the T-sql Forum and i tried all the possible resolutions mentioned there but it did not help me.

I have a sp which has around 20 parameters and i tried assigning them to local variables as mentioned in various posts but that did not help me.

Could you please help me out with this problem?

 

Thanks,

Deepti

 

 

 


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])));

Query takes too long to run

  
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH. UPDATE table SET column1 = column2 FROM table1 JOIN table2 ON column1= column2 Thank you

Query takes too long to run

  
I have the following query, which does an update statement but everytime I run it it took about 1hr and 20 mins to run so I created Indexes because that would solve the problem. After creating the Indexes I re-run the query but it was the same so I checked to see if it was blocked by running the following query and under the spid it had the same spid in under the blocked column, the lastwaittype is PAGEIOLATCH_SH. UPDATE table SET column1 = column2 FROM table1 JOIN table2 ON column1= column2 Thank you

While loop in SQL Query

  

I have create a function in sql RawMaterial with use of cursor and while loop.

Where I did mistake I didn't understand...

My query is

CREATE FUNCTION RawMaterial(@date varchar(100)) RETURNS INT
as
begin
DECLARE @item_total int
DECLARE @qty int
DECLARE @item varchar(100)
DECLARE @tempitem varchar(100)
DECLARE c CURSOR 
FOR
select item FROM master.recipe_item
open c
FETCH NEXT FROM c into @item
WHILE @@FETCH_STATUS = 0
BEGIN
(select @item_total = SUM(Cast(qty AS int)) FROM master.recipe_ingredient WHERE recid IN (SELECT recid FROM master.recipe_recipe WHERE item = @item))
(select @qty = cast(qty as int) FROM executive.consumption_total WHERE item = @item and date = @date)
FETCH NEXT FROM c into @item
RETURN @qty - @item_total

end
close c
--PRINT  (@item_total - @qty)
--RETURN @avail 
RETURN 234
END

but when I execute this function using

select dbo.rawmaterial('20-09-2010')

It will return only single row.

like

columnname

45


but i need all rows

like

columnname

45

43

23

43


how is it possible? Is function returns more than one row?



Do while loop query

  

Dear All,

Kindly check if my syntax is correct since I can't make it working.

CREATE PROCEDURE Schedule
(
@CutOffID INT,
@IDNumber INT,
@DateFrom DATETIME,
@DateTo DATETIME,
@ShiftID INT
)
AS
WHILE (@DateFrom <= @DateTo)
BEGIN
INSERT INTO ScheduleBreakdown (CutOffID, IDNumber, Date, ShiftID)
VALUES (@CutOffID, @IDNumber, @DateFrom, @ShiftID)
DECLARE @ScheduleID INT
SET @ScheduleID = scope_identity()
SET @datefrom = DATEADD(DAY, 1, @datefrom)
END


Error: Cannot insert null to ScheduleID

I need to achieve like this for example

DateFrom '8/1/2010'

Date To '8/15/2010

ShiftID '1'

IDNumber '1'

Results

ScheduleID     IDNumber          Date            Shift

1                    1                       8/1/2010       1

2                    1      &n

How to query previous and next record?

  

hi there,

  i would like to query out the next record and previous record guid from the current record guid.But i don't want to use the ID + 1 or ID - 1  because the ID is not secure for query like this. I am using MS SQL.

May i know how can i write the sql query?  Please give me a guidance.

 

 

hope can hear from you all soon. Thank You


Query takes too long to run

  
Every time I'm trying to run a query it is taking too long to run the simplest query, in order to run the queries, I have to go to the Activity monitor and kill off processes that have been sitting even though they are not running. Is there a way the processes can be killed off when they have finished executing?

Thank you

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

Microsoft's Collaborative Application Markup Language (CAML) Loop

  
discovered an interesting error recently while working with Microsoft's Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

  
Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

  
If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Retrieving a variable from a previous page

  

Hi,

I'm looking to have a search page where a user will select an item from a listview, then then taken to another page that wil display data based on their original selection.

A user will type in a string and be presented with a list of usernames that are like that string, when they select a user name who's profile they would like to view I would like the page that they're taken to, to be that users profile.

How would I transfer the variable from the search page to the profile page, and when I work on the profile page, how can I access that variable?


Thanks.


sql query that don't run

  

Hello....

I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query

  

I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:


Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5
&n

How to send an email with information in query string

  

First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.


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