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 2008 Forums
 Transact-SQL (2008)
 Minify SQL source?

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 10:57:12
Anyone know if minifying source for Sprocs makes any difference?

Save space?
Save memory (when being "compiled")?
Improve performance somehow?

(I mean to remove all whitespace / comments etc, and change all local variables names to single-character etc.)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 11:30:28
While I don't *know* the answer, I would assume that it does save space -- 2 bytes per character you remove. The definition column in sys.sql_modules is nvarchar(max). syscomments uses nvarchar(4000) to store the definition.

Also UDFs, Views, Trigggers...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 12:03:11
Removing the whitespace is an easy algorithm, so I might try some tests.

Minifying the @Vars is harder - mustn't change any SProc parameter names (which may be the same as / ambiguous with local variable names).

Hmmm ... another job I've made for myself
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 12:11:56
also watch out for named parameters from your front end apps

think it's going to be worth the effort? i have lots of procs you can optimize if you're bored lol
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 12:21:54
Minifying??????


In any case, why would you think so? Don't you think the compiled code isn't compressed to machine code?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 12:26:11
it isn't. Views, SPs etc are not compiled into machine code the way C++ is.

it's stored as is and then run through the SQL interpreter (query engine)

there's good reason to suppose she can save storage this way, but i doubt the result is worth the effort.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 12:39:06
She????

EDIT: I like "her" quote....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 12:46:16
oops.

sorry Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 13:00:08
No worries.

I'm not sure that the effort is in vain.

We change Sprocs in DEV etc. as-and-when.

We then make a single script (of all Sproc, Trigger and View definition changes) in order to make an upgrade release-package.

So minifying that upgrade-script would be a simple step in the release process.

Back in the '70s I wrote Basic interpreters in Assembly language. Those interpreters spent a lot of time stepping over, and ignoring, white space and comments whilst parsing the language. Unless that is done as a one-time action (either compile to machine code, or compile to some sort of P-code) then parsing white-space will waste time every time it happens.

How often does it happen? When a query-plan is made does that mean that the original Sproc source can be ignored until the cached query plan is lost from the cache? or is the original source used/parsed, to some extent, every time the Sproc is executed?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 13:03:32
P.S. People told me that compressing JS, HTML, CSS was pointless; I ignored them and have been doing that on all our web code for 9 years now. Now its become the Current Big Thing to improve your web site performance <sigh!>

SQL Source too, maybe??
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 13:26:20
As a production DBA, I would have a problem with developers making code changes like this. I seriously doubt you'll get more than a few microseconds boost out of it unless you've got some crazy code formatting going on. What happens when a production DBA needs to peek inside stored procedure code to help identity a critical production issue? White space and properly named local variables help out when eye-balling code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 13:34:48
I agree with that, but all our SProcs are encrypted -= specifically so that well-meaning DEVs don't RightClick-Edit-Save (missing the Revision Control System Check-in, QA steps etc etc.)

I'm sure in your shop you have training in place etc. that would prevent that, and if one of my guys did it there would be a public execution , but my clients' people do all sorts of "well meaning things" (without getting shot )

So because already Encrypted if they are also De-commented that makes no odds to me for Debug Diagnosis.

The performance increase in [minified] HTML / JS / CSS is significant - they are basically once-parse system - so my reckoning is that if SQL is re-parsing a lot there will be a measurable saving.

But having said that:

a) I need to test it and prove that there is a performance improvement that is worth having
b) If it was that worthwhile an improvement Microsoft / someone would have done it by now.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-01-19 : 20:12:08
quote:
The performance increase in [minified] HTML / JS / CSS is significant
Sure this isn't just due to reduced download size? Also, you can't compare browser memory management to SQL Server memory management.
quote:
they are basically once-parse system - so my reckoning is that if SQL is re-parsing a lot there will be a measurable saving.
This is only true if your sprocs are frequently recompiling, which is a bigger performance issue IMHO and one which minifying won't solve.

I agree that minifying would reduce your overall deployment package, but other than trimming whitespace I wouldn't do any other minifying techniques, and I don't think it will improve performance on anything.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-01-19 : 20:58:53
HTML / JS / CSS speed up is due to rediced download size.

as for your sprocs the only benefit you might get with minifying is putting more plans in the cache because plans are generated ba text.
but for this to be noticeable there should be a lot of white space in there!

I'll have to play!

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.7 out!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 22:14:45
Minify?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 22:53:10
quote:
Originally posted by X002548

Minify?



Quote from the original post:
quote:

(I mean to remove all whitespace / comments etc, and change all local variables names to single-character etc.)



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 23:47:23
No, I get it...but is that really aq word...I mean, I like to make up words just like the next guy...but Minify?

Minimize perhaps?

In any case...I still think it's a non issue....

BUT!!!!

Does it rain in Southern California?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-20 : 01:02:43
I have no idea if it rains in southern Cal, but I can say with a reasonable amount of certainty that it rains from late novemeber through the end of February in Northern California practically non-stop!

I was smart though. I moved away from the bay area to Ohio where it snows from November through April
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-20 : 02:21:24
quote:
Originally posted by X002548

Minify?


I know, ghastly isn't it.

We used to refer to the process as compacting the file. That was removal of spaces and comments. Since the addition of shortening local variable names, and sometimes some syntax rearrangements (removing unnecessary BEGIN/END on single statement blocks, for example) come bright spark has come up with the term "Minify".

One thing I find slightly ironic:

If you look at the big JavaScript libraries (e.g. JQuery) they have a file like JQuery.1.2.3.js - version 1.2.3 of course. That is the full fat, developers debugging version.

And then JQuery-MIN.1.2.3.JS is the "minified" version.

First, as an ardent byte-saver myself, it tickles me that they add 4 bytes to the filename (which appears in the HTML source every time you view a page) - you think I'm splitting hairs? We name our images folder "i" and not "images", it does mount up at millions of page views per day)

Secondly, most DEVs download the "normally named" file, stick that in their code, and leave it at that. So I reckon the Minifed version should be JQuery.1.2.3.JS and the Full Fat version JQuery-DEV.1.2.3.JS

But they made a grave error early on ... by not putting me in charge
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-20 : 19:50:40
quote:
Originally posted by X002548


Does it rain in Southern California?



Not very often, but holy crap is it raining this week. We've had one death yesterday in SD County where a 10 foot wide tree landed on a home and crushed the homeowner. Mud slides are causing evacuations. I sure hope our brand new vinyl fence survives; our neighbor's fence blew over and it was only a year old. My parents have already spent $600 on house repairs due to the wind and rain. We're hoping for better luck!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-01-20 : 22:26:26
I think the risk of introducing a bug during the "Minification" process would far outweigh any possible benefit.





CODO ERGO SUM
Go to Top of Page
    Next Page

- Advertisement -