|
Triple Tech posted:I don't know much about database design, but I'm not convinced the standard stored procedure benefit still comes through with dynamically generated SQL. It's like eval'ed code in a programming language, it's not semantically relevant up until the point that it's run, because it can always change before then. This isn't something predictable like bind parameters. If you dynamically assemble a query in your client code and use bind variables, that should keep re-parses low, assuming the query has a relatively limited number of forms it can assume and your implementation supports shared cursors. If you dynamically assemble the query in a stored proc, then you will hard parse every time, at least in Oracle. If, on the other hand, using a stored procedure allows you to avoid dynamically building a query at all, you get the benefits of low parse costs as well as keeping your query in the database where it's easier to maintain. For most users though, ease of maintenance and not performance should be the paramount concern, because things like parse costs don't matter until you're running your DB box to the limit. There's no one best way to maximize that, just use what fits your environment. If starting from scratch I recommend keeping all queries in stored procs, but that's just my biases talking.
|
#
?
Aug 22, 2008 23:16
|
|
|
|
| # ? May 15, 2024 10:15 |
|
|
stack posted:When creating a intermediate table in MySQL is it better to have a PRIMARY KEY index on both values or individual indexes for each column? Or to MySQL is there even a difference? Which indexing scheme will be faster depends on how you access the data and what the distribution of values is. If you usually access the table with only one or the other of the two keys specified, separate indexes on each column is a good bet (in addition to uniqueness enforcement as above). If you only ever access it by one of the keys, e.g. always where A= and never where B=, then you only need to index that column, and a unique index across (key_you_use,other_key), in that order, would suffice.
|
|
#
?
Aug 22, 2008 23:30
|
|
|
Triple Tech posted:I don't know much about database design, but I'm not convinced the standard stored procedure benefit still comes through with dynamically generated SQL. It's like eval'ed code in a programming language, it's not semantically relevant up until the point that it's run, because it can always change before then. This isn't something predictable like bind parameters. There is tremendous overhead in executing a SQL statement. Most of the work goes into finding out what the statement is and what it wants, not actually getting the data (unless you are pulling tons of rows). A DMBS will pre-parse and pre-plan queries in a stored procedure. This means that when they execute half the work is already done. Think about all the rules for SQL, when you feed it dynamic statements it has to check them to make sure it is valid. Then it has to plan out the joins and filters, figure out what indexes to use what method to do the join and only after all that is done, can it actually read the data. My first job was rewriting Pro-C into PL/SQL. Simply sitting on the database and not having everything fed through as dynamic statements resulted in orders of magnitude better performance. It took processes from days to hours. In addition many Stored Procedure languages have custom keywords and operations that are designed to be stupid fast, like Oracle's "BULK COLLECT". Xae fucked around with this message at 16:02 on Aug 23, 2008 |
|
#
?
Aug 23, 2008 15:58
|
|
|
Ok, heres a stupid one, but sql is not my strong suit. How do I drop all tables that start with a given string? (specifically table prefixes with drupal...)
|
|
#
?
Aug 25, 2008 16:20
|
|
|
Hive Mind posted:Ok, heres a stupid one, but sql is not my strong suit. How do I drop all tables that start with a given string? (specifically table prefixes with drupal...) There's no real easy, database-agnostic way of doing this. Honestly if I were doing this I'd just get a table listing, pop that into my favorite text editor and paste 'DROP TABLE' in front of each of them and then run that script.
|
|
#
?
Aug 25, 2008 16:26
|
|
|
No Safe Word posted:There's no real easy, database-agnostic way of doing this. Honestly if I were doing this I'd just get a table listing, pop that into my favorite text editor and paste 'DROP TABLE' in front of each of them and then run that script. I've been exporting, perling it up with regex, and then re-importing it. Its not pretty but I can still automate it so its not that bad I guess. Whats the command to get all tables again? Edit: I only need to do it in mysql.
|
|
#
?
Aug 25, 2008 16:30
|
|
|
Hive Mind posted:I've been exporting, perling it up with regex, and then re-importing it. Its not pretty but I can still automate it so its not that bad I guess. Whats the command to get all tables again? Why are you doing this? If you coded something that dynamically creates tables to store data for entities (like if you add a user then that user gets his/her own table, and when the user is deleted their table is deleted) then that is what really needs to get fixed as it is a very bad design. Oh yeah, the command is 'show tables'
|
|
#
?
Aug 25, 2008 16:43
|
|
|
I have a drupal installation with the whole multisite setup, which means that a bunch of sites with different dns names all run off of the same code base. I'm also running them all into the same database and just telling each drupal to append a table prefix to any table it wants (the prefix is the site name, like drupal_example_comvariables would be the variables table for that site). So now I'm trying to automate some stuff, and one of the things I need to do is clean out all tables related to a site from the database. Each site can add its own tables after I install it for extra modules, so I can't just drop a static list of tables, I need to delete all tables that start with that string.
|
|
#
?
Aug 25, 2008 16:55
|
|
|
Hive Mind posted:I have a drupal installation with the whole multisite setup, which means that a bunch of sites with different dns names all run off of the same code base. I'm also running them all into the same database and just telling each drupal to append a table prefix to any table it wants (the prefix is the site name, like drupal_example_comvariables would be the variables table for that site). Ahh.... This sounds like you might be a lot better off making a new database for each site. That actually makes a lot more sense if you can. I would rather have hundreds of databases instead of one database with thousands of tables. That way you can just "drop databasename" instead of removing all those tables. Or if you need to move a site to a different server you aren't copying tables one by one, you can just move the database. Say one site becomes really high volume, you can just move that one database to its own server without a lot of fuckery. If a database gets corrupted it will only bring down one site. If you want to secure each site, this is a lot better too. With one database if the password is compromised, someone messes up a module, or what not, only one site database will get fried. The only way to secure it now would be to setup security on a table by table basis which would be a real pain in the rear end. I am sure there are lots more reasons too.
|
|
#
?
Aug 25, 2008 19:20
|
|
|
poo poo. Well thats not gonna happen. I guess its back to perl... Thanks.
|
|
#
?
Aug 25, 2008 19:33
|
|
|
Can I do an IN() with multiple values as an expression? Like SELECT * FROM `Table` WHERE ("foo", "bar") IN `Value` ? As opposed to SELECT * FROM `Table` WHERE `Value` IN ("foo", "bar")
|
|
#
?
Aug 27, 2008 22:36
|
|
|
ijustam posted:Can I do an IN() with multiple values as an expression?
|
|
#
?
Aug 27, 2008 23:12
|
|
|
Is this a good place to put an Access question? Basically, I'm dynamically creating linked tables in VBA. There's an Access front-end application, and an SQL Server backend. I've been leaping around with ADOX and connection strings and provider strings and data sources and I just don't know what the "best practice" method of doing it is. For example, what's the real difference between using a DSN and not using one? Any assistance appreciated!
|
|
#
?
Aug 28, 2008 07:16
|
|
|
toby posted:No, but if you're using MySQL, you can use REGEXP - http://dev.mysql.com/doc/refman/5.0/en/regexp.html I haven't done it with MySQL, but I know that MSSQL 2005 lets you run multiple values in a single IN() statement. It's not very fast when you start throwing around a bunch of values, but it works.
|
|
#
?
Aug 28, 2008 13:57
|
|
|
genki posted:Basically, I'm dynamically creating linked tables in VBA. There's an Access front-end application, and an SQL Server backend. In Access, many things are better done through Access GUI objects and methods. If you are linking tables, you are probaly better off doing it that way. For example, one of my table builders in Access dynamically links to my DB2 DataMart with the DoCmd.TransferDatabase method. As for ADOX VBA, my best practices are: -ALWAYS use explicit Connection objects. VBA has automatic clean up, but your programs will run better if you do it all yourself explicitly: declare an Object (early or late), Create the Object, use it, close it, destroy it (set = nothing). The other reason explicit connections are better is becuase you have way more control over parameters. You for example, with an explicit connection you can set time outs, set persistent security settings, query schemas, set schemas.... -I make my Connection Object a Private module-level variable, and then I create a seperate Sub to open the Connection and set up the parameters. All of my various code uses the same Sub to open the Connection Object (but they each have to close it themeselves). -Put your connection string into Private String Constants at the top of your module.
|
|
#
?
Aug 28, 2008 15:29
|
|
|
disclaimer : I'm not very good at database programming since it's not my background, but this problem might be a little tricky, challenging or interesting to you DB whizzes. In MySQL, I have two tables, one for biological probes and another for the sequences associated with them. They're connected by a foreign key (probe_sequence_id), and the probe sequence table is also hopefully sped up by a hash attribute (that converts the otherwise complicated character string into a simple int). The hash integer will not be unique, but the combination of it and the sequence will be (it's designed to represent the first 31 or so characters of the string - there should be few but some collisions after that) So I want to: a) Insert into the probe sequence table all the sequences on a combination of the sequence and its hash using some mix of joint unique keys and triggers b) retrieve the keys either generated or pre-existing in the table that result from this (but carefully since a single hash may have multiple entries - it has to be the most recent one) and c) use those keys with my other data while inserting into the other table Does anyone know how to do this? the subproblems are retrieving primary keys after insertion, combined unique indexes, and triggers. Any help would be greatly appreciated!
|
|
#
?
Aug 28, 2008 22:12
|
|
|
Have you thought of using a Surrogate key? One that has no "real meaning" but is just a unique number to link the tables?
|
|
#
?
Aug 29, 2008 04:13
|
|
|
Yes, use a surrogate key. Use an auto number integer field as the primary key. This will guarantee that there are no collisions. You can get the primary key using a function that grabs the last insert id after inserting a new record. How you use it depends on what client api you are using for mysql.
|
|
#
?
Aug 29, 2008 13:27
|
|
|
Dr.Khron posted:In Access, many things are better done through Access GUI objects and methods. If you are linking tables, you are probaly better off doing it that way. For example, one of my table builders in Access dynamically links to my DB2 DataMart with the DoCmd.TransferDatabase method. I have no idea what library the Access GUI objects and methods are from, but what I ended up using was basicly the CurrentDb object and TableDef type, from the DAO 3.6 Object library. It was actually very straightforward and apart from some hiccups caused by the upsize_ts column, worked quite well.
|
|
#
?
Aug 29, 2008 20:05
|
|
|
I have a table that has a primary key that is auto incremented. Over time many entries have been deleted, but as you know, the auto_increment value just keeps going. There are like 2200 total rows, yet the auto_increment value is currently on like 3800. I want to reset the auto_increment value so it will begin going through and "filling in the holes". How do you reset the auto_increment value? Googling bring up this query: ALTER TABLE [table name] AUTO_INCREMENT = 1 But whenever I run it, I get the "this query was successfully executed!" message in phpmyadmin, but nothing gets reset. New records get placed with ~3800 values. Even if I go to the "Operations" tab and enter "1" into the text box, it gives me the same success message, but when the page refreshes, the old value of ~3800 is displayed. If I put in a larger value, like 9000, it will work fine. First off, will setting the value to 1 actually do what I want, or will it just overwrite over the old values and screw up all my data? And secondly, how I actually set the darn value?
|
|
#
?
Aug 29, 2008 20:56
|
|
|
nbv4 posted:I have a table that has a primary key that is auto incremented. Over time many entries have been deleted, but as you know, the auto_increment value just keeps going. There are like 2200 total rows, yet the auto_increment value is currently on like 3800. I want to reset the auto_increment value so it will begin going through and "filling in the holes". I believe there are database-specific ways of telling it to recycle old values (can't guarantee it's supported on all databases of course), but there should be no reason you should need the value of an auto_incrementing field to have any "business meaning" except as an identifying number.
|
|
#
?
Aug 29, 2008 21:05
|
|
|
No Safe Word posted:The short answer is: you shouldn't care what the value of an auto_increment field is. You are completely correct. Though I should add that you shouldn't care that there are gaps in the sequence either. If it really bothers you switch to a logical delete.
|
|
#
?
Aug 29, 2008 21:59
|
|
|
Quick question: I'm using the MySQL .NET connector in C# and trying to add parameters to a command using something like the following for the command text: ....STR_TO_DATE('?value1/?value2/?value3 8:00 PM','%c/%e/%Y %h:%i %p',?value4); ?value4 comes across fine when I use mysqlcommand.Parameters.AddWithValue, but the other 3 don't. I assume it's because of the single quotes, what's the proper way to use a parameter within single quotes? Escaping it somehow? I've already tried \? with no success.
|
|
#
?
Aug 30, 2008 22:24
|
|
|
SHODAN posted:Quick question: I'm using the MySQL .NET connector in C# and trying to add parameters to a command using something like the following for the command text: In Oracle you would use concatenation. code:
|
|
#
?
Aug 31, 2008 01:31
|
|
|
nbv4 posted:I have a table that has a primary key that is auto incremented. Over time many entries have been deleted, but as you know, the auto_increment value just keeps going. There are like 2200 total rows, yet the auto_increment value is currently on like 3800. I want to reset the auto_increment value so it will begin going through and "filling in the holes". The purpose of a primary key is to uniquely identify a record, and therefore its value must always be unique. When you reset the initial value of auto_increment to one, while (primary key) values larger than 1 are still present, there is obviously going to be duplication. To prevent this, mysql requires that no current primary key value is equal to or greater than the new initial auto_increment value.
|
|
#
?
Sep 1, 2008 04:19
|
|
|
e; got it.
The March Hare fucked around with this message at 14:55 on Sep 1, 2008 |
|
#
?
Sep 1, 2008 04:29
|
|
|
I am trying to do a select on a table where i grab only rows from the current day (in a datetime field) and also check for a matching id. I thought CURDATE() was the solution but that doesnt seem to be helping. This is on MySQL btw. Anyone know how to do this?
|
|
#
?
Sep 1, 2008 22:58
|
|
|
sct posted:I am trying to do a select on a table where i grab only rows from the current day (in a datetime field) and also check for a matching id. I thought CURDATE() was the solution but that doesnt seem to be helping. This is on MySQL btw. Anyone know how to do this? You need to match where the date part of the datetime field is equal to curdate(). A datetime field also has the time of day, so the only ones that will match curdate() will be datetimes from midnight. Look in the mysql manual, there are functions to pull the date part out of a datetime.
|
|
#
?
Sep 2, 2008 17:27
|
|
|
Begby posted:You need to match where the date part of the datetime field is equal to curdate(). A datetime field also has the time of day, so the only ones that will match curdate() will be datetimes from midnight. Yep, I was able to do it with SELECT * FROM table WHERE DATE(date_time) = CURDATE() Thanks.
|
|
#
?
Sep 2, 2008 20:19
|
|
|
Here's a generic problem that I see keep coming up, but I don't know of the best way to solve. Many solutions will work for small tables, but I've seen this come up with tables in the tens of millions of records. I'm going to leave out a lot of fields to try and simplify as much as possible. Table A: Employee ID, Unit Produced, DateTime Table B: Employee ID, Supervisor It's a simple query to determine the total production for a Supervisor. One day Supervisor Bob quits and Supervisor John replaces him. When you update the Supervisor in Table B, all previous production will show up as belonging to John even though Bob was the supervisor at the time. I can think of a couple of solutions, but I don't really like any of them. 1. Add a StartDate and EndDate field to Table B then also join on A.Date between B.StartDate and B.EndDate. Perhaps I'm overthinking it, but this seems like it will be very innefficient when you get to large tables. 2. Create a Supervisor table and add SupervisorID to table A. This becomes even more complex when you add in multiple layers of management. I've seen this go as far as 6 levels at times as well: Employee -> Lead -> Supervisor -> Manager -> Sr. Manager -> Director. It seems to me like there should be a way to keep the employee data seperate from the prouduction data in a space saving manner without significantly increasing the run time or complexity of the queries... or perhaps it's just wishful thinking.
|
|
#
?
Sep 2, 2008 21:55
|
|
|
Aredna, the fastest way to do what you want is probably to maintain an insert-only (no updating) table that stores (employee_id, lead_id, ...). Let's call that table Supervision_Hierarchy_Historical. You'd have supervision_hierarchy_historical_id in Table A, and probably in Table B. This solution is a bit ugly, but it does do a pretty good job of conserving space and time. If you want to conserve more space than that, I think you'll have to lose out on time.
|
|
#
?
Sep 2, 2008 23:59
|
|
|
Can someone tell me how count(*) works exactly on a MS SQL 2005 server. I assume/hope there is a clever way to determine the number of rows in a table and it doesn't just go through and count every single one. edit: I suppose I should also mention the above situation doesn't have a predicate Cooter Brown fucked around with this message at 02:02 on Sep 3, 2008 |
|
#
?
Sep 3, 2008 01:48
|
|
|
Aredna posted:Here's a generic problem that I see keep coming up, but I don't know of the best way to solve. Many solutions will work for small tables, but I've seen this come up with tables in the tens of millions of records. It seems to me that the employee ID should never get reused for a new employee. i.e., if Bob quits then his ID gets retired and John gets a new ID. The bob record would still stick around but be marked as no longer employed or something. This way all the history would still be linked to Bob. That makes the most sense to me unless I am misunderstanding your table structure/question.
|
|
#
?
Sep 3, 2008 13:21
|
|
|
Begby posted:It seems to me that the employee ID should never get reused for a new employee. i.e., if Bob quits then his ID gets retired and John gets a new ID. The bob record would still stick around but be marked as no longer employed or something. This way all the history would still be linked to Bob. Begby, I think the problem is as follows: You have employee Sue (ID 1) who is supervised by Bob (ID 2). Thus in the supervises table you have a record (1, 2) -- Employee 1 is supervised by Employee 2. When Bob is replaced by John (ID 3), if you update the record (1, 2) to become the record (1, 3), then you've lost the fact that Employee 1 was formerly supervised by Employee 2. I like Victor's approach, I'd only have two points to modify to that: 1) Why do we only have one datetime for supervision? I guess the end date of supervision would be either the start date of a new supervisor record or when the employee quits? What if an employee gets promoted to be president and thus has no supervisor? 2) "supervision_hierarchy_historical_id in Table A, and probably in Table B." I think that may be backwards? What I'm understanding is that supervision hierarchy historical id is a surrogate key in table B and may be in Table A (if you often do queries on "list employees and their current supervisors").
|
|
#
?
Sep 3, 2008 13:35
|
|
|
You're right -- supervision_hierarchy_historical_id could go in Table B to avoid having to pick the last one for a given employee every time we need to insert into Table A.
|
|
#
?
Sep 3, 2008 18:52
|
|
|
is it possible to restore a MySQL databasae from the data folders alone? I have the directory tree in tact for the data folder of a MySQL database that I need to restore. I don't have a dump file, though. I copied over the data folder, then ran a show tables and it worked. However that's pretty much as far as I can interact with the database. Can't do selects, drops deletes etc. Furthermore dumping the db does not return any sql for any tables.
|
|
#
?
Sep 4, 2008 16:18
|
|
|
Lamb-Blaster 4000 posted:is it possible to restore a MySQL databasae from the data folders alone? I have the directory tree in tact for the data folder of a MySQL database that I need to restore. I don't have a dump file, though. Here is the first result from a google search for 'restore mysql database from data folder' http://forums.mysql.com/read.php?22,47078,51428
|
|
#
?
Sep 4, 2008 18:24
|
|
|
Aredna posted:Here's a generic problem that I see keep coming up, but I don't know of the best way to solve.... That said, I typically use history tables for this. Let's say you have a table employee_manager_map that has employee_id, manager_id, and whatever else. You then create a table employee_manager_map_hist and use a trigger (or application code if you prefer) to insert old values into the _hist table with a date when the values in the main table change. Then create a stored function like get_manager_as_of(employeed_id, date) that tells you who any given employee's manager was at any given time. As long as you give your history table an incrementing synthetic key and an index on (employee_id, create_datetime), it's quite fast. HERE'S THE BIG "IF": This approach is designed for reporting. There's no particular reason why you can't use it "online" but I make no guarantees that it maintains an OLTP-suitable level of performance and/or data integrity. If I needed my application itself to be history-aware in this way I would probably go with the between-dates approach as above. Your other suggested approach of storing the supervisor-at-the-time on the unit production table is also perfectly viable. More generally, I've yet to see a really elegant solution to problems like this that are induced by change-over-time type processes.
|
|
#
?
Sep 5, 2008 00:34
|
|
|
MoNsTeR posted:
I have no clue how the far the research has advanced but wanted to point this out: http://en.wikipedia.org/wiki/Database_normalization#Sixth_normal_form
|
|
#
?
Sep 5, 2008 05:13
|
|
|
|
| # ? May 15, 2024 10:15 |
|
|
Thanks for all of the great feedback everyone. I'm definitely thinking about this from a reporting perspective and should have mentioned that to being with as well.
|
|
#
?
Sep 5, 2008 14:37
|
|