1,403 reputation
622
bio website
location Austin, TX
age 33
visits member for 3 years, 4 months
seen 2 days ago

Sep
15
comment “Transitive” keys
@bob.sacamento: if you're not comfortable reading and writing DDL, that's a strong reason to stick to the simpler solution A. It's a perfectly good and common setup.
Sep
15
revised “Transitive” keys
Added a schema to illustrate the OP's dilemma (if I understand it right)
Sep
15
comment “Transitive” keys
I took the liberty of creating simple DDL scripts for your two scenarios; if I've misunderstood anything, please correct me. I see two issues: the first model is simpler, which is worth a lot. Second, the second model involves a two-barrelled foreign key from the C table to the B table. Offhand, I don't know which RDMSs support such an FK. Of course you could have a single-column field to physically back up what is conceptually a two-column join, but if you end up doing that, what have you gained?
Sep
15
suggested suggested edit on “Transitive” keys
Sep
11
comment Index optimization for table that is truncated every day
A table should almost always have a PK, but there are many cases where a clustered index is not needed. MS SQL unfortunately does not help by conflating the two. There are several good questions here on DBA regarding these issues.
Sep
10
comment Index optimization for table that is truncated every day
Absolutely. My preference is to pull in data in all its ugly rawness first, often into a heap table, then do SQL cleanup before copying it to a new table. Maybe that's just because I'm a SQL guy and everything looks like a nail to me, but SQL is optimized for set-based operations. On the other hand, if the volume of data is huge you may need to do conversions and cleanup on an RBAR basis as you import it via SSIS or whatnot.
Sep
10
comment Index optimization for table that is truncated every day
It's a small thing, but I recommend explicitly defining your PK with an ALTER TABLE statement after your CREATE TABLE. This reminds you to make an explicit choice (including whether to cluster on the PK), puts it next to your other index declarations, and lets you give it a non-random name.
Sep
10
answered Index optimization for table that is truncated every day
Sep
3
comment Almost identical queries but large time difference in execution
Yes, if you want all records from X and records from Y where they exist, you'll use SELECT ... FROM X LEFT JOIN Y ON X.A = Y.A AND Y.B = 'Foo' AND Y.C = 'Bar'; filtering on Y in the WHERE clause will effectively make your join an INNER JOIN. As an aside, I find that consistently using left joins is more readable; I think of it as "start with this first table, then add from this second table."
Sep
3
comment Almost identical queries but large time difference in execution
I'm not sure the RIGHT JOIN is working as you expect. Because you have filters on Credits and Products in your WHERE clause, days with no credits or sales will still be excluded. If that's what you're trying to do, you should put these filters in the join clause.
Sep
3
comment Almost identical queries but large time difference in execution
How long is "too long"? Do you stop it after a few seconds, or minutes?
Sep
2
answered Database design suggestions for a data scraping/warehouse application?
Aug
27
comment Indexing the difference between two columns in MySQL
What are your indexes? A covering index on pk_column including column_x and column_y should be fine. Since there are no filters on your query, you're stuck doing a scan, even if you can index on the difference of the fields.
Aug
25
comment Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
A million rows in a dimension doesn't concern me - the data is only changed once a decade, and a covering index on the PK and two or three most-used fields will take up a trivial amount of server RAM. However, adding half a dozen SMALLINTs to a billion-row fact table is 12 GB plus overhead, and now you're talking real money. For dates that only need to store the date, you can of course point them to the "12:00 AM" record for the appropriate date.
Aug
22
answered Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
Aug
22
comment Data Warehouse design for reporting against data for many time zones
possible duplicate of Handling time zones in data mart/warehouse
Aug
22
comment Handling time zones in data mart/warehouse
Why use separate Date and Time dimensions instead of a single DateTime? A fact table may have several dates, and storing two INTs instead of one for each can add up.
Aug
22
comment Huge differences of performance of MySQL in two servers
Just to be clear: most queries run equally quickly, but a handful of queries are much slower on prod, is that right? Are there specific tables that the problem queries have in common?
Aug
21
awarded  Popular Question
Aug
21
comment How can I use parameters of type Int32 from an environment for an integration services package in a SQL Server 2008 job?
I've found that, in practice, date parameters need to be declared as strings. You can always have two variables in your package, populate one as a string from your parameter, and then calculate the second as an integer from the first.