.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

Recursive computation using scope, cache, and query order

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server
 
I have a situation where I have two queries Q1 and Q2 and the order of execution of these queries produces drastically different behavior. It seems that the cache is playing a strange role here which I do not understand.

Q1 executes fast always. Q2 when executed after Q1 executes fast. However, after dropping the cache (by doing a deploy/process), and when Q2 is executed first,  the query Q2 never returns. Perhaps it is related to the way I have setup a recursive computation using scope. I would appreciate if someone can look at the computation below and offer insights into where the problem may be.

Scenario 1) 
 Execute Q1 - fast response
 Execute Q2 - fast response

Scenario 2)
 Drop the cache, by doing a process.
 Execute Q2 - NO response from the server, SQL Profiler shows millions of cache queries.
 Abort Q2.
 Execute Q1 - fast response.
 Execute Q2 - fast response.

----------------------------------

Details of queries, and the context.

Q1: SELECT FutureWeeks ON ROWS,
 {[Product].[Product Name].[CHJ-1062]} * [Measures].[Replenishment]
 ON COLUMNS


View Complete Post


More Related Resource Links

how to create a List order and unourder a simple Sql Query

  
A Simple Query Pass to connection String 1 My Query is SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a   2,My Data is HD    HEAD    SH    SUBHEAD    TRK    TRACK    TGT    TARGET    PDC    DT_RELEASE    STATUS    COHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR1    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP    EdlinkHR    HR  

Strange behaviour of a query using a recursive CTE

  
Hi, I have a problem with a query. The situation is a bit complicated but I'll try to explain it the best I can. Short story: I have a .NET application that executes a recursive query (using CTE) and then uses a DataReader to read each row's each field one-by-one using the GetValue() method. With specific parameters this query behaves quite fast but with other parameters it can take more than a second to read a row's field (instead of the usual ~0.00001 second). Long story: The database: I have 4 tables with the following columns: 1. Entity table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255)), Description (nvarchar(2,000)). 2. Relation table: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), ParentEntityId (int), ChildEntityId (int), Ordinal (int). 3. Property table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255), Description (nvarchar(2,000)). 4. EntityProperty table with 10,000 rows: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), EntityId (int), PropertyId (int). The Entity table has 100,000 rows, the Relation table has 100,000 rows (each entity has a relation), Property table has 22 rows and the EntityProperty table has 2,010,000 rows (properties with Id from 1 to 20 are assigned to all entities and properties with

Strange behaviour of a query using a recursive CTE

  
Hi, I have a problem with a query. The situation is a bit complicated but I'll try to explain it the best I can. Short story: I have a .NET application that executes a recursive query (using CTE) and then uses a DataReader to read each row's each field one-by-one using the GetValue() method. With specific parameters this query behaves quite fast but with other parameters it can take more than a second to read a row's field (instead of the usual ~0.00001 second). Long story: The database: I have 4 tables with the following columns: 1. Entity table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255)), Description (nvarchar(2,000)). 2. Relation table: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), ParentEntityId (int), ChildEntityId (int), Ordinal (int). 3. Property table: Id (int), Version (bigint), InternalName (nvarchar(255)), ExternalName (nvarchar(255), Description (nvarchar(2,000)). 4. EntityProperty table with 10,000 rows: Id (int), Version (bigint), EffectiveStartTime (datetime), EffectiveEndTime (datetime), EntityId (int), PropertyId (int). The Entity table has 100,000 rows, the Relation table has 100,000 rows (each entity has a relation), Property table has 22 rows and the EntityProperty table has 2,010,000 rows (properties with Id from 1 to 20 are assigned to all entities and properties with

Recursive XQuery Order by

  

I got an recursive XML in my database generated by my stored functions. Simple example:

<MEMBER POSITION="9">
 <MEMBER POSITION="4">
  <MEMBER POSITION="4"/>
  <MEMBER POSITION="1"/>
 

Order By clause in RowNumber function slowing down the performance of my query.

  

Hi,

I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.

Thanks,

Deepti

 

 

 


Order parameter MDX query

  

Hello,

how can I arrange outcome of MDX query by ParameterName? In my query it doesn't work.

WITH

 

MEMBER [Measures].[ParameterCaption] AS '[Organisation].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER

 

[Measures].[ParameterValue] AS

select Parent/Child id recursive query

  

Table

Rank chid parid

7       6

5       4

4      3

3      2

2      Null

 

My output should be

 

Rank chiid parid

1        5       4

2        4      3

3        3      2

4        2      Null

 

parid : 6 not matching with child id, it should not come in select query

parid : 2 has child id , so it should be appear in the select query

Rank as row_number()

basically , last parent id should be next row of child id, O

only matching parid should be next row as child table..continues.....

I need some type of sub query , I dont want link please.

Thanks

 

Filter SharePoint list based on ID and scope as Recursive in DataFormWebPart

  

The following is the code in the DataFormWebPart

<DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" UseServerDataFormat="true" selectcommand="&lt;View&gt;&lt;ViewFields&gt;&lt;FieldRef Name=&quot;ContentTypeId&quot;/&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;File_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;RoutingEnabled&quot;/&gt;&lt;FieldRef Name=&quot;Category&quot;/&gt;&lt;FieldRef Name=&quot;Approach&quot;/&gt;&lt;FieldRef Name=&quot;Channel&quot;/&gt;&lt;FieldRef Name=&quot;Competency_x0020_Pillar&quot;/&gt;&lt;FieldRef Name=&quot;Contact&quot;/&gt;&lt;FieldRef Name=&quot;Cost&quot;/&gt;&lt;FieldRef Name=&quot;Cost_x0020_Description&quot;/&gt;&lt;FieldRef Name=&quot;Country&quot;/&gt;&lt;FieldRef Name=&quot;Editor_x0027_s_x0020_choice&quot;/&gt;&lt;FieldRef Name=&quot;Execution_x0020_Plan&quot;/&gt;&lt;FieldRef Name=&quot;External_x0020_Links&quot;/&gt;&lt;FieldRef Name=&quot;Issue&quot;/&gt;&lt;FieldRef Name=&quot;Lessons&quot;/&gt;&lt;FieldRef N

slective order by in sql server 2005 query

  
except ascending or decending can we define our own choice of order by, like if i set order by hour it display 0(zero) first, but if time start form 1800 then how to display 18 first then 19 then 20 then so on to 5am can any body help in sql 2005 server

procedure cache having two query plans

  

I noticed that a stored procedure takes excessively long when it's called from within an application/management studio. I understand what is actually happening, but I do not have a reason for why this is happening.

The difference between the query plan are as follows

 

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: False, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

ANSI_NULLS: True, ANSI_PADDING: True, ANSI_WARNINGS: True, ARITHABORT: True, CONCAT_NULL_YIELDS_NULL: True, NUMERIC_ROUNDABORT: False, QUOTED_IDENTIFIER: True

The difference here is that ARITH ABORT is on within one plan and is not on with the other one. ARITH ABORT on tends to be faster. Any ideas on how we are encountering this problem and a long term fix. One fix is to alter the procedure and set ARITH ABORT on, but i am thinking there may be a reason for this.


Using Group by and Order By in a Single query

  

Hi Guys,

I have a requirement to use Group By and Order By in a single query and I used it but the results are not as estimated so I need your help guys

I am writing a query like this

select id, opendate, lastactiondate, (opendate-lastactiondate) as responsetime, ownergroup from ticket group by ownergroup, responsetime, opendate, lastactiondate, id order by opendate

What I am expecting is it will group the results and then order the results in that groups based on the open date but whats happening is opposite it is ordering by opendate first and then grouping if possible

So I see Order By is overriding Group By, is there a waywe can group first and then Order by open date inside that groups

For your Informtion: I am using DB2 Database

Help is appreciated


Search Result web part generate "Scope in your query does not exist."

  

Dears ;

Could any one help me to sove the problem with search query in Moss 2007

before two days the search was working smoothly but we extend our farm and we add new server to be as indexing server

i modifed the search settings to the

  • application server will be the indexing server and will crawl the contents
  • web front will run the search query service

and i started full crawl

when i tried to search by simple box search it gives me Scope in your query does not exist

and when search by adnced search web part its working fine

and after many investigations i found that my simple search works fine when i desplay the drop down list of scops

e.g http://www.x.com/advanceSearch/results.aspx?k=%cat%86&s=All%20Sites 

but when i hide the drop down list of scops my query which generated is

http://www.x.com/advanceSearch/results.aspx?k=%cat%8

and i would like to hide the scops drop down list for design target and the search sould work

Could any one tell me where the problem ?

 

thank you

 

 


Query with ORDER BY fails on unindexed column

  

I Got a table with about 10000 records. When I use the query (for testing) SELECT * FROM <table> there is no problem with it....
But the query SELECT * FROM <table> ORDER BY date DESC (and column date is an unindexed column) the query never finishes.

quering the database with ORDER BY on an indexed column, the resultset is there in a couple of seconds.

Can anyone give me some advise?


Using Union and Order in query

  

SQL Server 2008. Guys,  I need to do following. I have tables Employees(empid,country,region,city) and Suppliers(supplyid,country,region,city). So I have a query:

SELECT country, region, city
FROM HR.Employees

UNION ALL

SELECT country, region, city
FROM Production.Suppliers;

So I need

to add logic to the query 
such that it would guarantee that the rows from Employees
would be returned in the output before the rows from Customers,
and within each segment, the rows sh

Recursive query?

  

I have a dataset similar to the one shown below and need to end up with one record which contains all of the descriptions concatenated together in the proper order

Code            RowNum                  Description

A                 1                              The quick brown

A                 2                              fox jumped

A                 3                             over

A                4          &n

Single Result Set from Recursive Query

  

I have a very simple LocationHierarchy table with ID, ParentLocationHierarchyID, Name columns.

The proc below produces the exact output I want but I want to create a result set that I can pull into a datatable or whatever.

How do I do this? Adding a simple select where the print command is simply creates a result set for each row. Not what I want.

I imagine using some sort of Temp Table might work, but do not know how to impliment it.

Thank you for your help

Earl

 

 

ALTER PROC dbo.ShowHierarchy
(
 @Root int,
 @MaxLevel int
)
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @ID int, @Name varchar(30)

 SET @Name = (SELECT Name FROM dbo.LocationHierarchy WHERE ID = @Root)
 
  IF @@NESTLEVEL<@MaxLevel
  BEGIN 
   PRINT Cast(@Root as Varchar) + ' - ' + (REPLICATE('-', @@NESTLEVEL * 2) + @Name)
  END
 SET @ID = (SELECT MIN(ID) FROM dbo.LocationHierarchy WHERE ParentLocationHierarchyID = @Root)

 WHILE @ID IS NOT NULL
 BEGIN
   EXEC dbo.ShowHierarchy @ID, @MaxLevel
   SET @ID = (SELECT MIN(ID) FROM dbo.LocationHierarchy WHERE ParentLocationHierarchyID = @Root AND ID > @ID)
 END

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.
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