| 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... |
 |
|
|
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  |
|
|
|
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  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-19 : 12:46:16
|
oops.   sorry Kristen |
|
|
|
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? |
|
|
|
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?? |
|
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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." |
|
|
|
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 havingb) If it was that worthwhile an improvement Microsoft / someone would have done it by now. |
|
|
|
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. |
|
|
|
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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.7 out! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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   |
|
|
|
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.JSBut they made a grave error early on ... by not putting me in charge |
|
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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." |
|
|
|
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 |
|
|
|
Next Page
|
Minify SQL source?