February 2, 2009 at 1:35 pm
declare @start_pd_date as varchar(10), @end_pd_date as varchar(10);
set @start_pd_date = Convert(varchar(20),'2008-12-01',120)
set @end_pd_date = Convert(varchar(20),'2008-12-31',120)
declare @year as varchar(4)
set @year = '2005'
while @year < '2009'
Begin
declare @querySQL varchar(8000)
declare @queryFROM varchar(2000)
declare @queryWHERE varchar(2000)
Set @querySQL = 'insert into Revenue' + @year + '
select distinct ltrim(rtrim(EmpID))+right(ltrim(rtrim(depno)),3) as RevID,
rtrim(PaRevID) as HCid,
rtrim(clmno) as ClaimNum,
ltrim(rtrim(clmlnno)) as LineNum, ltrim(rtrim(adjseqno)) as Ajudication,
cast(case when isdate(FromDt) = 0 then null else FromDt end as Datetime) as ServcDate
when len(ltrim(rtrim(EmpID))) = 13 and (left(ltrim(rtrim(EmpID)),3) = ''000'' or ltrim(rtrim(EmpID)) like ''[a-z]%'')
then substring(ltrim(rtrim(EmpID)),4,10) + right(ltrim(rtrim(depno)),3)
else ltrim(rtrim(EmpID)) + right(ltrim(rtrim(depno)),3) end as RevID_old,
set @queryFROM = ' from dbo.Stage_Facil as fac left join STATE_LOOKUP_STATE5.dbo.chartfieldlookup as chart on fac.mbu = chart.mbu and
ltrim(rtrim(fac.hmBudgetid)) = chart.hmBudgetid and fac.coPlace = chart.coPlace and fac.leglentcd = chart.legentcd
left join STATE_LOOKUP_STATE5.dbo.RevType_ASO as ft ON fac.fndgPlace = ft.RevChartfield_cd
set @queryWHERE = ' where (clmlnstcd = ''APRVD'') and (year(FromDt) = ' + @year + ') and
(EmpID <> ''unkwn'' and EmpID not like ''000000000%'' and ltrim(rtrim(depno)) <> ''unkwn''
and EmpID not like ''999999999%'' AND EmpID not like ''DO NOT%'')
and ltrim(rtrim(fac.hmBudgetid)) not in (''154'',''158'',''188'',''189'',''194'',''195'',''196'')'
Execute(@querySQL + @queryFROM + @queryWHERE)
set @year = @year + 1
End
It takes nearly 3hrs for me to run this query where i exppect only 20min to run. Is there a way this query can be modifed in more smarter way performing the same job.
February 2, 2009 at 2:04 pm
I think there is a CASE statement missing in the dynamic sql as I have a WHEN but no corresponding CASE.
February 2, 2009 at 2:07 pm
I also have a ',' at the end of the select that will cause an syntax error due to the 'FROM' clause then being added to the dynamic sql.
Either that, or when I did a copy/paste it lost stuff.
February 2, 2009 at 2:14 pm
Change this:
and (year(FromDt) = ' + @year + ')
to this:
and (FromDt >= dateadd(yyyy, cast(' + @year + ' as int) - 1900, 0) and FromDt < dateadd(yyyy, cast(' + @year + ' as int) - 1899, 0))
This will allow the query to use an index on FromDt.
February 2, 2009 at 2:57 pm
Sorry ,The query i have pasted here may be wrong, its a long code doing all repeated stuff like CASE staments just to make it short i cut it down. I dont have any syatax errors I am running this since years.
I am just trying to improve tha performance by the way it is working now. I am looking to do this job in a differnt way which improves performance.
February 2, 2009 at 3:03 pm
Well, without the entire query it is difficult to know what needs to change to improve it.
February 2, 2009 at 6:37 pm
Its he same cast and case statements repeated with no change except the field names. anyways i was just looking for better performance of such dyanamic sql. any idea?
February 2, 2009 at 6:59 pm
Third post was the only idea I could give you without the entire query.
February 3, 2009 at 6:04 am
Can you post the actual execution plan? Simply looking at a slice of the code we may or may not be able to identify an issue, but looking at the execution plan, you can see if you're getting scans, key lookups, etc. People trying to help will also frequently ask for structure & sample data.
BTW, if it's getting slower over time, have you looked to see if your statistics are up to date or the indexes are defragmented?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply