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

I have a working query that I would like to:

1.  Break into 10 chunks (JSON)
2.  Zip/compress each chunk
3.  then return the whole 10 chunks as JSON

Can anybody help?
LVL 12
gbzhhuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
you want to do it all from SQL Server? Returning JSON is not an issue but compressing it is!
0
gbzhhuAuthor Commented:
Yes all from SQL.  SQL 2016 has a Compress function just can't hack the syntax of it all
0
gbzhhuAuthor Commented:
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')) 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Olaf DoschkeSoftware DeveloperCommented:
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.
0
gbzhhuAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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)

Open 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.
0
gbzhhuAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
gbzhhuAuthor Commented:
Ok.  I read you now.  Let me look how IIS would be setup to do this
0
gbzhhuAuthor Commented:
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?
0
Olaf DoschkeSoftware DeveloperCommented:
Is there a bottleneck at all? Even if it is on two machines, Webserver and Database servers should be neighbors, shouldn't they?

You may test whether SELECT COMPRESS(@x) makes any difference, as far as it's described it's rather for persisting zipped data than for transfer compression, but I also am not aware of details in regard of OLEDB providers or ODBC drivers doing transfer compression.

Even if you find something, then it would mean more CPU utilization and CPU is a valuable resource on a database server.

No matter what, if your ASP.NET web service queries a local SQL Server, the transport protocol should be Shared Memory and for that compression and uncompression would rather be two extra steps you can spare, obviously, as the compression/decompression ends in what you have initially.

And if you have split web server and database server, then the reason for that would be having CPUs dedicated to both services and being able to load balance that better. The network between database and web server shouldn't be a concern, then. Even less so, if the machines are virtual servers.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gbzhhuAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.