Solved

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

Posted on 2016-08-17
12
66 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
  • 7
  • 4
12 Comments
 
LVL 69

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Handle null when using linq in this line 1 20
Auditing with Temporal Tables 4 16
Log Backup 2 12
SQL Date Retrival 7 26
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now