Solved

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

Posted on 2016-08-17
12
117 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

622 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