August 10, 2005 at 9:57 am
I understand what most of this query is doing. The part i dont understand is the "{ oj...}. I also noticed the query runs significantly longer the first time. I believe this has to do with the overhead of parallelism execution. Does making this a stored procedure reduce how frequently sql server recreates the execution path?
SELECT DISTINCT
XVYReportType.xvyrtCode, XVYReportType.xvyrtName, XVYReportType.xvyrtValXName, XVYReportType.xvyrtValYName, XVYReportType.xvyrtPTDName,
XVY.xvyroBudgetYear, XVY.xvyroMonthNumber, XVY.xvyroMonthName, XVY.xvyroReportPeriod, XVY.xvyroLevel1Account, XVY.xvyroLevel1Name, XVY.xvyroLevel2Account, XVY.xvyroLevel2Name, XVY.xvyroLevel3Account, XVY.xvyroLevel3Name, XVY.xvyroLevelNumber, XVY.xvyroOrd, XVY.xvyroReportType, XVY.xvyroReportControl, XVY.xvyroSubAccount, XVY.xvyroDescrip, XVY.ValXAmount, XVY.ValXPercent, XVY.ValYAmount, XVY.ValYPercent, XVY.Variance, XVY.ValXAmountPTD, XVY.ValXPercentPTD, XVY.ValYAmountPTD, XVY.ValYPercentPTD, XVY.VariancePTD, XVY.xvyroBold, XVY.xvyroRowFormat, XVY.xvyroSuppressPercent
FROM
{ oj myDatabase.dbo.tblBudgetPlanningXVYReportType XVYReportType INNER JOIN Mydatabase.dbo.tblBudgetPlanningXVYReportOutput XVY ON
XVYReportType.xvyrtID = XVY.xvyroRTID} WHERE
XVYReportType.xvyrtCode = 'BVA' AND
XVY.xvyroMonthNumber = 1 AND
XVY.xvyroBudgetYear = 2005 AND
XVY.xvyroReportControl = 'Short1' AND
XVY.xvyroLevelNumber <= 3 AND
XVY.xvyroReportType = 'InternalSummary'
August 10, 2005 at 11:36 am
This is ODBC Outer Join Escape Sequence http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcouter_join_escape_sequence.asp
change your code like below and try if it works:
"...FROM
myDatabase.dbo.tblBudgetPlanningXVYReportType XVYReportType INNER JOIN Mydatabase.dbo.tblBudgetPlanningXVYReportOutput XVY ON
XVYReportType.xvyrtID = XVY.xvyroRTID WHERE..."
Best Regards,
Vlad
August 10, 2005 at 11:53 am
Removing the odbc outer join escape sequence worked, but didnt improve query performance. I guess i need to find a way to optimize this query or reduce the number of times SQL Server recreates the execution plan.
August 10, 2005 at 12:19 pm
If you want some tips how to optimize your query, you should post result of this script here.
set showplan_text on
go
SELECT ... (your select)
go
set showplan_text off
go
OR You can do it yourself. The main point - you should play with indexes.
All Index Scan in result of showplan_text should be replaced with Index Seek
Best Regards,
Vlad
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply