Debugger For SQL

Question

Debugger For SQL

Viewing 12 posts - 46 through 56 (of 56 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

macrocharlie Ten Centuries Points: 1055 More actions · Answer 2

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?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Jeff & GSquared:

Did either of you get a chance to perform any additional analysis ?

SQL = Scarcely Qualifies as a Language

macrocharlie Ten Centuries Points: 1055 More actions · Answer 5

I think I later reader that the debugger only works with 2008.

GSquared SSC Guru Points: 260824 More actions · Answer 6

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

GSquared SSC Guru Points: 260824 More actions · Answer 7

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

GSquared SSC Guru Points: 260824 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

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