SQL Server (2016) Query to return the whole result as compresed JSON

you want to do it all from SQL Server? Returning JSON is not an issue but compressing it is!

Yes all from SQL.  SQL 2016 has a Compress function just can't hack the syntax of it all

The query is below and works fine - thought a bit slow (guess will have to remove convert functions) chunking and compressing syntax I am stuck with
      

SELECT t = (SELECT 
			cp.id AS cpid
			,cp.customerSrcRef AS cr
			,cp.productSrcRef AS pr
			,CONVERT(DATE, cp.validFrom) AS vf
			,CONVERT(DATE, cp.validTo) AS vt
			--,CONVERT(DECIMAL(10, 2), ((cp.rate/100) * pr.rate) + pr.rate) AS p
			,cp.price as p
			,cp.active AS en
			,cp.modifiedDate AS md
		FROM dbo.accountManagerCustomer amc
		INNER JOIN dbo.customerPrice cp ON amc.customerSrcRef = cp.customerSrcRef			
		WHERE amc.enabled = 1 AND amc.deleted=0
			AND cp.validTo >= CAST(GETDATE() AS DATE) AND cp.active = 1
			--AND ((@isFullCustomersAM = 0 AND amc.accountManagerRef = @accountManagerId) OR @isFullCustomersAM != 0)
		ORDER BY cp.id

		FOR JSON PATH, ROOT ('root')) 

Select allOpen in new window

Like Éric said. The usual solution is having a REST API do the MSSQL Server query and forward that as JSON. The webserver configuration then simply needs to support gzip and the requests send need to have an accept encoding gzip header.

Also see: https://msdn.microsoft.com/en-us/library/ms143729.aspx

Look out for "Native XML Web Services", This is a deprecated feature. The advice is "Use Windows Communications Foundation (WCF) or ASP.NET instead."

Bye, Olaf.

Hi Olaf,

We are using a web service (not rest but good old asmx. will probably go Rest at some point).  This is a solution for a mobile app with huge amount of data.  Time is being lost in transferring data from SQL to Web server (100mb of data and can grow) hence why I am tinkering with zipping in SQL.  Is there another way to reduce that data without round trips?

Cheers

I see what you mean...http://blog.sqlauthority.com/2015/12/21/sql-server-2016-new-t-sql-functions-compress-and-decompress/

So you want to compress the whole JSON and not just single coplumns, then you need to assign the resul to a variable to be able to apply COMPRESS to it:

DECLARE @x NVARCHAR(MAX) = (SELECT ... FOR JSON ) 
SELECT COMPRESS(@x)

Select allOpen in new window

I'd still say the compression rather is the task of the driver used, you typically only want transfer gzipped, but let this arrive on the Javascript level decompressed, like any requested html, css, image files.... Or do you plan to implement gzip in javascript for client side decompression?

Bye, Olaf.

Hi Olaf,

There is no javascript, css etc.  This is for native iOS app.  The data will be unzipped in the app using Swift.  I'd have to make sure part parties (zipper and unzipper) are using the same zip format

Ok, So I will try to chunk the request then assign the result to a variable then compress that variable.  

If anybody has done anything similar I'd appreciate

Cheers

Doesn't really matter much, if it's Javascript or Swift, if it's Windows, Android or iOS or OSX. You use http and that in itself would handle compression or not transparently, doing this on your own is just making it complicated.

This should be handled on the level of IIS: https://technet.microsoft.com/en-us/library/30e64245-ec6b-4c4e-891b-b7249e36c0a0

Bye, Olaf.

Ok.  I read you now.  Let me look how IIS would be setup to do this

It looks like the HTTP compression would be applied to transfer of data between web server and client.  How do we deal with the data transfer from DB to web server?

Hi Olaf,

Useful input there from you.  The servers are Amazon AWS servers hired by our company and I am not sure of their architecture.  We only just joined this parent company and are also new to AWS

We managed to use compress with JSON now and are clocking 27 seconds for 110MB of text data (compressed to 11MB) this is tested by calling the web service via the browser on local machine.  I am assuming calling from device will take the same.  This is a huge improvement for us as we were getting speeds of 90+ seconds.  

I will have to revisit the IIS compression as we are desperately late with project now.  You said it was easy though so I am positive.  

Thank you for assisting