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


Post New Web Links

Query optimization in sql 2005

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :ASP.Net
 

 Hi ,

        How to optimize sql query in sql server 2005, any idea
 




View Complete Post


More Related Resource Links

SQL Reporting Service 2005 - share schedule report performace T-SQL query?

  
Hi, I have SQL 2005 reporting services Shared Schedules and each schedule has its own subscribed report. I would like to have T-SQL 2005 to find out performance loading on each schedule. i.e. MySchedule_1 has 10 reports in it and AVEGARE report eaxecutiontime is like 3mins 5sec      MySchedule_2 has 7 reports in it and AVEGARE report eaxecutiontime is like 4mins 9sec Pls can I have T-SQL 2005 on ReportServer database to find out load on each schedules (and more drill-down to each report level for execution time)?

MDX Query optimization

  
Hello Experts, I have a MDX query which first selects those promotions and products valid for a customer and a particular period. Then those sets are used for data extraction from cube. WITH MEMBER   [MEASURES].[Measure_Value] as Sum   ( { [MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] } ) SET   [Products_Subset] as FILTER ( {[Product].[TU Code]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) SET   [Promotion_Subset] as FILTER ( {[Promotion].[Promotion ID]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) MEMBER   [Measures].[Level2 Customer Code] AS   [Customer].[Hierarchy].CurrentMember.Properties("Level2 Customer Code") SELECT   { [Measures].[Planning Customer Code] ,[MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] ,[Measures].[Comments] } ON COLUMNS , FILTER   (( { [Promotion_Subset]} * { [Products_Subset]} * {[Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004]} * {[Time].[Calendar].[Month].&[7]&[2010]}* {[Country].[Code].&[

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

  
Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

T-SQL 2005 table query

  
Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

SQL query optimization

  
I have a stored procedure that is taking a long time to run. I have to optimize it. While testing I found that there is a join that is taking a long time : select t1.col1, t1.col2, count(distinct a.personid) e1, count(distinct b.personid) e2 from table1 t1 ...... join messages m with(nolock) on m.messageid = t1.messageid join messagerecipients mr with(nolock) on m.messageid = mr.messageid  left join people a with(nolock) on mr.personid = emp.personid and emp.persontypeid = 2  left join people b with(nolock) on mr.personid = con.personid  and con.persontypeid = 1   The personid in messagerecipients does not have an index. So the join are taking a long time when I find the count in the select statement. I do not intent to modify the production table for now to apply indexes. Is there a way I can optimize my query   Thanks, Reshmi

Sql Server 2005 ( remove header information from query output file "Job 'bustd_Daily_Query_Output_1

  
Dear All,I have scheduled job for querying output & asve it into particuler folder....but in the top of file i getting the message like "Job 'bustd_Daily_Query_Output_13July09' : Step 1, 'Query1' : Began Executing 2009-07-13 16:32:06"I want to remove this & want output in txt file format without above header....RegardsRavendra Cindia

How to query the Active directory using SQL Server 2005

  
Hi, I am trying to query Active server from Sql Server 2005 and getting error I used following script to create link server EXEC sp_addlinkedserver 'ADSI3', 'Active Directory Services 111', 'ADSDSOObject', 'adsdatasource' and then trying to connect using following statement SELECT   * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=local.mycompany.ca'' WHERE objectCategory=''User'' ') and getting following error An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=local.resound.ca' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Please let me know if you know How to query the Active directory using SQL Server 2005 Thanks, Raj    Dreaming a world without any war in anywhere

SQL 2005 Query Assistance needed

  
Question: I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], ' ' AS [Debit], (CONVERT(varchar, SUM(pvp.totalfee))) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.CompanyI

MS SQL 2000 The query processor ran out of stack space during query optimization.

  
On an insert statement with an image field I get the following error when the data to go into the image field is over a certain size. Lots of special characters. The query processor ran out of stack space during query optimization. Please help on how to fix.

SQL 2005 Query Assistance needed ASAP

  
I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 ($225.00 charge minus $65.00 adjustment). SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], 0.0 AS [Debit], SUM(pvp.totalfee) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.Compa

2005 query runs slow in 2008

  

Hi,

I'm running some of our stored  procedures, written on 2005, on a test 2008 server

One of the stored procedures runs much slower on 2008, the execution plan is considerably different as well.

I've tried changing the compatability level, to no effect.

I'm wondering f there are any general actions I can take to mitigate this issue, or whether I'm just going to have to deal with these problems on a one by one basis.

thanks

 

 


Sean

Converting an Access pivot query to SQL 2005

  

Hi, I am new to table pivot and would like to know how to go about converting the following query in Access to SQL Server 2005:

 

TRANSFORM First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS FirstOfBUGDET_LINE_BURDENED_COST 
SELECT tbl_Proj_Budget.PROJ_ID,
 First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS [Total Of BUGDET_LINE_BURDENED_COST] 
FROM tbl_Proj_Budget 
GROUP BY tbl_Proj_Budget.PROJ_ID 
PIVOT tbl_Proj_Budget.RESOURCE_LIST_ALIAS;


1) what does the 1st line mean?

TRANSFORM First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS FirstOfBUGDET_LINE_BURDENED_COST

2) How would I conver this to TSQL ?

First(tbl_Proj_Budget.BUGDET_LINE_BURDENED_COST) AS [Total Of BUGDET_LINE_BURDENED_COST]

 

Thanks


xml query optimization

  

following query is taking 3 second at my computer. need to optimize the query

declare @TargetResponseXML xml
set @TargetResponseXML = 

'<mmm>
	<prospectsurveyresponses pspt_id="100" vcli_id="321" cmp_id="221">
		<mmm>
				<surveyelements id="1">
		<pages>
			<page seq="1">
				<title>Title1</title>
				<questions>
					 
					<question id="2" seq="2" ismandatory="1" isvertical="0">
						<text>Travel through</text>
						<type>Matrix</type>
						<responseset>
							 
							<targetresponses id="4">
								<response type="matrix" id="4" seq="4" jumpto="2" isselected="0">
									<text>UK</text>
									<value></value>
								</response>
								<response type="matrix" id="5" seq="5" jumpto="3" isselected="0">
									<text>US</text>
									<value></value>
								</response>
								<response type="matrix"

Tracing of query in sql server 2005

  
hi friends,

Is it possible that while a query is running in sql server 2005 but cann't be possible to trace it in profiler or in activity monitor.


Thanks in Advance
Ravi

help with sql 2005 query+ if exists

  

Hi,

I need to write a query that returns history of a carton. I need to check if the carton's status had the status NEW any time. If yes, then I must return the history results from status NEW else return all the carton's history.

This is what i have so far without checking the NEW status

 

SELECT cartonstatus_name as Event ,cartonstatushistory_date as Date
FROM dbo.CartonStatusHistory
inner join Carton on Carton.carton_id=CartonStatusHistory.carton_id
inner join dbo.CartonStatus on CartonStatus.cartonstatus_id=CartonStatusHistory.cartonstatus_id
 WHERE CARTON_ID=@ItemId

 

 

 

 
CREATE TABLE [dbo].[Carton](
	[carton_id] [nvarchar](255) NOT NULL,
	[packlist_id] [nvarchar](30) NULL,
	[businessunit_id] [int] NOT NULL,
	[seasonyear_id] [int] NULL,
	[electronicinvoice_id] [int] NULL,
	[cartonstatus_id] [int] NULL,
	[cartonstatus_date] [smalldatetime] NULL,
	[StatusUser_id] [int] NULL,
 CONSTRAINT [PK_Carton] PRIMARY KEY CLUSTERED 
(
	[carton_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = O

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

select query sql server 2005

  

i have three table

1)    tbl_pages              primary key column is  (intPk_PageID)

2)   tbl_pagecontents   foreign key colum (intPk_PageContentID) reference with tbl_pages

3)   tbl_banners           foreign key colum  (intFk_pageid) reference with tbl_pages


select * from tbl_pages
---------------------------------------------------------------------------------
intPk_PageID   vcr_PageTitle    vcr_PageUrl         int_PageStatus
---------------------------------------------------------------------------------    
1                      Contact Us          contactus.aspx             1
2                      News                   news.aspx       

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