|
Molotov Cock Tale posted:Ok on SQL2012 you should be able to do this with a recursive CTE. The anchor is what you posted, union all to the same, on recipe.id = ingredient.recipeid or however you've done that. It took me a little while, but I basically got what I'm looking for with the following: code:code:(Use the PK from the Ipk column, and sort any from the Rpk column under it) code:
|
#
?
Jul 17, 2013 18:13
|
|
|
|
| # ? May 30, 2024 05:51 |
|
|
the company I'm working for has a giant mess of a legacy database which is connected to their giant mess of an ERP I've been hired over the summer to implement a separate system to keep track of a small amount of extraneous data to do with a very specific subset of the rest of the data. (Only the "lots" which come into the Worcester, MA branch from certain towns in CT) The server that runs SQL Server 08 is out in Denver, and the guy responsible for it doesn't want me to many any modifications to the ERP database for understandable reasons. So my solution is to create some views inside another database on the server which will query all of the data needed from the ERP, and then I can use the data from the views as necessary. I'm wondering exactly what strain will this put on the server? I'm not all that experienced with database development, so I'm trying to make this as unobtrusive as possible. So basically I'm trying my very hardest to not upset the delicate performance balance on this server, which is being accessed from multiple branches many times throughout the day. I wanna know: - Does making a separate database help performance in any way (as opposed to making a few tables on the same database)? Would it have to be on an actual separate server to see any improvement? - Would trying to limit the number of times I query the main ERP database help? - If so, does using a view help with this? Ie. in my mind I assumed (probably wrongly) that it works like this: The view queries the ERP database, then when I query the view multiple times with the front-end I'm making, it won't requery the ERP, it will only use the data that's already been loaded into the view (is that even how sql works??) - If that's not how it works, is it possible to make it do that? - Or better yet, could I just have data be extracted from the ERP on a schedule, like once a day? so far I think I've got the hang of database design, I've been reading a lot about how to ensure data integrity and I think I'm picking it up pretty well. I also definitely know how to design a good front end that will keep people from loving up the rules that I can't necessarily enforce directly in the database itself. I just don't know about resource management on a sql server, and I want to play it safe.
|
|
#
?
Jul 17, 2013 18:52
|
|
|
jryand posted:I wanna know: No, making a separate database shouldn't matter at all. Putting it on an actual separate server is a huge performance downgrade, unless you're going to move all the data from the source server to the other anyway. Replication or somesuch is a reasonable solution to this. Yes, limiting the number of times you query the main database would help. Views don't work that way. A view is basically a predefined SELECT, and nothing more. It doesn't cache any data for you in the middle -- it should make and save an execution plan for you, which is a minor help, but every query on the view is a direct query on the data underlying it. I really, really recommend replication for your purposes if you want a holistic copy of the source data. Does your separate system have to be real-time?
|
|
#
?
Jul 17, 2013 19:39
|
|
|
Sudden Infant Def Syndrome posted:What I'd love to do would be to order it so that it puts the sub ingredients under the main like this:
|
|
#
?
Jul 17, 2013 19:45
|
|
|
Anaxandrides posted:No, making a separate database shouldn't matter at all. Putting it on an actual separate server is a huge performance downgrade, unless you're going to move all the data from the source server to the other anyway. Replication or somesuch is a reasonable solution to this. no, it would be fine to update overnight or something Alright I'm looking into replication, but I'm getting this error: code:"sp_helpserver select @@servername" it returns NULL I've been looking for a way to do this but I guess I'm going to have to talk to the network administrator to fix this? He's pretty unfamiliar with SQL in general, so I'm not sure that's even a valid option jryand fucked around with this message at 20:13 on Jul 17, 2013 |
|
#
?
Jul 17, 2013 19:58
|
|
|
Sedro posted:Change your ORDER BY RL.Lvl to ORDER BY Rpk, RIpk Haha durrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr. Edit: For some reason this returns -1 in PHP using sqlsrv_num_rows. If I replace the SQL with a different query it works fine. Sudden Infant Def Syndrome fucked around with this message at 21:01 on Jul 17, 2013 |
|
#
?
Jul 17, 2013 20:19
|
|
|
Anaxandrides posted:Well, that's not the expected answer. What in the world is causing server crashes frequently enough that you're looking at moving away from replication in order to ease recovery? Our servers don't crash terribly frequently, but if replication fucks up we're incredibly hosed. We have around 350 tables replicated among the 5 shards, and I recently finished an audit showing the necessity of replication for more than 90% of the tables. One of the big problems is we can't bring servers into synch by turning on replication for all the tables at once. We tried that while recovering from a catastrophic failure about a year ago and ended up DOSing two production DB servers. That incident, and a human error fuckup lit a fire under the higher ups to find something to replace replication that wouldn't, for instance, require truncating and refilling a table with 99% correct data when turning on transactional replication. Furthermore, we're worried about the increasing overhead of merge replication as we bring additional shards online. I think the plan is to grow from 5 shards to 10 by the end of 2014.
|
|
#
?
Jul 17, 2013 21:14
|
|
|
jryand posted:no, it would be fine to update overnight or something Does sp_helpserver return zero rows? If so, it's possible that the linked server doesn't exist. Do you have permissions to create a linked server and, if so, do you have a remote login to use on the server you're trying to connect to? And is the firewall between the two open? The NULL result is probably @@SERVERNAME. You can fix that by using sp_addserver for 'local'.
|
|
#
?
Jul 17, 2013 21:29
|
|
|
I think I'm going to just create a job to select into a table in my database, is there any reason why that's not a good idea?
|
|
#
?
Jul 17, 2013 21:59
|
|
|
jryand posted:I think I'm going to just create a job to select into a table in my database, is there any reason why that's not a good idea? No reason it's not a good idea, no. If you have access to Microsoft's entire toolkit, SSIS will be faster and cleaner. Plus that way you don't need to create a linked server.
|
|
#
?
Jul 17, 2013 22:35
|
|
|
Nth Doctor posted:Our servers don't crash terribly frequently, but if replication fucks up we're incredibly hosed. We have around 350 tables replicated among the 5 shards, and I recently finished an audit showing the necessity of replication for more than 90% of the tables. Can you be any more specific about the tables you're trying to synchronise across shards? Do all shards write to these tables, with changes then sent to all the other shards? Is it just pushing changes from a master copy to the shards? Do the tables need to be in the DB they're in for RI reasons? What is their read/write pattern of behaviour? CDC and SSIS sound good so far, just wondering what else could fit.
|
|
#
?
Jul 17, 2013 23:45
|
|
|
Molotov Cock Tale posted:Can you be any more specific about the tables you're trying to synchronise across shards? Do all shards write to these tables, with changes then sent to all the other shards? Is it just pushing changes from a master copy to the shards? Do the tables need to be in the DB they're in for RI reasons? What is their read/write pattern of behaviour? It's a big mix, really.
|
|
#
?
Jul 18, 2013 01:39
|
|
|
Nth Doctor posted:It's a big mix, really. 3 and 4 sound like good use cases for CDC/Change Tracking. Maybe ideal, in fact. For 1, it's hard to see how else you're going to get that functionality without merge replication. The only question I have is 2 -- is every single transaction propagated? Are these tables in their own database, or do they share a database with tables that are replicated differently? If they are distinct, and if SQL2012 is an option SQL AlwaysOn may be a decent solution.
|
|
#
?
Jul 18, 2013 08:39
|
|
|
Anaxandrides posted:3 and 4 sound like good use cases for CDC/Change Tracking. Maybe ideal, in fact. For 1, it's hard to see how else you're going to get that functionality without merge replication. The only question I have is 2 -- is every single transaction propagated? Are these tables in their own database, or do they share a database with tables that are replicated differently? If they are distinct, and if SQL2012 is an option SQL AlwaysOn may be a decent solution. In case 2, all transactions are replicated from shard 1 to the other shards. The tables are in databases with non-replicated tables, so log shipping is out. SQL 2012 is not an option at this point in time, unfortunately. I'm finally at the point where I can get my hands dirty with actual code instead of researching and vomiting information at a table full of DBAs every couple weeks, so I'm going to investigate CDC/CT and Sync Services to see if this can get me where I need to go.
|
|
#
?
Jul 18, 2013 13:08
|
|
|
I've written a SELECT statement that pulls up a single row of information for any particular Japanese character it's given. What's the best way to feed it multiple characters so I can retrieve information on each character in one go? Right now I'm using OR's in the WHERE clause; while I am getting back one row of information per character as expected, the rows returned don't match the order of the characters I feed into the statement:quote:SELECT Edit: I discovered that I could instead type quote:WHERE c.literal IN ("貸", "出", "金", "利") IAmKale fucked around with this message at 17:57 on Jul 19, 2013 |
|
#
?
Jul 19, 2013 17:51
|
|
|
Karthe posted:I've written a SELECT statement that pulls up a single row of information for any particular Japanese character it's given. What's the best way to feed it multiple characters so I can retrieve information on each character in one go? Right now I'm using OR's in the WHERE clause; while I am getting back one row of information per character as expected, the rows returned don't match the order of the characters I feed into the statement: http://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order
|
|
#
?
Jul 19, 2013 18:05
|
|
|
Doctor rear end in a top hat posted:You can give the individual characters sorting values using a CASE statement.
|
|
#
?
Jul 19, 2013 18:30
|
|
|
What is people's take on generating your own SQL queries procedurally, assuming you're not using a SQL DSL or any kind of ORM? Say it's done at the layer of a web-app calling into Postgres. Say you have a basic SELECT and you might want to spice it up a permutation of LIMIT, OFFSET, ORDER BY etc, each likely in need of parameterization. Possibly more predicates for WHERE. I'm seeing a couple of options options: 1. I write each individual distinct query by hand. That's a lot of repetitions for each combination, not very DRY and adds more cognitive load to the DAL than I like. 2. I write some sort of really basic query generator based on the parameters I'm given and I try hard not to gently caress up the order of interpolation. I very much like the brevity of 2, but I'm wondering if people have gone down that road in the past and ended up hitting a dead end that I might not foresee right now? Stitching a SQL string, bad idea? DreadCthulhu fucked around with this message at 20:20 on Jul 21, 2013 |
|
#
?
Jul 21, 2013 09:04
|
|
|
DreadCthulhu posted:What is people's take on generating your own SQL queries procedurally, assuming you're not using a SQL DSL or any kind of ORM? Say you have a basic SELECT and you might want to spice it up a permutation of LIMIT, OFFSET, ORDER BY etc, each likely in need of parameterization. Possibly more predicates for WHERE. I'm seeing a couple of options options: If I'm understanding you correctly, we do some of this by using dynamic SQL in our stored procedures from time to time. The biggest downside is that you end up with one execution plan per permutation, rather than one for the entire sproc.
|
|
#
?
Jul 21, 2013 18:19
|
|
|
Nth Doctor posted:If I'm understanding you correctly, we do some of this by using dynamic SQL in our stored procedures from time to time. The biggest downside is that you end up with one execution plan per permutation, rather than one for the entire sproc. We've gone for a similar idea with dynamic SQL to return TOP X records or whatnot in some queries. It's possible to save yourself some grief with indexing and, to some extent, execution plans, by dumping query results into a temporary table and using OFFSET, LIMIT, etc on that. If you want to get fancy, you can have your proc call another proc that does have a cached execution plan, dump the proc results into that temporary table, and LIMIT/OFFSET that. We've only done that for lovely performance fixes on procs that needed immediate fixes, though, and rewritten them later when we had time.
|
|
#
?
Jul 22, 2013 08:25
|
|
|
I've got a bit of SQL that works, but i do not know why it works:code:Aid 87 88 89 90 91 92 which is what the query should return. The part i'm having trouble understanding is the 'Set @DivisionT' part. I debugged the query and at the print @DivisiontT part it gives me quote:insert into #FilterDivisionID values(87); Somehow it loops through all the DivisionID numbers. 
|
|
#
?
Jul 22, 2013 15:21
|
|
|
Charlie Mopps posted:I've got a bit of SQL that works, but i do not know why it works: The replace call is replacing every comma in the string with: '); '+char(10)+'insert into #FilterDivisionID values(' so it isn't looping so much as automatically creating one EXEC statement with all 6 inserts. Edit: See this code code:Beginning one PRINT of the string insert into #FilterDivisionID values(87); insert into #FilterDivisionID values(88); insert into #FilterDivisionID values(89); insert into #FilterDivisionID values(90); insert into #FilterDivisionID values(91); insert into #FilterDivisionID values(92); Ending one PRINT of the string (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (6 row(s) affected) Nth Doctor fucked around with this message at 15:37 on Jul 22, 2013 |
|
#
?
Jul 22, 2013 15:34
|
|
|
Nth Doctor posted:The replace call is replacing every comma in the string with: '); '+char(10)+'insert into #FilterDivisionID values(' so it isn't looping so much as automatically creating one EXEC statement with all 6 inserts.  i have no idea why i couldnt figure that out myself.  Thanks a lot!
|
|
#
?
Jul 22, 2013 15:40
|
|
|
E: F,b. @DivisionID starts off as a comma separated list of numbers. The line you're looking at uses the REPLACE function to replace the commas in @DivisionID with code:
|
|
#
?
Jul 22, 2013 15:40
|
|
|
DreadCthulhu posted:What is people's take on generating your own SQL queries procedurally, assuming you're not using a SQL DSL or any kind of ORM? Say it's done at the layer of a web-app calling into Postgres. Say you have a basic SELECT and you might want to spice it up a permutation of LIMIT, OFFSET, ORDER BY etc, each likely in need of parameterization. Possibly more predicates for WHERE. I'm seeing a couple of options options: This sounds like the "DAL"* from our old 10 year old WinForms app to a degree. I'd either keep it trivially simple or just do #1. DRY can actually get you in trouble sometimes when you're writing this kind of code. Our system had a thing like "use this query template when fetching a single Foo, use this query template when fetching a list of Foos". The lovely thing is that these queries often had lots of joins and select *s to support certain wide grid views or bloated forms, so they performed lovely if you tried to reuse them. I'm sure you could do it far better than our thing, but those pitfalls still exist and somewhat negate the value of something like this in the first place. * really it was just all in event handlers in the client application
|
|
#
?
Jul 22, 2013 18:27
|
|
|
The magento eav structure makes me cry. That's all I have to say folks, just wanted to share my despair
|
|
#
?
Jul 23, 2013 14:24
|
|
|
Apologies if this is the wrong thread, the distinction is slightly fuzzy to me. In our environment no one has used CLR methods for their projects before so yesterday when I wrote a really simple one to do a task I was asked to convert some of our apps to use a CLR method due to the apps being pointless and not running correctly. Now with all of these different projects I have some issues. Should I create a separate database to store all these functions and procedures since some of them require trustworthy and unsafe to be set? Drythe fucked around with this message at 18:38 on Jul 24, 2013 |
|
#
?
Jul 24, 2013 13:49
|
|
|
I have 2 tables linked by a foreign key The first table has an instead of delete trigger which will check if any rows in the other table reference the deleted records, and if so, set "NoNameBrand" to the deleted brand name, and switch the foreign key to point to this "No Name" record: code:code:When you delete a record from the first table, it will set the record to have NoNameBrand reference the deleted brand name But then before it's deleted, the insert/update trigger will see that the NoNameBrand already exists in the first table, so it will switch it back to the original state and then it will try to delete the record, only to get an error because the fk is restricting it Is there a way to time it out better? Essentially I would like to delete the record immediately, and THEN perform the necessary updates to the NoNameBrand I was thinking of using a UDF but that seems unnecessary and annoying
|
|
#
?
Jul 24, 2013 17:13
|
|
|
Drythe posted:Apologies if this is the wrong thread, the distinction is slightly fuzzy to me. Be very, very careful with CLR methods. You can create a new database to hold these CLR procedures if you like, yes. The only warning I will give is that CLR methods are indeed unsafe. An exception thrown for any reason has a pretty decent chance of bringing down the SQL instance and forcing a restart. What in the world are you doing that CLR is required? Is it possible to pump this through a script task in SSIS, or through a new .exe written for this purpose? jryand posted:However, these two triggers conflict with each other To be perfectly honest, this is the exact sort of thing that triggers ought not be used for. That said, couldn't you just add a WHERE clause on the update trigger WHERE c.NoNameBrand <> cd.NoNameBrand? Anaxandrides fucked around with this message at 19:53 on Jul 24, 2013 |
|
#
?
Jul 24, 2013 19:35
|
|
|
why not exactly? what should I be using instead?
|
|
#
?
Jul 24, 2013 20:17
|
|
|
jryand posted:why not exactly? You're doing computational tasks in the database, and that's just a fundamental design flaw. The applications that interact with the database should be doing all the computation; triggers are for ensuring database integrity (e: also a few other things in some edge cases), and your database should have as few integrity constraints as it can. Amarkov fucked around with this message at 03:08 on Jul 25, 2013 |
|
#
?
Jul 25, 2013 03:05
|
|
|
Amarkov posted:You're doing computational tasks in the database, and that's just a fundamental design flaw. The applications that interact with the database should be doing all the computation; triggers are for ensuring database integrity (e: also a few other things in some edge cases), and your database should have as few integrity constraints as it can. This, basically. While I'm certainly in favor of having computational tasks in the database when and where it's appropriate, and there certainly are cases, these triggers represent a giant red flag about whatever application is inserting this data. These triggers are being used to do things here that an application should explicitly be doing, and that you're looking to fix these triggers rather than have whatever application is doing this be corrected. It would be far, far better to see this done on a client somewhere and get correct data sent to your insert/update/delete, rather than this. I do understand that sometimes band-aids are needed, but this doesn't look like one. It looks like a permanent feature.
|
|
#
?
Jul 25, 2013 10:40
|
|
|
I see why I shouldn't use the insert/update one, but I'm not so sure about the delete one The list of "accepted brands" changes often enough that there will probably be many brands deleted over time, and the actual items associated with those brands need to be "soft deleted" and set to be a no name. I'm not sure these people will always go through the client, since I'm only here for another month and I don't really know what they're going to do with the database once I leave are you sure I should only be doing this through the front-end, or is there any other way I can accomplish this? e: alright I've been thinking about this more and I think i might have really messed up and I should have been using soft delete flags for everything all along so I might decide to just rename all my tables, add an "IsDeleted" column to all of them, create views with the original table names that filter on IsDeleted, and make sure all of my code that involves delete statements is modified jryand fucked around with this message at 14:36 on Jul 25, 2013 |
|
#
?
Jul 25, 2013 14:08
|
|
|
jryand posted:I see why I shouldn't use the insert/update one, but I'm not so sure about the delete one The way you're doing it can accomplish it, make no mistake. It's just not the preferred way to go about it. I did suggest adding a WHERE clause to your INSERT/UPDATE trigger -- does that not work? It looked as though it would to me, but you're doing some weird stuff with it. Also, so far as it goes, I am a big fan of storing data in a way that allows historical analysis of what the data was, for reporting/auditing purposes. By the same token, I much prefer "IsActive" than "IsDeleted", if only because DELETE is an actual command, and something where IsDeleted = '1' should intuitively not be in the database at all any longer, as it would've been deleted.
|
|
#
?
Jul 25, 2013 16:30
|
|
|
I'm looking for an efficient way to do an update on a table to update a percentile based on how the 'score' value of the row compares to every other row. I don't need it to be pure Mysql - if pulling some values through a PHP script first is better, that's fine. I do need the percentile to be stored, because I want to be able to select a random 'nearby' opponent - picking a random target value for percentile within X of the player, and then taking the first entry above or below that value, seems like a very quick way to do this, provided percentiles are in the table and indexed. Ideally I'd want the percentiles to not actually be accurate - by which I mean, in the event of scores "5, 10, 10, 20, 30, 50" I would rather have the percentiles be "0, 20, 40, 60, 80, 100" than "0, 30, 30, 60, 80, 100", even though that unfairly puts one player above another. And now I think I've answered my own question while posting the ways that occurred to me - is this solution horrible? code:It appears to work, but will it be horrifying if there's tens of thousands of rows?
|
|
#
?
Jul 25, 2013 21:12
|
|
|
Oracle Spatial question here: Is there a built-in function to take an existing shape and just reverse the winding order of the points? Long story short, a bunch of data was ingested with a backwards winding order and I need to flip them around, I'd love to have a nice little 1-liner I can throw at the offending data. If it matters, all the shapes are guaranteed to be convex quadrilaterals. Some span the antimeridian.
|
|
#
?
Jul 25, 2013 21:48
|
|
|
I've got this table of realestate properties with 3 columns I want to ORDER BY. I want anything with `poa` = '-1' to be first in the list, followed by anything WHERE (NOT `auctiondate` = '3000-01-01 12:00:00' AND `auctiondate` > NOW()) to be sorted by auctiondate, followed by everything else ordered by `askingprice` So in the list of properties, first you see all the Price on Application ones, followed by everything with an upcoming auction (except where the default auctiondate in the year 3000 is present) followed by everything ordered by price. This stops people guessing the askingprice of upcoming auctions/PoA stuff based on where they fall in the search results. How the heck do I do this? The default auctiondate being on 3000 is throwing me off (I think?), I've been going to stupid lengths like: ORDER BY `poa` ASC, (CASE WHEN (`auctiondate` > NOW() AND `auctiondate` != '3000-01-01 12:00:00') THEN `auctiondate` ELSE `askingprice` END) DESC and a bunch of variants of that but I can't really get it working the way I need it to. Any decent way to make this happen?
|
|
#
?
Jul 26, 2013 02:40
|
|
|
Try thisSQL code:
|
|
#
?
Jul 26, 2013 04:36
|
|
|
You did it! Thanks so much  This is the first time I'd discovered CASE WHEN, so I was having some trouble bending it to my will. Works perfectly!
|
|
#
?
Jul 26, 2013 05:13
|
|
|
|
| # ? May 30, 2024 05:51 |
|
|
Bad Munki posted:Oracle Spatial question here: Can you use SDO_UTIL.REVERSE_LINESTRING? this document has a couple of functions that may do the trick for you. roomforthetuna posted:It appears to work, but will it be horrifying if there's tens of thousands of rows? Have you looked at FIND_IN_SET and GROUP_CONCAT? If this were MSSQL, I'd suggest a CTE using OVER, but that's out since you're on MySQL. This looks like a reasonable alternative.
|
|
#
?
Jul 26, 2013 20:53
|
|