I've run into the practical default limit in postgresql, and it is 8 joins. Anything over 8 joins in postgres, and you're playing with fire.
More specifically, join_collapse_limit defaults to 8. The planner refuses to reorder joins if the query excedes join_collapse_limit joins. I suspect pla...
I'm designing a database and a Java application to the following:
1. Allow user to query the database via an API.
2. Allow a user to save a query and identify the query via a 'query-id'. User can then pass-in 'query-id' on next call to API, which will execute the query associated with id but it w...
You can use the PIVOT function to get the result.
The way I would recommend doing this would be to write a hard-coded version of this first, then convert it to dynamic sql.
Note: I determined the cost per month by dividing the Actual Cost by the number of days per between the projectstart/pro...
@SimonRigharts neither. ALTER DATABASE MODIFY FILE, SET OFFLINE, move file, SET ONLINE.
I really should do a blog post on that. Detach is "easiest" but most prone to disaster. Backup is easy but can take much time and can interfere with log chain.
@swasheck Yeah it is kind of kludgy but it has the advantage of being generally applicable to things like start-up commands and the like. For example you could add it to the RUN keys in the registry if you wanted to.
I approach Windows administration like I do Linux administration.... :-P
I don't know if powershell might offer better solutions though since I don't have much experience with that.
and I just realised I brought the database servers back up after patching and told the web guys that it was back up without actually moving said database files. I am so clever
I feel that with a properly tuned DB it would be easier to store only historical aggregates and query the current day from the base table (be it anything :) — dezso11 mins ago
@dezso I'll reverse the question: isn't the involvement of the genetic optimizer enabled so things (e.g. finding a decent plan) are not too slow with many joins?
@dezso Depends on requirements, but storing aggregated historical data with the leading edge coming from transactional data (directly or through a low-latency feed) is the normal way to do low-latency reporting systems.
@ConcernedOfTunbridgeWells Ick.... I'd never do it that way.
too many failure cases
I think for opening balances and journal entries it's far cleaner to do closing balances and roll forward to live transactions from there.
(keep in mind that larger companies usually close a lot of things monthly so your high volume accounts will involve no more than 1 month of live data to move)
@ChrisTravers Depends on how the system does opening balances. Oracle Financials calculates an opening balance for the next month on closing IIRC. However, from the docs a lot of the closing is initiated manually, so this process may vary with the site.
@ConcernedOfTunbridgeWells You may be calling an "opening balance" what I am calling a "closing balance" for example, but still that's a roll forward point, right? You aren't storing live summary info regarding checking account balances or the like, right?
No. The record is generated on closing. The balances table does have some fields that OF updates with the journals, though. I have no idea how reliable they are for 'current' balances. We didn't use them - the current balance was calculated from the opening position + journal entries at the appropriate rate for the account (P/L accounts at a spot rate, BS accounts at an average monthly rate. certain capital accounts at odd synthetic rates).
The closing process is still initiated manually, though. Accountants still feel they have to control that process themselves - I guess it comes from trial balances.
@ChrisTravers Could just be terminology.
There is an adjustment between the CB and OB on balance sheet accounts due to F/X changes, though. CB and OB in settlement currency are the same.
@ConcernedOfTunbridgeWells There are a couple reasons for manually doing closing. It provides an opportunity for review, and for everyone to sign off on financial info.
It also allows for control as to when a given accounting period is closed and no more transactions are allowed in that period.
@ConcernedOfTunbridgeWells and I also think that there is a certain limit (in row number for example) from which upwards precalculated aggregates are the clear winner
It may depend on how big the overhead is in inserts, too
@ChrisTravers I was more being facetious about the accounting profession's insistence on manual processes for things that can be calculated by a stored procedure.
'We couldn't possibly automate that. It's far too complicated.'
COTW: 'So, tell me how you calculate earned premium then'
@ConcernedOfTunbridgeWells Here's what I tell them: "Treat the computer as a bookkeeper in need of supervision. Let me automate things in a way that you can supervise."
@ChrisTravers I know. It's a source of endless amusement when I do accounts related stuff. The accounts people here are used to being able to blind the business with science and run their own little empires. They pretty much assume that IT people are too dumb to understand accounting. It's a cultural thing.
@ChrisTravers Yes. There's a lot of overlap, particularly in the MIS area. From time to time I even get accounts departments viewing a data warehouse as a threat.
The problem is that SQL Server was running under a NT SERVICE sub-account.
Does them out of a job or reduces their gatekeeper status on the data.
Data warehousing tends to be quite political. A lot of people view control of certain data or insight into how it's calculated as job security or prestige. There is very much an 'I report this to so and so' mentality. They want to be seen to be producing something.
Same old, same old. Kimball coined the term 'gatekeeper' to describe these people and it's a real operational problem in implementing data warehouse systems.
Accountants definitely want the ability to send 'adjusted' figures out. For a data warehouse I need to be able to separate the manual adjustments out and show them in reconciliation reports. They definitely don't like having their dirty little secrets exposed.
It depends on the accounts people. Some like the idea of automated reporting systems to get BAU work off their plate. Some view it as a threat. I had one systems accountant say he wanted to 'feeeeel' the data before he released it.
Having said that, there are plenty of applications for having the manual adjustments available in the data warehouse. It allows corrections of things that can't be corrected at source (e.g. mistakes in financials advised over EDI systems that you need to get a broker to sort out at their end).
@ConcernedOfTunbridgeWells As far as financial figures, accounting should be the gatekeepers of the data, IMO. The question becomes what role a data warehouse plays in supporting the work of everyone, and such. The challenge might be to either help ensure that accountants can be gatekeepers for their domain or how data warehouse systems can support (and supplement) their domains separately.
One thing that gets drilled into anyone's mind studying accounting is this idea of careful division of oversight.
(Because this is something that prevents things like embezzlement, or human error running amok)
How my customer does things is they have live credit card transactions entering in web servers (unfortunately running MySQL). These get pulled in nightly to the accounting system using PostgreSQL, and digested into financial transactions. These transactions are then reviewed and posted to the books (or backed out and support requests filed) by accounting staff.... Then the final numbers are digested into reports that go into other reporting systems beyond accounting's control.
(those systems include customer-facing reporting systems)
@ConcernedOfTunbridgeWells Very true. And then there are processes that nobody understands how they work and attempts to be transparent reveal fun surprises. :-P
@ConcernedOfTunbridgeWells I once did a fixed asset depreciation system for a customer and they wrote back saying it was generating the wrong numbers, compared to their existing system.
@ChrisTravers There is that too - and IT departments are just as bad. I have this working theory that incompetence goes up to about the 90th percentile of the IT industry.
anyway off for dinner, but the basic point is it is a sign of someone really programming outside his (in this case) depth. In other words trying all kinds of kludges before settling on something with the explanation of "FINALLY IT WORKS!"
I have many schemas on mysql instance running on my development pc. I've noticed that mysql is taking a large amount of ram and cpu time.
Is there a way to "disable" schemas of project I'm not working for?
I like to avoid to drop schema and then re-import when I need it. I'm searching to a way ...
Take note folks, if you ever need to setup SSL for SQL Report Server, verify the stinking certificate before configuring SSRS or deal with this: dba.stackexchange.com/questions/34760/…
As part of the development team's desire to get out of doing production support, we've gone through all of the SQL Agent jobs and provided friendly instructions to help people gauge "is this important", when does it need to be fixed, etc
"Job failures will have a direct impact on our websites. Contact on-call teams if you need assistance! This issue needs to be resolved by 7:00AM.
If this issue is not resolved please alert the Marketing Team"
Soooo uhh that failed last night at 5:30 and no one did squat until this morning at 750 after our business users, and potentially the public, had been noticing
Boggles the mind. I can't see how you can make the criticality of that situation more apparent
The ideal solution would be to use 32 separate TinyInt fields. TinyInt supports values 0-255. If you try to use a single field and do bit processing you lose the ability to index any of these flags just as you would if you used Bit type fields.
I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the following errors
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, pa...
Hopefully someone else can give you some magical answer, but I STRONGLY suspect the resolution will be to restore the month old backup and deal with the data loss. If it was vital data then it's unlikely a competent DBA allowed it to be in simple recovery with a month between backups, so there may be resume-updating as well. — JNK10 secs ago
No, they were lucky not to lose anything. The funny part was they were located in a 40-storey building (one of the few in Athens). The real fun was the day after 9/11, when no-one dared to speak to him. I think they rented a place a few weeks later for the backups.
@ypercube I think sometimes people don't want to think about disaster recovery because it's unpleasant and kind of grim
if you are in business long enough something bad WILL happen to a physical structure or system
it may not be a building collapse but you will have a water leak or the person in the floor above you will leave something running over the weekend and flood your server room or the AC will kick off or there will be a fire that sets off sprinklers or something
Not sure I have it any more. Well, I'm sure I have it as I'm a pack rat but do I have a box with ... IDE hd connectors and which of the umpteen hard drives sitting in my rat hole would have it...
I would choose DB2 everyday and twice on Sunday compared to MySQL
My mischievous self sometimes wishes that you could star just a portion of someone's chat messages... so you could take things wildly out of context.... "The real fun was the day after 9/11"
This probably is a simple one but I cant get my head around it.
I have a table of MemberBusinessCats which contains a BusinessCatID and a MemberID.. the table could be presented like this:
+-----------------------+-----------------+------------+
| MemberBusinessCatID | BusinessCatID | Memb...
@Zane I hate MySQL's for their stupid, kind of compliant query syntax
I can run a similar query across SQL Server, DB2, Oracle no problem. MySQL not so much. What error do I receive? "Something's wrong with the query, LOL"
@bluefeet I mean that a "1 new answer" banner appeared, and by the time I was able to click it, there was already an up-vote. That doesn't make sense to me. Nobody could have read the answer that fast.
To ensure that entities created by different DbContexts are be the same it is necessary that they share a common ObjectContext which can be passed in through the DbContext constructor.
The way to create a common ObjectContext using the Code First approach is via a DbModelBuilder - the recommende...
I'm exporting from SQL Server to Excel, but Export Wizard is erroneously reading my nvarchar source column data types (i.imgur.com/ymxtnKc.png). They should not be 202, whatever that means. I'm not sure why the Export Wizard would be incorrectly reading the columns' data types considering that they're coming straight from SQL Server..
@Zach assuming your DDL doesnt declare at 202 which is why you're wondering. perhaps export does a scan of a representative population of the data and that's the longest it found? i dunno. @billinkc may know better
@Zach The Import/Export wizard is great when it works. Otherwise, crack open VS. And Excel is a wonderful data exploration tool but as a data transport system I hate it with the fury of a thousand suns.
The person who decided to make Excel a source and destination instead of CSV, well there's a special level of hell reserved for them. One that Dante wrote about
@bluefeet I see scottsdaleaz.gov/Page2166.aspx They don't seem to be a big citrus eater here in Louisiana, but I think we have mainly Norway rats - they definitely DO love MREs or NATO Field Rations. Whatever we have in the roof does climb the lines, though. They come in during the cold weather. I probably need to put poison out again.
@CadeRoux i read that Louisiana was ranked as one of the most unhappy places in the US based on Tweets because of their love for profanity. perhaps that's the reason?
@CadeRoux we had to seal every little opening with steel wool and then cover it with chicken wire to prevent them in the house. Then outside we have traps with peanut butter
@billinkc There was a product which let you share data sets and analyze them - it had a free viewer but the main product was pretty slick. Would be nice if there was a standard. CSV is too vague - lots of tools have problems with it and it doesn't have any metadata. Excel at least has SOME metadata and formatting, but it's a tool of Satan, of course.
@swasheck I think that study was flawed because it's simply based on use of language on twitter. There have been other studies about happiness and we come out tops. nytimes.com/2009/12/22/nyregion/22nyc.html?_r=0
@swasheck I really don't understand the point of those studies. Are they expecting to improve the happiness in the unhappy areas through some kind of changes or investment? Or let people know to just move away from unhappy areas because they are susceptible to natural unhappiness disasters? We'd be better off spending money on more volcano warning systems.
(That's a joke about Bobby Jindal.)
Because there definitely is some "scientific" research which is a waste of money.
@CadeRoux the purpose is to get and spend grant money on a useless project that uses fancy visualizations (d3) which the member(s) parlay into a job somewhere else
@swasheck A couple years ago, PBJ mocked funding a volcano monitoring project at the USGS, then there was a volcanic eruption like two weeks later: indecisionforever.com/blog/2012/08/27/…
I've run into the practical default limit in postgresql, and it is 8 joins. Anything over 8 joins in postgres, and you're playing with fire.
More specifically, join_collapse_limit defaults to 8. The planner refuses to reorder joins if the query excedes join_collapse_limit joins. I suspect pla...
You can use the PIVOT function to get the result.
The way I would recommend doing this would be to write a hard-coded version of this first, then convert it to dynamic sql.
Note: I determined the cost per month by dividing the Actual Cost by the number of days per between the projectstart/pro...
I have many schemas on mysql instance running on my development pc. I've noticed that mysql is taking a large amount of ram and cpu time.
Is there a way to "disable" schemas of project I'm not working for?
I like to avoid to drop schema and then re-import when I need it. I'm searching to a way ...
MGRP SRNO LOC AdmitDtm shift1 shift2
Mode of Arrival 5 Ambulance 2013-01-02 0 1
Mode of Arrival 5 Ambulance 2013-01-02 1 0
Mode of Arrival ...
The ideal solution would be to use 32 separate TinyInt fields. TinyInt supports values 0-255. If you try to use a single field and do bit processing you lose the ability to index any of these flags just as you would if you used Bit type fields.
I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the following errors
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, pa...
This probably is a simple one but I cant get my head around it.
I have a table of MemberBusinessCats which contains a BusinessCatID and a MemberID.. the table could be presented like this:
+-----------------------+-----------------+------------+
| MemberBusinessCatID | BusinessCatID | Memb...
DECLARE @x TABLE (CREATEDATE VARCHAR(32), CREATETIME VARCHAR(32));
INSERT @x VALUES
('20120101','142304'),
('20120101','44427');
SELECT CONVERT(DATETIME,
CREATEDATE + ' ' + STUFF(STUFF(RIGHT('00'
+ CREATETIME, 6), 5, 0, ':'), 3, 0, ':'))
FROM @x;
So in your case,
UPDATE dbo.UsersTemp S...
