Post reply

Relational database - rarely more efficient to store snippets of non relational data against a record - thoughts?

  • July 28, 2015 at 3:52 am

    #1815334

    Eric M Russell (7/27/2015)

    SQL Server stored procedure calls can accomodate multi-valued input paramters, if that's what you're talking about here. By this, I mean you can pass in a string of multiple invoice numbers, and the procedure returns a resultset containing data for that set of invoices. This input parameter could be in the form of a delimited or XML string.

    If a relational database otherwise seems to be a natural for for your organization's needs, then I wouldn't switch to a NoSQL alternative just to work around this type of case usage requirement.

    Agreed. Although, if possible, I'd avoid XML and all the memory overhead associated with it. Since you can use a tally table with Jeff's code to deal with delimited strings, it's going to work better for just a list of items like this.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 28, 2015 at 3:57 am

    #1815335

    Eric M Russell (7/27/2015)

    Based on what you've described above, I see no reason not to simply create a SubItem table.

    Absolutely agreed. Based on what we're seeing, maintaining this in anything other than a relational manner will become a major headache. This is still relational data, not non-relational.

    To get it back, include it in the original data set, bring back the information of the parent record and all the child records together a single data set. That means that you'll see six copies of the parent data if there are six child records, but you should be able to deal with that on the app side of things. One call, one data set, still relational storage. Or, if you really want to, it's added overhead, but you could pivot the results so that you return a single column of data, just IDs if that's all that's needed, for the child data. That'll work too.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply