optimzing a 100 line script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

optimzing a 100 line script

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-05 : 21:21:26

when we have a long script what should we put in begining and end of the script.
like if we have many instructions: create database, 5 tables, doing a long search in the stored procedures of the instance, dispalying all the logins, users and databases

all this in the same script what should we do : putting begin end or something to optimize it.

also :
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOshould we put them all over the script like when ever i create a table or just once in the script. what do they serve pls?

Thanks a lot.

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 02:25:06

"to optimize it"

You optimise each query separately.

A CREATE DATABASE is unlikely to be able to be improved - it will take as long as it takes ... I suppose you could reduce the initial size of the database to make it faster, but then the extension of it will fragment the database, so ... no point.

"doing a long search"

You can try to optimise that in the normal way - check the query plan, review whether the expected indexes are being used, consider adding new indexes to improve the performance, consider constructing the query differently, etc.

"putting begin end or something to optimize it"

No, adding BEGIN and END won't optimise the query!

Read up on QUOTED_IDENTIFIER and ANSI_NULLS in BoL (i.e. and other modifiers like them) and decide what you need them set to, and what their scope is (i.e. whether they should be used before an object/procedure is created, or whether they should be used WITHIN procedures. Some effect the way, and state, an object when is created, others can change the state when an object is running.

"what do they serve"

They ensure that the objects are created in the same state when you run the script.

So, if you take your script to a different server, which has different settings, they will cause you objects to be created the same way each time the script is run.

If you will only ever run your script on your own server, and you are happy with the default setting on your server, then you probably don't need them at all.

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 14:17:07

put /* at the beginning of your script, and */ at the end.

then it will run really fast.

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-06 : 16:13:59

yeah man i can t believe it it did it so fast even no data amazing:)
life s much easier this wife every thing is error free even :)

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 16:18:03

You fibber rtutus - you didn't even try it!!!!!

All those GO statements are going to stop that neat little optimisation working

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 17:21:31

huh?

/* */ will comment out everything inside, including GOs. at least they do in SSMS. BatchParser.dll is a decent parser, it's clever enough to know that.

Maybe it's different in QA, but I'd be surprised if it was.

/*
select 1
go
select 2
go
*/

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 02:49:30

"at least they do in SSMS"

May be a SQL 2000 / QA thing then, it would be nice for a GO on a line by itself NOT to be interpreted as a "GO" if it was otherwise enclosed:

SQL2000:

Server: Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

-----------
2

(1 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '*'.

Also:

select 'xxx
go'

is fine, but GO on its own isn't:

select 'xxx
go
YYY'

treats the GO as a terminator and gives:

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'xxx
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'xxx
'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '

'.

so we currently achieve this with syntax like:

select 'xxx' + CHAR(13) + CHAR(10) + 'go
YYY'

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 03:30:00

wow, that pretty much sucks. clearly the batchparser was improved for 2005. your example of

select 'xxx
go
YYY'

works fine in ssms.

www.elsasoft.org

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-07 : 03:57:16

u were right kristen, i didn t try it, i took it for granted i thought jezemine was joking, he was actually, right?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 04:18:09

uh, yes.

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 11:08:52

[code]
IF @VersionYouAreUsing < 2005
BEGIN
uh, No

END
ELSE
BEGIN
uh, Yes [:{]
END
[/code]
Kristen

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-07 : 11:29:18

don t tell me there is no command to get the current runing version of sql : (whetrher 2k or 2005 :)

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 12:02:11

I think we better stop making jokes.

you can use one of these two:

-- returns a human readable string, not that useful for programatic use
select @@version

-- returns a DWORD where highest byte is the version,
-- rest of the bytes are minor versions, service packs, etc.
select cast(@@microsoftversion as varbinary)

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources