July 15, 2009 at 7:50 am
In 2000, it's a pain and you can't really do it unless you post a text plan. In 2005/2008, it's easy. Just right click on the execution plan, assuming it's a gui, and select "Save Execution Plan As..." Save it as a .sqlplan file (which is basically xml). Zip that up and attach it to a post.
I've got a video over at Jumpstart TV that shows how.
"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
July 15, 2009 at 8:12 am
While you are figuring out how to post the execution plans, please post the DDL for the table(s) and your code. That will give us something to start with at least.
July 15, 2009 at 12:29 pm
Execution plans attached.
July 15, 2009 at 12:30 pm
Unfortunately, my NDA doesn't allow me to publish the table DDL;(
July 15, 2009 at 12:36 pm
Query used:
select
m.first_name, m.last_name, gh.*, pro.*
from enterprise.dbo.members m
inner join enterprise.dbo.group_header gh
on gh.group_num = m.group_num
left join dim_programs pro
on pro.program_code = m.program_code
where
m.group_num in ('SFVA00', 'SFA000', 'SFPA00', 'SFN000')
and date_effective = '1/1/2006' or date_end is null)
July 15, 2009 at 12:45 pm
J.D. Gonzalez (7/15/2009)
Execution plans attached.
I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them to open as is.
"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
July 15, 2009 at 1:19 pm
J.D. Gonzalez (7/15/2009)
Query used:
select
m.first_name, m.last_name, gh.*, pro.*
from enterprise.dbo.members m
inner join enterprise.dbo.group_header gh
on gh.group_num = m.group_num
left join dim_programs pro
on pro.program_code = m.program_code
where
m.group_num in ('SFVA00', 'SFA000', 'SFPA00', 'SFN000')
and date_effective = '1/1/2006' or date_end is null)
Okay, looking at this can you verify the data type for your column date_effective is actually a datetime? The variable declared as @mydate - is it declared as a datetime?
Can you try the following:
declare @mydate datetime;
set @mydate = dateadd(month, datediff(month, 0, getdate()), -1);
select
m.first_name, m.last_name, gh.*, pro.*
from enterprise.dbo.members m
inner join enterprise.dbo.group_header gh
on gh.group_num = m.group_num
left join dim_programs pro
on pro.program_code = m.program_code
where
m.group_num in ('SFVA00', 'SFA000', 'SFPA00', 'SFN000')
and date_effective = '1/1/2006' or date_end is null)
This is inclusive and will include dates for today where the time is midnight (00:00:00.000), but it will not include anything where the time is greater.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 2:21 pm
date_effective is set up as a datetime.
Just for clarification, hard coding the date or using a parameter seems to be very efficient. It's when I use the dateadd function that it runs much slower.
July 15, 2009 at 2:32 pm
J.D. Gonzalez (7/15/2009)
date_effective is set up as a datetime.Just for clarification, hard coding the date or using a parameter seems to be very efficient. It's when I use the dateadd function that it runs much slower.
When using a variable - how are you setting the variable? Are you using the dateadd function to set the variable or just hardcoding that?
If you can zip those execution plans up so we can see them - it should help us see what is happening.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 3:06 pm
There are two scenarios:
Slow to run.
date_effective <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
Fast to run.
date_effective <= cast(cast(month(getdate()) as varchar(2)) + '/' + '01' + '/' + cast(year(getdate()) as varchar(4)) as datetime)-1 --Fast to run
The rest of the query stays the same. I'm attaching the .sqlplan again. You should be able to open them using SSMS.
select
m.first_name, m.last_name, gh.*, pro.*
from enterprise.dbo.members m
inner join enterprise.dbo.group_header gh
on gh.group_num = m.group_num
left join dim_programs pro
on pro.program_code = m.program_code
where
m.group_num in ('SFVA00', 'SFA000', 'SFPA00', 'SFN000')
and date_effective = '1/1/2006' or date_end is null)
July 15, 2009 at 3:14 pm
I am not sure that I understand what you are after.
You said that you have identified that fastest form of the query already, so what is it you need?
July 15, 2009 at 3:54 pm
You have to zip the execution plans up - we can't do anything with them when they have the .sqlplan extension.
Edit: Nevermind - I was able to download them as is.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 9:51 pm
Grant Fritchey (7/15/2009)
J.D. Gonzalez (7/15/2009)
Execution plans attached.I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them to open as is.
They're a new feature as of today... you have to save the bloody things locally and then open them.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 5:35 am
Jeff Moden (7/15/2009)
Grant Fritchey (7/15/2009)
J.D. Gonzalez (7/15/2009)
Execution plans attached.I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them to open as is.
They're a new feature as of today... you have to save the bloody things locally and then open them.
Help the stupid guy (meaning me), how? If I right click on them, I don't get save as. If I click on it directly it opens a new explorer window, with an aspx extension and an error message about the encoding.
"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
July 16, 2009 at 6:53 am
Grant Fritchey (7/16/2009)
Jeff Moden (7/15/2009)
Grant Fritchey (7/15/2009)
J.D. Gonzalez (7/15/2009)
Execution plans attached.I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them to open as is.
They're a new feature as of today... you have to save the bloody things locally and then open them.
Help the stupid guy (meaning me), how? If I right click on them, I don't get save as. If I click on it directly it opens a new explorer window, with an aspx extension and an error message about the encoding.
I'm not sure what the problem there is, Grant. I'm using IE and when I right click on one, the expected popup menu with a "Save Target As" as one of the selections appears.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply