Solved

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

Posted on 2016-08-17
12
102 Views
Last Modified: 2016-08-22
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?
0
Comment
Question by:gbzhhu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41759250
you want to do it all from SQL Server? Returning JSON is not an issue but compressing it is!
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759256
Yes all from SQL.  SQL 2016 has a Compress function just can't hack the syntax of it all
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759259
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41759262
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
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759268
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41759283
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
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759298
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41759310
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
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759326
Ok.  I read you now.  Let me look how IIS would be setup to do this
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 41759333
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 41759364
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
 
LVL 12

Author Comment

by:gbzhhu
ID: 41764959
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

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question