March 28, 2006 at 2:00 am
Hi Everybody,
I am posting this code as per the issue with RS 2005
I do have an query which takes 12 minutes in sql query analyser
But when i write this query replacing with parametres it is taking above fifteen minutes, what i want now where is the query getting late. i want to see the query which is sent by RS 2005 to sql query analyser.
And after that i need to ask for a method where i improve the performance of the query, I am using left join
Have a look on this query , Thank you
Select AreaID as AREA, Unit Unit,reg Register, occured Occured,order_detail Order#,ename Employee,
pregross as Gross,PctDisc Discount, tax Tax, net Net, refundday as Business_Day
from (select refd.BusinessDay as RefundDay ,
refd.unitnumber as unit ,
refd.ParSalesHdrId as HdrId ,
refd.Amounttendertime as occured ,
refd.Registernumber as reg ,
refd.posordernumber as order_detail ,
refd.posemployeename as ename ,
refd.prediscountedordertotal as preGross ,
refd.percentdiscounttotal as PctDisc ,
refd.ordertaxtotal as tax ,
refd.grossordertotal as net ,
refd.ordertype as OTyp ,
refd.overrefundflag as Ref ,
sale.Amounttendertime as Orig_Sale_Date,
sale.grossordertotal as Original_Sales_Amt,
sale.posemployeename as Orig_Sales_Person,
refund_after = DATEDIFF (mi, sale.Amounttendertime, refd.Amounttendertime )
from parsaleshdr as refd
left join parsaleshdr as sale
on refd.unitnumber = sale.unitnumber
and refd.grossordertotal = sale.grossordertotal * -1
and cast(convert(varchar,sale.BusinessDay,101) as datetime)
>= cast(convert(varchar,DATEADD ( d, -1*@Noofdays, refd.BusinessDay ),101) as datetime) where refd.grossordertotal < -18.00
and Ltrim(Rtrim(refd.overrefundflag)) ='R'
and cast(convert(varchar, refd.BusinessDay,101) as datetime)
<=cast(convert(varchar,@Todate ,101) as datetime)
and cast(convert(varchar, refd.BusinessDay,101) as datetime)
>=cast(convert(varchar,@Fromdate ,101) as datetime)
and sale.unitnumber IS NULL)
Temp join wbhierarchy wb on Temp.unit = wb.unitid
order by wb.areaid, Temp.unit
March 28, 2006 at 8:07 am
I would start by running your query through the Execution Plan in Query Analyzer and see where your bottlenecks are. Then, I would definitely rewrite your query.
March 28, 2006 at 9:40 pm
Actually The Prority of importance with rewriting the query is low, I mean i don't need the query to be re written now , even though it is an requirement. what i need is where is the query getting late as it is taking 12 min in Query analyser and 15 min in reporting Services 2005. So what i want is How can i track the query which is sent by the Rs 2005 to query analyser. The priority of importance of tracking the query is high
Thank you
Raj Deep.A
March 29, 2006 at 5:56 am
Hi
I don't know if this is an obvious question but have you taken the recommendations for indexing from query analzyer? If your not going to rewrite at this time query analyzer should have told you about where your getting your full table scans and where you should place your indexes to speed things up. I don't know if your dealing with a lot of data but I would look more at the query analzyer and see what it is recommending for changes.
shelly
March 29, 2006 at 6:10 am
Raj, when you say it takes 15 minutes in RS, do you mean it takes 15 minutes to run the query in the Data tab or when you run the report?
If it's running the report, I would guess the extra time comes from the process of actually rendering the report. If you are in the data tab, how are you connecting to the data source? Assuming the data is SQL2005, are you using sqlclient or oledb? SQLClient is faster.
I think queries in RS get recompiled each time you run them, which will add a bit of an overhead - the answer is to turn it into a stored procedure, which is best practice for RS.
Set up a trace in SQL if you want to trap the actual query from RS and compare it.
Also in query analyser are you specifying actual parameter variables or are you hard-coding the values in. This could mean a little more overhead.
Regards
Chris McGuigan
March 29, 2006 at 8:39 am
Could be several things however how long does this by itself take and how many rows are returned.
(Note: I remove unreferenced columns from the subquery)
select
refd.BusinessDay as RefundDay ,
refd.unitnumber as unit ,
refd.Amounttendertime as occured ,
refd.Registernumber as reg ,
refd.posordernumber as order_detail ,
refd.posemployeename as ename ,
refd.prediscountedordertotal as preGross ,
refd.percentdiscounttotal as PctDisc ,
refd.ordertaxtotal as tax ,
refd.grossordertotal as net
from
parsaleshdr as refd
left join
parsaleshdr as sale
on
refd.unitnumber = sale.unitnumber
and refd.grossordertotal = sale.grossordertotal * -1
and cast(convert(varchar,sale.BusinessDay,101) as datetime) >= cast(convert(varchar,DATEADD ( d, -1 * @Noofdays, refd.BusinessDay ),101) as datetime)
where
refd.grossordertotal < -18.00
and Ltrim(Rtrim(refd.overrefundflag)) ='R'
and (cast(convert(varchar, refd.BusinessDay,101) as datetime) BETWEEN cast(convert(varchar,@Todate ,101) as datetime) AND cast(convert(varchar,@Fromdate ,101) as datetime))
and sale.unitnumber IS NULL
This information will help me understand if it is the subquery eating all the time or the join after.
March 31, 2006 at 1:28 am
Your Suggestions are valuable, Thank you
Raj Deep.A
April 2, 2006 at 7:33 pm
The difference could just be SQL Server caching the query plan which makes the query execute faster. Parameters entered at run time and the time the plan stays in the cache can all effect this.
To be honest 12mins to run a report seems pretty exessive to me, whats an extra 3 mins...
I'd say you should definately re-write your query to execute quicker, and /or try a stored procedure. And if that doesn't work maybe pre-build the data in a denormalised table and just query it direct.
enjoy...
Martin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply