August 20, 2008 at 7:42 pm
Carl Federl (8/20/2008)
In reply to Jeff Moden's question: Under the "union all" solution, there are no "parameter", just SQL Statements that are sent to the server in the normal manner as a command batch.
Now that's a horse of a different color. Thanks for the feedback, Carl. Man, it's been a thousand years since I've written anything like that... I gotta sharpen my pencil... maybe even steal a bit of your code to run my own test, Carl. Thanks again...
--Jeff Moden
August 21, 2008 at 7:34 am
Does anybody know if the debugger in 2008 Management Studio works on SQL 2005? Also can you get 2008 Management Studio without buying the whole SQL 2008 package?
August 21, 2008 at 8:13 am
AFAIK the answer to both is no. But I am not declaring this to be the definitive answer.
I do think that the Dev edition of sql server is pretty cheap tho.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 2, 2008 at 6:38 am
Jeff & GSquared:
Did either of you get a chance to perform any additional analysis ?
SQL = Scarcely Qualifies as a Language
September 2, 2008 at 6:52 am
I think I later reader that the debugger only works with 2008.
September 2, 2008 at 8:31 am
I finally got a chance to run a speed test on a proc that does it's own string-split, without calling a UDF.
Same test as before:
create proc [dbo].[ParamsTest3]
(@DateList_in varchar(max))
as
set nocount on;
declare @Date datetime, @XML XML;
select @XML = ''' ';
;with Dates (DateIn) as
(select result.value('(i/@date)[1]','datetime')
from
(select t.c.query('.') as Result
from @xml.nodes('i') t(c)) a)
select @date = date
from dbo.calendar
inner join Dates
on date = datein;
go
create table #T (
Start datetime,
Finish datetime,
Prc varchar(100));
go
set nocount on
insert into #T (Start, Prc)
select getdate(), 1
go
exec dbo.paramstest3
'1/1/2000|2/1/2000|3/1/2000|4/1/2000|5/1/2000|6/1/2000|7/1/2000|8/1/2000|9/1/2000|10/1/2000|11/1/2000|12/1/2000';
go 1000
update #T
set finish = getdate()
where finish is null;
Result: 1483 milliseconds, which is almost twice as fast as the version with a UDF.
Edit: Made the XML visible by replacing the carrets with parens. To make it work, it has to be returned to carrets (triangle-braces).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 8:42 am
By going one step further, and setting the proc to take an XML input parameter:
create proc [dbo].[ParamsTest4]
(@DateList_in XML)
as
set nocount on;
declare @Date datetime;
;with Dates (DateIn) as
(select result.value('(i/@date)[1]','datetime')
from
(select t.c.query('.') as Result
from @DateList_in.nodes('i') t(c)) a)
select @date = date
from dbo.calendar
inner join Dates
on date = datein;
Same test, 1376 milliseconds for 1000 executions. About a tenth of a second faster than the string conversion.
Either is better than ten times faster than the temp table and the union insert.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 9:07 am
To simulate multiple users, I scheduled 10 jobs which ran the proc 1000 times each, all running at the same time. Run time averaged 46 milliseconds for each job. Of course, without variation in the parameters, I'm running purely in the cache at that point, but concurrent users didn't seem to harm the performance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2008 at 7:58 pm
Carl Federl (9/2/2008)
Jeff & GSquared:Did either of you get a chance to perform any additional analysis ?
No, I sure haven't... my apologies. Have a new job with 1:10+ commute time each way... hasn't left much time for anything but short answers with not much analysis.
--Jeff Moden
September 3, 2008 at 7:38 am
Jeff Moden (9/2/2008)
Carl Federl (9/2/2008)
Jeff & GSquared:Did either of you get a chance to perform any additional analysis ?
No, I sure haven't... my apologies. Have a new job with 1:10+ commute time each way... hasn't left much time for anything but short answers with not much analysis.
Bummer your commute isn't by public transport so you could do stuff on a laptop while enduring the ride!! Hope you love the job! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 3, 2008 at 5:12 pm
TheSQLGuru (9/3/2008)
Jeff Moden (9/2/2008)
Carl Federl (9/2/2008)
Jeff & GSquared:Did either of you get a chance to perform any additional analysis ?
No, I sure haven't... my apologies. Have a new job with 1:10+ commute time each way... hasn't left much time for anything but short answers with not much analysis.
Bummer your commute isn't by public transport so you could do stuff on a laptop while enduring the ride!! Hope you love the job! 🙂
Heh... thanks Kevin... 4 way win for me... I love the job, I love the people I work for, I love the people I have to work with, and I love the pay increase. Since I'm driving in the opposite direction of traffic (most of the time), it's a non-stressful drive... I turn off the phone, turn on my favorite oldies station, hit cruise control, and relax/think.
--Jeff Moden
September 4, 2008 at 8:06 am
WOW! Doesn't get much better than that! You even get to take advantage of the long drive for some "me time"! Congrats.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply