SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Minify SQL source?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  02:19:45  Show Profile  Reply with Quote
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).
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/21/2010 :  09:58:56  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  10:18:57  Show Profile  Reply with Quote
"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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/21/2010 :  10:49:15  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/21/2010 :  11:41:15  Show Profile  Reply with Quote
That's good enough for me; thanks.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/12/2011 :  04:53:16  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30112 Posts

Posted - 09/12/2011 :  08:41:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/12/2011 :  09:05:00  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/12/2011 :  09:20:58  Show Profile  Reply with Quote
"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!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 09/12/2011 :  09:55:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/12/2011 :  10:07:45  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
<reads link>..................... bleh <vomits>


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/12/2011 :  10:33:29  Show Profile  Reply with Quote
"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
Go to Top of Page

TaoK
Starting Member

1 Posts

Posted - 12/05/2012 :  14:25:17  Show Profile  Reply with Quote
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 :)

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000