July 13, 2012 at 10:41 am
Hi Everyone,
I have a Stored Procedure which exports a dataset everynight from one vendor's database in a TMU file to our second vendor's database. We have been exporting this data successfull for three years and now the files are becoming too big that it takes lot of time in ftp server. Some times it is timed out. We want to make the size of these file small.
To do that I have to tweak the SP.
The parameter I want to change is @LastUpload to @lastMonth.
I do not want to change something which will crash. So I need some help.
I think if I can change the @Last_upload to say @Last_Month, I will reduce the size of the file.
--and (evt_add_date>=@last_upload or (evt_change_date is not null and evt_change_date>=@last_upload))
Any help is really appreciated.
Thanks
July 13, 2012 at 12:36 pm
Post the code inside the window using the IFCodes please. Many of us don't open attachments. Also, post DDL with sample data so we can help you better.
July 13, 2012 at 12:40 pm
the question is how often do you upload? if its quicker than every month then getting the last month of data would increase the size not decrease it. and not knowing why you need this upload its hard to say if you should look at changing the time frame or breaking it up into seperate files or adjusting the time out on the ftp (although with a continues data transfer how the connection could time out is a question i would ask.)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 13, 2012 at 12:56 pm
For those of you too nervous to open a text file...
CREATE Procedure [dbo].
--@evt_keys nvarchar(4000)=null
@last_export nvarchar(20)=null
as
--select @last_export = null
Create table #temp_keys (Id uniqueidentifier)
--if rtrim(isnull(@evt_keys, ''))=''
--insert into #temp_keys select evt_key from ev_event (nolock) where evt_delete_flag=0
--else
--insert into #temp_keys exec [dbo].[_SelectStringFromString] @evt_keys, 'S'
declare @last_upload datetime
if rtrim(isnull(@last_export, ''))= '' or isdate(@last_export)=0 or convert(datetime, @last_export)>getdate()
-- select @last_upload = getdate()
select @last_upload = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
else
select @last_upload = dateadd(mi, -1, convert(datetime, @last_export))
insert into #temp_keys
select evt_key
from ev_event (nolock)
--join ev_event_ext (nolock) on evt_key_ext=evt_key
--left join ev_event_location (nolock) on evl_evt_key=evt_key and evl_delete_flag=0
--left join ev_location (nolock) on loc_key = evl_loc_key and loc_delete_flag=0
where evt_delete_flag=0 and evt_end_date >= '01/01/2012'
and evt_etc_key <> '59a0986a-c314-43c4-a36b-c33e48d6d107'
and (evt_add_date>=@last_upload or (evt_change_date is not null and evt_change_date>=@last_upload))
insert into #temp_keys
select evt_evt_key
from ev_event (nolock)
--join ev_event_ext (nolock) on evt_key_ext=evt_key
--left join ev_event_location (nolock) on evl_evt_key=evt_key and evl_delete_flag=0
--left join ev_location (nolock) on loc_key = evl_loc_key and loc_delete_flag=0
where evt_evt_key is not null and evt_key in
(select Id from #temp_keys)
--select * from #temp_keys
--return
select distinct
[Activity Code]=evt_code,
[Name]=--evt_title,
replace(replace(replace(evt_title, char(146), char(39)), char(147), char(34)), char(148), char(34)),
[Parent Code]=--evt_evt_key,
(select evt_code from ev_event p1 (nolock) where p1.evt_key=ev_event.evt_evt_key),
[Parent Start Date]=--'Use evt_evt_key to get evt_start_date',
(select convert(nvarchar(10), evt_start_date, 101) from ev_event p2 (nolock) where p2.evt_key=ev_event.evt_evt_key),
[Activity Label]=--evt_title,
(select etp_code from ev_event_type (nolock) where etp_key=ev_event.evt_etp_key),
[Domain Code]='', -- 'aphl.org', -- ???
[Start Date] = convert(datetime,evt_start_date+' '+ rtrim(ltrim(isnull(evt_start_time,'12:00AM')))), --convert(nvarchar(10), evt_start_date, 101),
[End Date] = convert(datetime,evt_end_date+' '+ rtrim(ltrim(isnull(evt_end_time,'11:59PM')))),--convert(nvarchar(10), evt_end_date, 101),
[Delivery Method] = '', --(select etp_code from ev_event_type (nolock) where etp_key=ev_event.evt_etp_key),
[Active Indicator] = '1', -- case when isnull(evt_closed_flag, 0)=0 then '1' else '0' end, -- convert(nvarchar(1), isnull(evt_closed_flag, 0)),
[Open Indicator] = '0', -- case when isnull(evt_closed_flag, 0)=0 then '1' else '0' end, -- convert(nvarchar(1), isnull(evt_closed_flag, 0)),
[Cancel Indicator] = convert(nvarchar(1), isnull(evt_delete_flag, 0)),
[Link Type] = case when evt_evt_key is null then '' else '1' end, --'1',
[Status] = '',
[Vendor] = '',
[Default Payment Term] = '',
[Media Type] = '',-- evt_etp_key,
--(select etp_code from ev_event_type (nolock) where etp_key=ev_event.evt_etp_key),
[Content Type] = '', -- rtrim(case when charindex(',', evt_course_topic_ext)>0 then left(evt_course_topic_ext, charindex(',', evt_course_topic_ext)-1) else evt_course_topic_ext end),
-- evt_course_topic_ext, -- evt_etc_key,
-- (select etc_code from ev_event_category (nolock) where etc_key=ev_event.evt_etc_key),
[Description] = '', -- evt_short_description,
[Cost] = '',
[Hours] = '',
[Credit Hours] = '',
[Cancel Days] = '',
[Passing Grade] = '',
[Instructor Note] = '',
[Employee Note] = '',
[Require Approval] = '',
[Approver] = '',
[Currency] = '',
[Instructor] = '',
[CBT Launch Method] = '',
[CBT Launch Path] = '',
[Max. Attempts] = '',
[Time zone] = case
(select tzn_time_zone from fw_time_zone (nolock) where tzn_key=ev_event.evt_tzn_key)
when '(GMT-05:00) Eastern Time (US & Canada)' then 'America/New York'
when '(GMT-06:00) Central Time (US & Canada)' then 'America/Chicago'
when '(GMT-07:00) Arizona' then 'America/Phoenix'
when '(GMT-07:00) Mountain Time (US & Canada)' then 'America/Denver'
when '(GMT-08:00) Pacific Time (US & Canada), Tijuana' then 'America/Tijuana'
when '(GMT-09:00) Alaska' then 'America/Anchorage'
when '(GMT-10:00) Hawaii' then 'Pacific/Honolulu'
when '(GMT-12:00) International Date Line West' then 'Pacific/Aukland'
else
(select tzn_time_zone from fw_time_zone (nolock) where tzn_key=ev_event.evt_tzn_key)
end
,
[Location] = loc_name, -- 'Loc_name/loc_code From ev_event_location
left join ev_location (nolock) on loc_key = evl_loc_key
where evl_evt_key={evt_key} and evl_delete_flag=0',
[Min Capacity] = '',
[Max Capacity] = evt_capacity,
[Certification Indicator] = '',
[Certification Days] = ''
from ev_event (nolock)
join ev_event_ext (nolock) on evt_key_ext=evt_key
join #temp_keys on Id=evt_key
left join ev_event_location (nolock) on evl_evt_key=evt_key and evl_delete_flag=0
left join ev_location (nolock) on loc_key = evl_loc_key and loc_delete_flag=0
--where evt_delete_flag=0 and evt_end_date >= '07/30/2009'
--and (evt_add_date>=@last_upload or (evt_change_date is not null and evt_change_date>=@last_upload))
drop table #temp_keys
GO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 13, 2012 at 1:01 pm
Now we can all see the code. However as already stated you need to post ddl, sample data and desired output before anybody has a chance at helping.
Why all the NOLOCK hints? They are on every single table. I can't help but think that you are not aware of what this does. It is NOT a magic go fast pill. It is however a magic pill that can produce duplicate or missing data, uncommitted transactions and can even corrupt your entire database (under the right circumstances).
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
One you post the required information for people to help you will find that lots of people will roll up their sleeves and help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2012 at 7:01 pm
Thanks for replying. This SP runs every night as a TMU file. I do not have access to DDL file.
I am not a DBA, I was asked to tweak the SP so that the file is smaller.
To give a little background, we have two databases and these jobs runs every night with all the changes we have in a day. The start date for this job is 1/1/2009. This is making the size of the file big and ftp is timing out.
My manager has asked me to change the it to Last Month so that it has a smaller file size.
I hope this helps.
AS
July 16, 2012 at 7:04 am
amritasehdave (7/15/2012)
Thanks for replying. This SP runs every night as a TMU file. I do not have access to DDL file.I am not a DBA, I was asked to tweak the SP so that the file is smaller.
To give a little background, we have two databases and these jobs runs every night with all the changes we have in a day. The start date for this job is 1/1/2009. This is making the size of the file big and ftp is timing out.
My manager has asked me to change the it to Last Month so that it has a smaller file size.
I hope this helps.
AS
DDL is not a file. It is the create table statements. There is no chance that anybody will be able to help you with this unless you provide enough information for somebody to run some tests. If you are unable to post ddl and sample data then we can't help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 16, 2012 at 7:21 am
Is it just me or does the last bit of code have an extra single quote mark? Or maybe is missing one?
Also, please explain to me how @last_upload is not providing last month's date? The first part of the following code gives me 06/01/2012 as a date, for example, when @last_export is null. Since @last_export is a value passed into the stored procedure, you should just be able to change that value to get the second half of the @last_upload code working properly.
declare @last_upload datetime
if rtrim(isnull(@last_export, ''))= '' or isdate(@last_export)=0
or convert(datetime, @last_export)>getdate()
-- select @last_upload = getdate()
select @last_upload = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
else
select @last_upload = dateadd(mi, -1, convert(datetime, @last_export))
You also have 2 dates hard coded into the proc. You probably want to switch those over as pass-in variables also in order to make the whole thing work as expected.
Beyond that, until you provide further information, we can't help you because we have no context in which to frame your question.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply