Viewing 15 posts - 1 through 15 (of 16 total)
Here is some sample data for what I am trying to do.
--Create temp table for trees
create table #trees (ID int Identity(1,1), Parentid int NULL, TreeName varchar(20) NOT NULL,RootID int)
--Create tempo...
May 16, 2011 at 3:06 pm
Thanks for all the input. I think I figured it out. If you have better suggestions, I'm open but I believe it is working.
declare @dt smalldatetime,@day tinyint,@mnth tinyint,@currentyear...
May 11, 2011 at 7:21 am
mistake in the example. 09/07/2010 should be 10/07/2010
and 11/07/2010 should be 01/07/2011
May 10, 2011 at 3:43 pm
Thanks again for your help with this. There's one piece I left out
there's actually 2 input dates.
Declare @StaticDate datetime, @Today datetime
Set @StaticDate = '04/07/2010'
Set @today = getdate()
So the 3...
May 10, 2011 at 3:21 pm
Thanks, that is the basis of what I'm trying to do.
Expanding on your example, if today is 08/05/2011 I would expect a targetdate =09/07/2011
on every 3rd month, the date should...
May 10, 2011 at 2:51 pm
Great discussion here.
I'm having a similar dilemma. We are building a social component to our website and the requirements are that customers and or external application would be able...
August 17, 2009 at 10:04 am
My appologies...
Here's the table structure and current indexes.
CREATE TABLE [dbo].[BookAccess](
[BookAccessID] [uniqueidentifier] NOT NULL,
[SessionID] [uniqueidentifier] NOT NULL,
[UserID] [uniqueidentifier] NOT NULL,
[BookID] [int] NOT NULL,
[ChunkID] [int] NOT NULL,
[Time] [datetime] NOT NULL,
[Type] [int] NOT...
August 21, 2008 at 7:57 am
Here's the basis of the SP. Partioned on YearMonth (YYYYMM). Clustered index on (YearMonth,Time)
create stored proc test @startdate datetime, @enddate datetime
as
declare @start varchar(20), @end varchar(20)
set @start = ...
August 21, 2008 at 6:32 am
I jumped the gun a bit.
The dynamic sql works fine when I'm only accessing 1 partition.
when expand the date range to include multiple partitions, it does a full scan again.
partioned...
August 20, 2008 at 2:35 pm
Thanks for all your help.
Using Dynamic SQL works fine but a pain to recode.
August 20, 2008 at 9:50 am
Yes, it is partitioned on yearmonth YYYYMM
Time is datetime datatype
August 19, 2008 at 1:34 pm
If it's parameter sniffing, then how come if I change the condition in the query to this...
"where yearmonth = @fnstart
and time between @start and @end"
this works fine
or
"where yearmonth between @fnstart...
August 19, 2008 at 1:23 pm
Here's the execution plan when I add (maxdop 1)
insert into #download select userid, bookid, count(*), type from bookaccess b with(nolock) where ...
August 19, 2008 at 1:00 pm
Here's the execution plan when I run the SP as suggested with assigning local variables for the between.
declare @fnstart int, @fnend int;
set @fnstart = dbo.fn_yearmonth(@start);
set @fnstart = dbo.fn_yearmonth(@end);
select *
from table2
where...
August 19, 2008 at 6:29 am
When this runs in management studio as a query, it runs fine. As soon as I create it as a stored proc, is when it hangs.
The plan for the...
August 18, 2008 at 11:49 am
Viewing 15 posts - 1 through 15 (of 16 total)
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy