|
It can be optimized
One DB
One Table
One Field
One Record
Why else we have linq2xml
[Edit]
For the sake of backup convenience maybe a second column int autokey would be appropriate. Record '0' is then the current version and all the others are backups.
modified 5-Dec-17 10:25am.
|
|
|
|
|
JustWatchLittle wrote: Why else we have linq2xml
because we needed a new acronym for GIGO
- the latter is last centuries jargon... and as the kids say "that's like 100 years ago."
Signature ready for installation. Please Reboot now.
|
|
|
|
|
I see, I'm definitely too old to discuss These things
|
|
|
|
|
JustWatchLittle wrote: It can be optimized
I could live with:
Three servers for the IT Department basement trolls
Seven databases for the Manager-lords in their window'd offices
Nine tables for Mortal Men doomed to their cubicles
One record for the CEO to monitor them all!
|
|
|
|
|
Cold be RAM and disk and case
And cold be the CPU under stress
Never more to access a data set
Never, till the CEO fails and the Company's dead
In the back room the DB shall die
And still on paper let them write
Till the dark admin lifts up his hand
And reboots the server at 3 AM
GCS d-- s-/++ a- C++++ U+++ P- L+@ E-- W++ N+ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X
|
|
|
|
|
And a partidge in a pair tree?
Someone's therapist knows all about you!
|
|
|
|
|
One ring to rule them all ...
I'd rather be phishing!
|
|
|
|
|
One Ring to bring them all and in the darkness bind them ...
|
|
|
|
|
I think that's why people are turning towards NoSQL platforms nowadays.
Not that it's an easy solution but it does take away from slowing down the database engines which were not originally built for XML.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
This is a time bomb. On any day or any reason this "database" will crash..
Press F1 for help or google it.
Greetings from Germany
|
|
|
|
|
KarstenK wrote: This is a time bomb. On any day or any reason this "database" will crash..
Yup. I made that prediction within a month of being here. People look at me like I'm one of those homeless people holding up a "the world will end tomorrow" sign.
|
|
|
|
|
What you should do is set up a one-time agent that exports the data into a new table that contains a reasonable schema so you can illustrate the folly of their ways. It shouldn't take you more than an hour or so to do.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Never mind - I just saw that the data keeps changing. In that case, saving the data as a comma-delimited blob would be better, because then you could include the header row, and thus you can be reasonably confident that you can correctly parse it out of the blob.
If you need it, I have a CSV parser that parses the data into a DataTable without intervention from the programmer. Just aim a stream at it and let it eat (and it even determines appropriate data types, unlike Microslop's importer).
Go here for an explanation of the class and to get the download - it's part of a larger application, but it should be a simple matter to extract what you need.
SQLXAgent - Jobs for SQL Express - Part 3 of 6[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 5-Dec-17 14:24pm.
|
|
|
|
|
John Simmons / outlaw programmer wrote: saving the data as a comma-delimited blob
At 323K per row, chances are HIGH that the XML data contains tons of commas that are structurally irrelevant. The worst of all nightmares.
It Is The Absolute Verifiable Truth & Proven Fact
That Your Belly-Button Signature Ties
To Viviparous Mama.
|
|
|
|
|
But the blob would still be much smaller because of the absence of XML element tags (assuming that each field contains no more than a few words)
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I like XML in the database.
It allows me to put stuff I don't care about somewhere I don't need to see it. But the downstream processes can get it and stick it in browser-based UIs easily.
|
|
|
|
|
If you want your data as xml from the database, you can do that in the query that pulls the data.
".45 ACP - because shooting twice is just silly" - JSOP, 2010 ----- You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010 ----- When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Quicker to do it once ahead of time -- it doesn't change during the day.
|
|
|
|
|
Marc Clifton wrote: But the core software is built on a third party product that puts everything in XML because that way its, um, extensible!!! Yay for buzzwords getting in the way of real work.
Jeremy Falcon
|
|
|
|
|
Amateurs.
You should create a servicelayer that converts between xml and json, and then store it in a Mongodb.
That's what nosql is for.
|
|
|
|
|
I'm not hearing anything weird or out of the ordinary
|
|
|
|
|
I've seen tables much, much bigger than that storing XML. And sometimes, you just need to store XML without knowing anything about the structure of it, like if it comes from another system and you need it verbatim.
Most interesting approach I've seen is not to use a native XML type, but to compress it to binary, then convert that to base64 and store that in nvarchar columns. Which drowns everything in zeros and probably makes things bigger than storing native, with the added benefit that its practically impossible to query.
Regards,
Rob Philpott.
|
|
|
|
|
Marc Clifton wrote: But the core software is built on a third party product that puts everything in XML because that way its, um, extensible!!!
Isn't that the definition of persisted object stores (ie NoSQL)?
Marc Clifton wrote: To me, that just seems in[s]ane.
Just not well thought out. But something that follows naturally for over enthusiasm for new technology (not blaming XML but rather that it was 'new' and thus used.)
Marc Clifton wrote: And this isn't the only table that contains these XML blobs,
Could be worse...could be invalid or even ill-formed xml.
|
|
|
|
|
jschell wrote: ill-formed xml
No such thing.
|
|
|
|
|
PIEBALDconsult wrote: No such thing.
Not sure what you mean but I dealt with ill formed xml many times.
Both in preventing it and in trying to parse data from something that had been created incorrectly.
|
|
|
|