|
You can do exactly what you want to do, probably without much pain. If I am understanding you properly, I've done what you are describing a thousand times over. I write SQL which generates SQL very frequently. Unless I greatly misunderstand you, you're just missing a crucial piece of how to go about this.
|
# ? Jan 5, 2009 21:09 |
|
|
# ? Jun 8, 2024 06:09 |
|
Atimo posted:What I'd like to do is setup the t-sql that's creating the string into a sproc call, populate a table of table names to run it against, get it all back as one big lump table, then have a bit of .net code or something write each row back out to a file, but without the line breaks its worthless. code:
code:
code:
|
# ? Jan 5, 2009 21:11 |
|
Victor posted:You can do exactly what you want to do, probably without much pain. If I am understanding you properly, I've done what you are describing a thousand times over. I write SQL which generates SQL very frequently. Unless I greatly misunderstand you, you're just missing a crucial piece of how to go about this. Well it boils down to code:
'line1 line2' It looks fine in Management Studio in text mode sure, but thats not all that usefull, but for now I'm going to have to run this against all 100 or so tables, copy & paste each out by hand, then go back and hand edit a portion of the outputted code. If I could keep the line breaks, I could cut the steps to just the hand edit per file. (Which is still a bitch and a half, thanks boss) That make any more sense?
|
# ? Jan 5, 2009 21:16 |
|
What are you using to query the database? It seems like there may be a setting there that is causing the problems, not the database itself. If worse comes to worse, you could always replace the line breaks with some delimiter that isn't used anywhere else, like '|' or something, and then parse the results and replace '|' with a line break.
|
# ? Jan 5, 2009 21:32 |
|
Jethro posted:What are you using to query the database? It seems like there may be a setting there that is causing the problems, not the database itself. /facepalm Thanks, I got so frustrated with the breaks that that didnt even occur to me. If only I'd thought of that 3 hours ago. Well at the pace this is taking me I can still add in the delimiters, scrap what I have, and still be doign better then without it.
|
# ? Jan 5, 2009 21:36 |
|
This may be of use: http://luke.breuer.com/time/item/SQL2005_print_and_nvarchar(max)/449.aspx
|
# ? Jan 5, 2009 21:47 |
|
Victor posted:Curious. I'm not aware of any such issues with our triggers, but I have not personally worked with the more complicated scenarios. What I will say is that it is nice to be able to write normal insert/update statements to do things instead of executing sprocs that necessarily have to operate one-at-a-time, at least until we get table-valued parameters in SQL2008. I don't know what the TVP support situation is for other databases. Having to operate through a web service seems even more gross. Kyte from Oracle is able to explain my aversion to triggers far better than I can. I ran into a similar situation where I used them to maintain denormalized data similar to Kenneth Downs first example. Triggers have their place, such as setting watermark fields or simple checks, but any 'business logic' (I loving hate that phrase, but it works) should be explicit. Aside from simple architecture preference there can be some wonky things that happen with triggers and transactions. I can't remember the code the the ORA errror, but my previous employer get them by the bucket full.
|
# ? Jan 6, 2009 00:17 |
|
Xae posted:Triggers have their place, such as setting watermark fields or simple checks, but any 'business logic' (I loving hate that phrase, but it works) should be explicit.
|
# ? Jan 6, 2009 00:50 |
|
90% of the poo poo I know about Oracle is from AskTom <3 tom kyte
|
# ? Jan 6, 2009 00:51 |
|
Kyte's argument really seems to boil down to "people are too dumb to write triggers correctly and therefore triggers should not be used much". Either I'm missing something, or my company mysteriously avoids most of the problems he notes, most of the time, with minimal frustration in the process. Methinks I will need to do some asking around and see if my current view of things is correct.
|
# ? Jan 6, 2009 04:09 |
|
Victor posted:Kyte's argument really seems to boil down to "people are too dumb to write triggers correctly and therefore triggers should not be used much". Either I'm missing something, or my company mysteriously avoids most of the problems he notes, most of the time, with minimal frustration in the process. Methinks I will need to do some asking around and see if my current view of things is correct. That is now how I understood his article at all. He seems to be saying that if you have to use triggers to enclose some business logic, you're doing something wrong. Then he presents some common situations where people use triggers and how you can get the same result without triggers. That seems totally valid to me. I cannot think of any situation where it actually makes sense to use a trigger in your application. It *will* come back and bite someone in the rear end when they're trying to debug a problem or modify the code somewhere down the line. I've used triggers to help debug and keep track of what/when/how the data in a table is changing, but that type of activity is outside the actual application and isn't part of the business logic of anything.
|
# ? Jan 6, 2009 04:24 |
|
At my shop, we just assume there are triggers. Thus, there's no "oh poo poo, what happened" moment. Kenneth Downs explains the benefits of supertight coupling in the article I posted above. So far, we've really benefited from that coupling ourselves. We don't do stuff in triggers that is not roll-back-able, but honestly, if the email sends are in sprocs, and execution goes through them before the transaction is rolled back, the same problem happens. I don't want to be an rear end and all, but "you're doing it wrong" is most of what comes to my mind. Dumb people don't know that you can use SQL Server Profiler to examine what's going on in triggers. Profiler is one of the best pieces of software on this planet IMHO, because it complements TSQL and SSMS so loving well. Want to script an action in SSMS? Just fire up Profiler, do the action in SSMS, and BOOM, your dream is reality.
|
# ? Jan 6, 2009 04:41 |
|
More links on people discussing triggers in a non-retarded, non-trivial way would be much appreciated.
|
# ? Jan 6, 2009 05:13 |
|
The issue is less about when the original team is working on the project and more about 5-10 years later. Databases tend to long outlive the application built on them. I do a bit of digging in old databases, and time after time some 'clever' trigger solution is the cause of problems later on. Oracle's mutating table error causes even more headaches. The intent is to tell people they are bumping into ACID problems, the reality is that they come up with convoluted schemes to circumvent it.
|
# ? Jan 6, 2009 06:25 |
|
Is there a more elegant way of doing what I'm trying to do here? SELECT (some stuff) FROM Game WHERE (TimeLimitBunit = 'MINUTE' AND TIMESTAMPADD(MINUTE,TimeLimitB,LastMove) < NOW()) OR (TimeLimitBunit = 'HOUR' AND TIMESTAMPADD(HOUR,TimeLimitB,LastMove) < NOW()) OR (TimeLimitBunit = 'DAY' AND TIMESTAMPADD(DAY,TimeLimitB,LastMove) < NOW()) What I really wanted to do was just this: SELECT (some stuff) FROM Game WHERE TIMESTAMPADD(TimeLimitBunit,TimeLimitB,LastMove) < NOW() but it doesn't work, presumably because TIMESTAMPADD receives 'MINUTE' rather than MINUTE as its first argument and doesn't know what to do.
|
# ? Jan 6, 2009 20:29 |
|
Hammerite posted:Is there a more elegant way of doing what I'm trying to do here? code:
|
# ? Jan 6, 2009 20:38 |
|
SQL: SELECT tValues.itemValue, tValues.date FROM tValues INNER JOIN tItems ON tItems.ID = tValues.itemID WHERE tItems.systemItem = 6 This simple sql statement that gives me this: code:
code:
|
# ? Jan 8, 2009 17:20 |
|
jovial_cynic posted:SQL: SELECT tValues.date, avg(tValues.itemValue) AS avg_itemvalue FROM tValues INNER JOIN tItems ON tItems.ID = tValues.itemID WHERE tItems.systemItem = 6 GROUP BY tValues.date ORDER BY 1
|
# ? Jan 8, 2009 17:25 |
|
var1ety posted:SELECT tValues.date, avg(tValues.itemValue) AS avg_itemvalue aha! It's the GROUP BY that I was missing. I couldn't* figure out how to keep it from just averaging everything together. Thanks! jovial_cynic fucked around with this message at 17:45 on Jan 8, 2009 |
# ? Jan 8, 2009 17:41 |
|
I had a small query to do today and I was thinking through it and wanted the opinion of the much more experienced DBAs here. I have a table Language, with columns Key, Language, Value. It provides a way to support internationalization for a bilingual website I'm doing, inspired by .NET's resource files and GetString call. I needed to provide a dump of the text for a translator, so I ended up doing the following: code:
I decided to use a self-join because I wanted to show the data of two records of the table in one record on the output. However, while this query definitely does not scale well as each new language would require another self-join. I'm wondering if I'm using self-joins in the appropriate way (to merge multiple records of a table into one record) and if there's a better way to be doing this type of query.
|
# ? Jan 9, 2009 19:30 |
|
chocojosh posted:I had a small query to do today and I was thinking through it and wanted the opinion of the much more experienced DBAs here. You can do it in a single scan using a pattern like this: code:
|
# ? Jan 9, 2009 19:40 |
|
So this is probably a pretty easy question, but I'm new to SQL and just messing around with some stuff. Anyways, I want to do something like this with mySQL: code:
|
# ? Jan 9, 2009 21:04 |
|
Plinkey posted:So this is probably a pretty easy question, but I'm new to SQL and just messing around with some stuff. code:
|
# ? Jan 9, 2009 21:20 |
|
Victor posted:More links on people discussing triggers in a non-retarded, non-trivial way would be much appreciated. I have a problem about compiling visiting statistics, and I wonder if triggers could prove to be an efficient way of solving this. I have a main table that relates: | sensor_name | user_name | in_time | out_time | I compile statistics for users that combine one or more sensor_names in one report. I calculate total number of visits, last visit time, total length of visit, and also average time spent. I use php to do these calculations and for around 200,000 tuples, I manage to generate webpage in a bit under a second. The largest bottleneck here, I believe, is that I retrieve all related tuples from the main table to compute this, so its a poo poo ton of data to process, and I do it every time somebody loads a page, or changes some option in the report. One solution, that I think will work well, is to use more tables that contain some precompiled information, such as | sensor_name | user_name | total_visits | last_time | total_visit_time | This way I have to read out a lot less tuples from SQL. However now I have a problem of how to efficiently and correctly generate and maintain such tables. I could modify stat table each time I add new tuple to main logging table in my php script, but I could also use a trigger. Which way would be better? And what other thoughts do you guys have on compiling visiting statistics in general?
|
# ? Jan 10, 2009 03:34 |
|
Remember that you are fundamentally doing caching. I think Rico Mariani has a good post detailing how a lot of programming can be reduced to caching (and people would be better off if they were explicit about this), but I couldn't find it. Another way to think of this is to think of memoized functions. You're calling a function that returns data from the DB and you need to memoize the result, or a portion of the result. Does this make sense?
|
# ? Jan 10, 2009 03:40 |
|
Victor posted:Remember that you are fundamentally doing caching. I think Rico Mariani has a good post detailing how a lot of programming can be reduced to caching (and people would be better off if they were explicit about this), but I couldn't find it. Another way to think of this is to think of memoized functions. You're calling a function that returns data from the DB and you need to memoize the result, or a portion of the result. Does this make sense? How exactly do I memoize php function? I read all the tuples from the DB only once per each script invocation, so memorization wouldn't help. But it would if I could have a persistent data structure between php script's invocation. Is that even possible? Would it make sense to use session variables for this?
|
# ? Jan 10, 2009 03:45 |
|
First think about you want to do, divorced from the technology you are forced to use. Just think, theoretically, about what you're doing. You're running some sort of map/reduce on a large body of information, and it's costly. This large body of information is continually getting new pieces of data, so you can't just memoize in a dumb way. One option is to always maintain an up-to-date value, but that could be expensive. Another option is to maintain values for all days prior to the given day. I hinted at this earlier: this "prior day" thing is really memoizing part of the function's return value.
|
# ? Jan 10, 2009 03:49 |
|
Tell me whether the query I have constructed is a reasonably sensible one for the task I want MySQL to do. Is there a far simpler way of doing this?code:
|
# ? Jan 10, 2009 04:28 |
|
As far as I can tell, you need to group three different ways, so what you have is probably the minimum complexity possible. The only potential issue is if MySQL sucks rear end at executing that query, which would not surprise me in the least. MSSQL would have no problems whatsoever.
|
# ? Jan 10, 2009 04:48 |
|
Victor posted:First think about you want to do, divorced from the technology you are forced to use. Just think, theoretically, about what you're doing. You're running some sort of map/reduce on a large body of information, and it's costly. This large body of information is continually getting new pieces of data, so you can't just memoize in a dumb way. Can you explain better what you mean by the last paragraph? Do you mean running a script at the end of the day to compile stat values for this day? I guess this could be a good solution since there is only so many visits a day that could occur, and it wouldn't be too expensive to combine the daily information. Is this what you mean? Also the stat table i described is what you seem to be describing, only I wasn't thinking of it in daily terms. Is this correct? Is this really memoization though? You are still doing SQL queries to get that information, you just do a lot less of them. Can you also comment on the trigger approach? If I add a trigger to add/modify a tuple in stats table each time i add/modify a tuple in actual log table, is that better than making explicit SQL queries to do same thing? hexadecimal fucked around with this message at 06:22 on Jan 10, 2009 |
# ? Jan 10, 2009 06:19 |
|
Nightly batch processes are extremely common in the software world, so yes, at midnight or sometime thereafter, you would update the daily table so that when you do your aggregation, you'll take a value or values from the daily table, and only the current day's worth of values from the detail table. The cost of using triggers is keeping a value up-to-date. If there are possible concurrency concerns (such as lots of people updating the detail table), then you run the danger of locking the aggregate values while doing an update, have that take a nontrivial amount of time, and end up locking people out of selecting those aggregate values until your update is finished. Wow, that's a long sentence -- if you don't understand, I can elaborate. I suggest you first read up on memoization and some different flavors of it. Then, give me links of what you read and I'll try to fill in any gaps.
|
# ? Jan 10, 2009 06:27 |
|
Victor posted:Nightly batch processes are extremely common in the software world, so yes, at midnight or sometime thereafter, you would update the daily table so that when you do your aggregation, you'll take a value or values from the daily table, and only the current day's worth of values from the detail table. That makes sense. I understand what memoization is, I just don't have much experience with SQL, so I wasn't sure what is efficient way of doing this, but it makes more sense now. Another question. Is it faster to sort in SQL or php? That is, i can use ORDER BY when selecting tuples out of stats page, or I can read those tuples into PHP objects and use quick sort to do the job. Which one would be more efficient? From what I have tried it is quick sort in php but that was without stats table. vvvv Well I was compiling stats and sorting in like one SQL statement from the log table, and it was loving slow compared to just reading all tuples in php and sorting using my own sort algo. hexadecimal fucked around with this message at 21:27 on Jan 10, 2009 |
# ? Jan 10, 2009 06:38 |
|
Unless you have a really really really good reason to do otherwise, sort in MySQL.
|
# ? Jan 10, 2009 21:25 |
|
I've got a table tblFoo that's like thiscode:
select count(userID) as numberOfUsers, dateAdded from tblFoo where dateAdded < '2009-01-03' ... which will return numberOfUsers -> 4. But since I'm graphing this, I'd want the query to give me the total for each day. However, I don't want this: code:
code:
If this requires a join with a table that contains the date range, good. Because I already have the SQL for that handy.
|
# ? Jan 11, 2009 00:23 |
|
hexadecimal posted:vvvv Well I was compiling stats and sorting in like one SQL statement from the log table, and it was loving slow compared to just reading all tuples in php and sorting using my own sort algo.
|
# ? Jan 11, 2009 00:47 |
|
quote:If this requires a join with a table that contains the date range, good. Because I already have the SQL for that handy. code:
Are you using MySQL or something else (read: better)? Jethro fucked around with this message at 00:53 on Jan 11, 2009 |
# ? Jan 11, 2009 00:49 |
|
Jethro posted:That probably means you don't have good indexes on your tables. Can you elaborate? I didn't have any primary key in my table because it wasn't needed, however in the log table, the whole tuple could be considered a complex key, but i don't think its possible to create one in MySQL. Should I just add a primary int key, that is unique for all tuples but is not really meaningful? Would that speed up the operators?
|
# ? Jan 11, 2009 00:58 |
|
hexadecimal posted:Can you elaborate? I didn't have any primary key in my table because it wasn't needed, however in the log table, the whole tuple could be considered a complex key, but i don't think its possible to create one in MySQL.
|
# ? Jan 11, 2009 01:05 |
|
Jethro posted:I mean, what are you sorting by, and create an index (not necessarily a key) on those columns so that MySQL can then use that index when you ask it to sort. I sort by different criteria, but index is a primary key right? that is only applicable to one column in a table?
|
# ? Jan 11, 2009 01:06 |
|
|
# ? Jun 8, 2024 06:09 |
|
Jethro posted:
I'm using mysql. Running the following query: code:
Anyhow, it gives me this: code:
|
# ? Jan 11, 2009 01:14 |