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


Post New Web Links

CTE & HASH UNION query hint

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

Hi everybody,

I am updating a query for a report. The original query has an index hint for the JOIN and also a HASH UNION hint for the query (the query consists of two parts with UNION operator). Now, on the client we do one extra simple query to add a total amount for each group.

My first thought was to remove this extra processing on the client (though I suspect it's rather quick) and add totals using cte of the original query and partition by group clause.

Now, 1) Do you think this idea is good at all to add this extra column directly from SQL Server

2) How (and there) should I add this query hint? It doesn't allow me to do so in CTE as I'm getting a syntax error expression.

In the meantime I'm also experimenting with this query on a huge database with adding/removing hints.

Thanks in advance.


For every expert, there is an equal and opposite expert. - Becker's Law


My blog



View Complete Post


More Related Resource Links

Linq Query Union

  
HelloI need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using unionDim CStep() As New List(Of ProductModel) CStep= (From p In db.Produts _ Where p.CategoryID= 1 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).Union (From p In db.Produts _ Where p.CategoryID= 2 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).ToList()

Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Here is my stored proc code. Exec plan is in Excel Spreadsheet form.

https://spreadsheets.google.com/ccc?key=0AtpBDycPp-0_dDZBVHVkdU5QejBmSEE2MGM2bUREQlE&authkey=CN7wqLML&hl=en#gid=0

 

USE [HDIS]
GO
/****** Object: StoredProcedure [dbo].[sp_GetBlockSpecimenInfo] Script Date: 09/23/2010 17:26:11 ******/

Help :Is this Query right? How to Get the Result: Which to use union OR join AND How?

  
Can you please help me?

QUERY1:

SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

GETTING RESULT:
T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11

QUERY2:

SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

GETTING RESULT:
T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:
SELECT * FROM (
SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2

Group by Clause in Union Query

  

Table 1

Qty BatchNo MaterialCode

54 211 127

50 AR0347 165

252 054 190

180 022 191

50 HP10004 194

20 005010 196

 

Table 2

Qty BatchNo MaterialCode

6 211 127

28 054 190

20 023 191

 

Select Sum(iQty) as iQty ,vBatchNo,vMaterialCode

from

(

Select 

 a.iQty as Qty,      

 a.vBatchNo   as  BatchNo,      

 a.vMaterialCode &

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

Adding Rows to a query from another query, while avoiding UNION

  

OKay,

I've looking into PIVOT and UNPIVOT which I don't believe will work, and utilizing an OUTER APPLY may work, but hamfisted attempts have not yet been successful.  The database I'm working on is stupid, pointless, frivolous, and mostly just a learning tool for me to figure a few things out, while having fun with one of my video games.  So, to get the embarrassing aspects out of the way, the database is a Move/Combo list for characters in Mortal Kombat 4.  (yes, i know, don't ask).

I lined it up as several list tables (basically what i call tables that rarely if ever change, but are used as parent tables in FK relationships.  A good example is lst_Buttons.  THis is the list of U,D,F,B,HP,HK with a description, Abbreviation and PK ID integer.  Once the buttons are added to this table, they are never messed with again, but having them in a table makes it easier to link them into the junction table of tbl_Moves and lst_Buttons, called tbl_MoveKeys.  So, like a basic move is Jump Kick,thus tbl_Moves has ID: 1, Move: 'Jump Kick'.  tbl_MoveKeys has MoveID 1, ButtonID 1, MoveID 1, ButtonID 6, and thus a silly little formatting function returning an NVARCHAR for a computed column in tbl_Moves is Command: U+HK.

I then have several other table like Locations, Weapons, Combos, and Characters. Now, understandin

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.

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

Need Syntax To Make Results of LINQ Union Return Non-Generic Type

  

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
        {
            using (WorkbookDataContext dc = _conn.GetCont

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.


MDX Query parameter from SSRS

  

Hello,
I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )

 

 


Get ServerReport selected Parameter value to use in vb query

  

I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.


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