| Author |
 Topic  |
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 01/21/2010 : 02:19:45
|
Well, lets assume that there is a measurable improvement in speed (I haven't had a chance to try it as yet)
Now you just have to include that in your DEV cycle, like any other tool you use and "test against" (including SQL server itself!).
For us, in HTML / JS etc., we minify at the deployment from DEV to QA - i.e. whatever went to QA is what goes to PRODUCTION. I reckon the same approach would be safe in SQL too.
I've found a couple of things that remove comments in SQL, nothing that does more aggressive compaction (shortens variable names, removes unnecessary BEGIN/END / parenthesis, "AS", "OUTER" and other pleasantries in the language). |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/21/2010 : 09:58:56
|
I think I would wait till Microsoft offers built-in "Minification" as a feature.
I can see that there might be some benefit in a web app where the code is being sent over the web to the client, but I can't see how there would be much benefit in a SQL Server app where a stored proc is only occasionally re-compiled.
CODO ERGO SUM |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 10:18:57
|
"where a stored proc is only occasionally re-compiled."
I agree, but I have no idea what SQL does when I execute an SProc.
If the query plan is cached does SQL, in effect, have some "compiled" (in the loosest sense of the word) code that it runs, or does it have to do something, every time, with the original source? |
|
|
|
russell
Pyro-ma-ni-yak
USA
4993 Posts |
Posted - 01/21/2010 : 10:49:15
|
| I am 99% sure that the identifier for the execution plan is used and the proc is not read. unless of course it is recompiled. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/21/2010 : 11:41:15
|
| That's good enough for me; thanks. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/12/2011 : 04:53:16
|
Decided to revisit this. I've knocked up a little program to strip out extraneous spaces and so on ... we have just done a rollout, so I have a 9MB script of "updates" to Sprocs etc. (which has compressed by 50%). Just executing it was in half the time, 5 minutes instead of 10, which is a handy time saving when rolling out onto a live server as we have to schedule downtime.
Next issue: how to test if any improvement in performance. We have a regression test I can run on our QA system. Sadly the end-to-end time on that is no use, as our regression test pauses on each web page to ensure it is fully rendered before checking the page for specific content.
But I wondered if I could check our logs for the elapsed time for each SProc call (should!! be identical sequence of events on each QA run) and see how they compare. I don't have a dedicated server for QA, so it will be have to be on shared machine, but hopefully if there is any significant difference it would show up. The QA test is mostly to do with regression testing, so coverage of the system rather than load testing, as such there will be many Sprocs that are reused frequently, and plenty that are probably only executed once (those required for specific tasks).
We do have a load test, but that will have to be on a staging site, which will require testing of the compressed code through a QA cycle before I can deploy to STAGING ... so a quick method of seeing if this is faster, or not, would help - otherwise I'll park it until we next do a rollout
I'm open to any other ideas for testing performance Before/After |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/12/2011 : 08:41:25
|
The algebrizer seeks out names and aggregates in your query and find the object_id associated with it.
So there is absolutely no performance gain in using shorter names for variables.
The query parse tree is stored binary including the objects used, so metadata like comments is not there anyway.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 09/12/2011 : 09:05:00
|
This is one of the more disturbing threads I've seen.
I'd may actually shed real tears when my beautiful readable code becomes minified. Especially because my style is pretty verbose.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/12/2011 : 09:20:58
|
"The query parse tree is stored binary including the objects used, so metadata like comments is not there anyway"
Yup, I was expecting that. But ... how often does it go back to the raw source? Is this stuff just cached in RAM, or is it built ("compiled") into Binary when you do CREATE SPROC ?
If its in RAM then it will get flushed at some time and have to be rebuilt. Maybe if you use RECOMPILE on the Sproc (or parameter sniffing or somesuch decides that should happen) that will happen often?
I don't know the answer to those questions, but if you do it will save me time because if everything is straight-to-binary the moment it is created I can soft-pedal this project.
Although I think there may be some benefit in obfuscating the code. We ENCRYPT our Sprocs on Live systems, to make life harder for a hacker (or a "well meaning" junior DBA <sigh>), but the encryption is trivial to reverse (I think RedGate even provide a one-click unencrypt in their toolkit now?) so killing all the comments and reducing @variable names to single characters, will make that harder, and hopefully force Junior-DBA to use the Source Code repository instead of being sacked! |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 09/12/2011 : 09:55:19
|
quote:
Although I think there may be some benefit in obfuscating the code.
If you're really serious about confusing hackers, and also really crazy, take a look here:
http://www.sqlsaturday.com/viewsession.aspx?sat=81&sessionid=4265
Not sure if you saw this when I posted it a while back in the Corral. It should make enjoyable reading in any event.  |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 09/12/2011 : 10:07:45
|
<reads link>..................... bleh <vomits>
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/12/2011 : 10:33:29
|
"Not sure if you saw this when I posted it a while back in the Corral"
Nope. I was on sabbatical I expect ... same code-point different glyph sounds ideal for my single-char @Variable obfuscation |
|
|
|
TaoK
Starting Member
1 Posts |
Posted - 12/05/2012 : 14:25:17
|
Just stumbled across this old thread today, and thought I'd pitch in just in case anyone's still interested.
On http://poorsql.com, you can do a "poor man's minification" - removal of comments and whitespace (including, for example, the whitespace between a numeric constant and any subsequent keyword or identifier, and any whitespace between word-breaking syntax elements like string constants, quoted names, etc).
This "minification" is reasonably simplistic, it only acts on whitespace (it does NOT modify local variables - I'm not sure that would be worth much for T-SQL code anyway, except as an obfuscation tool).
The .Net library that implements this (Poor Man's T-SQL Formatter) is open-source, and there is a command-line client for automation, but I never actually added the minification option to the command-line options; if this is something that someone would be interested in (for example in the context of build automation) please tell me, I would be happy to add a switch for this.
To the question of performance, I don't believe there is any tangible benefit to minifying; the only argument above that made sense to me was network/transfer time for deployments, in cases where you are guaranteed not to be debugging the corresponding procs in production (or even relying on line numbers in errors!)
I wrote this functionality only because it was very easy to do, given the pieces I already had (comprehensive tokenizer, rough parser); I can't make any statements about reliability etc except to say that it works for me :)
|
|
|
|
Topic |
|
Minify SQL source?
