Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I personally think the best place for this kind of string manipulation is in program code in a fully expressive language that has functions and can be compiled. Doing this in T-SQL is not good. Program code can have fast functions that do proper escaping.</p> <p>Let's think about things a bit:</p> <ul> <li><p>When you deploy new versions of the parts and pieces of your application, where is the best place for this functionality to be?</p></li> <li><p>If you have to restore your database (and all its stored procedures) will that negatively affect anything? If you are deploying a new version of your web front end, will the JSON conversion being tied into the database cause problems?</p></li> <li><p>How will you escape characters properly? Are you sending any dates through? What format will date strings be in and how will they get converted to actual Date objects on the other end (if that is needed)?</p></li> <li><p>How will you unit test it (and with automated tests!) to prove it is working correctly? How will you regression test it?</p></li> <li><p>SQL Server UDFs can be very slow. Are you content to use a slow function, or for speed hack into your SQL code things like <code>Replace(Replace(Replace(Replace(Value, '\', '\\'), '"', '\"'), '''', '\'''), Char(13), '\n')</code>? What about Unicode, <code>\u</code> and <code>\x</code> escaping? How about splitting <code>'&lt;/script&gt;'</code> into <code>'&lt;' + '/script&gt;'</code>? (Maybe that doesn't apply, but maybe it does, depending on how you use your JSON.) Is your T-SQL procedure going to do all this, and be reusable for different recordsets, or will you rewrite it each time into each SP that you need to return JSON?</p></li> <li><p>You may only have one SP that needs to return JSON. For now. Some day, you might have more. Then if you find a bug, you have to fix it in two places. Or five. Or more.</p></li> </ul> <p>It may seem like you are making things more complicated by having the middle layer do the translation, but I promise you it is going to be better in the long run. What if your product scales out and starts going massively parallel—you can always throw more web servers at it cheaply, but you can't so easily fix database server resource saturation! So don't make the DB do more work than it should. It is a data access layer, not a presentation layer. Make it do the minimum amount of work possible. Write code for everything else. You will be glad you did.</p> <p><strong>Speed Tips for String Handling in a Web Application</strong></p> <ol> <li>Make sure your web string concatenation code doesn't suffer from <a href="http://en.wikipedia.org/wiki/Schlemiel_the_Painter%27s_algorithm" rel="nofollow">Schlemiel the Painter's Algorithm</a>. Either directly write to the output buffer as JSON is generated (Response.Write), or use a proper StringBuilder object, or write the parts of the JSON to an array and Join() it later. Don't do plain vanilla concatenation to a longer and longer string over and over.</li> <li>Dereference objects as little as possible. I don't know your server-side language, but if it happens to be ASP Classic, don't use field names--either get a reference to each field in a variable or at the very least use integer field indexes. Dereferencing a field based on its name inside a loop is (much) worse performance.</li> <li>Use pre-built libraries. Don't roll your own when you can use a tried and true library. Performance should be equal or better to your own and (most importantly) it will be <em>tested</em> and <em>correct</em>.</li> <li>If you're going to spend the time doing this, make it abstract enough to handle converting any recordset, not just the one you have now.</li> <li>Use compiled code. You can always get the fastest code when it is compiled, not interpreted. If you identify that the JSON-conversion routines are truly the bottleneck (and you MUST prove this for real, do not guess) then get the code into something that is compiled.</li> <li>Reduce string lengths. This is not a big one, but if at all possible use one-letter json names instead of many-letter. For a giant recordset this <em>will</em> add up to savings on both ends.</li> <li>Ensure it is GZipped. This is not so much a server-side improvement, but I couldn't mention JSON performance without being complete.</li> </ol> <p><strong>Passing Dates in JSON</strong></p> <p>What I recommend is to use a separate JSON schema (itself in JSON, defining the structure of the virtual recordset to follow). This schema can be sent as a header to the "recordset" to follow, or it can be already loaded in the page (included in the base javascript files) so it doesn't have to be sent each time. Then, in your JSON parse callback (or post-callback on the final resultant object) look in the schema for the current column and do conversions as necessary. You might consider using ISO format since in <a href="http://ejohn.org/blog/ecmascript-5-strict-mode-json-and-more/" rel="nofollow">ECMAScript 5 strict mode</a> there is supposed to be better date support and your code can be simplified without having to change the data format (and a simple object detect can let you use this code for any browser that supports it):</p> <blockquote> <p><strong>Date</strong></p> <p>Dates are now capable of both parsing and outputting ISO-formatted dates.</p> <p>The Date constructor now attempts to parse the date as if it was ISO-formatted, first, then moves on to the other inputs that it accepts.</p> <p>Additionally, date objects now have a new .toISOString() method that outputs the date in an ISO format. var date = new Date("2009-05-21T16:06:05.000Z");</p> <p>print( date.toISOString() ); // 2009-05-21T16:06:05.000Z</p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. POUse SQL to return a JSON string
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErikE
    1. USErikE
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUse SQL to return a JSON string
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 Better than mine. I will remove my answer.
      singulars
      1. PO
      1. USL.B
    2. COYou raise some very good points, and make a compelling argument. It may not be the right solution for most situations. We were planning a standard format for things like dates and other work-arounds for issues mentioned above, but they were just that...work arounds. MS just need to add a JSON format like they offer an XML format.
      singulars
      1. PO
      1. USdavids
    3. CO@davids Is my additional information about passing dates in JSON helpful?
      singulars
      1. PO
      1. USErikE
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload