[how to] Finding stored procedures with missing indexes

---

• Finding stored procedures with missing indexes
• Simple scenario needs to go from Unnormalized Data to 3NF - Review Please
• Saving settings for multiple accounts in SQL Server
• table design to track significant events associated with an item
• I would like to query a range of criteria on multiple columns in MySQL
• mysql_connect() system error :111
• Fast paginated result, for various filter clauses
• Improving performance of SQL Server after migration to cluster servers [closed]
• Type conversion in expression may affect "CardinalityEstimate" in query plan choice?
• RSExecRole missing
• Implementation of stack in MySQL
• Want to Get Brands In Wordpress Database Structure Saved as Custom Post types
• MySQL incredibly slow on WAMP and XAMPP
• PostgreSQL replication for archiving
• column precision exceeded. ORA-01438 is so unmeaningful
• Applying user-defined fields to arbitrary entities in a SQL Server database
• Access denied Error in MySQL and Drupal
• ON DELETE cascade alternate
• Parallel Statistics Update
• Stored Procedures Best Practices: Fenced or Unfenced?
• How to prevent high memory, CPU and time consumption by MySQL restore?
• MySQL Performance Monitoring

Finding stored procedures with missing indexes Posted: 09 Mar 2013 08:13 PM PST How to scan for stored proc that need indexes to be created. Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script or app that can scan to indicate if an index needs to be created?

Simple scenario needs to go from Unnormalized Data to 3NF - Review Please Posted: 09 Mar 2013 07:26 PM PST Here is the simple scenario. It is an animal charity, and at the moment, to track their establishment's costs, they have a spreadsheet like so: 1NF: Am I right in saying that the above is already in 1NF because according to the definition of 1NF: First Normal Form: A relation in which the intersection of each row and column contains one and only one value. Then, I went on to figure out what I would make my primary key. Consequently, went for the following: IncuredCosts (ID, Name, BoughtFrom, DateBought, Cost) 2NF: Am I also right in saying that the above is already in 2NF, because again according to the definition: Second Normal Form: A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key 3NF: Let me start by the definition here: Third Normal Form: A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key. Here I am stuck, I do not know how to get it into 3NF. As it currently stands, it is possible that the Name attribute would duplicate (say they might order some more Dog food) and of course as you can see the Bought From attribute is already repeating. I would greatly appreciate some help. Thank You.

Saving settings for multiple accounts in SQL Server Posted: 09 Mar 2013 07:25 PM PST I'm using SQL 2012 to store the account settings for multiple accounts (each user account will have a set of settings). These settings could have any number of fields (has not been completely set and could grow) but each user account will have the same set of settings (different values of course). Is it better (faster with a smaller footprint and more efficient) to: give each setting its own column and each row to a user? keep in mind that there could be hundreds of settings variables. (i.e. userid,setting1, setting2, setting3...etc) Relate each individual setting to a user and making it a name value pair table (i.e. userid,settingsName, settingValue)

table design to track significant events associated with an item Posted: 09 Mar 2013 08:59 PM PST An Orders table I'm dealing with at present in design phase has about 10 or more columns. Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders could go into the millions. At the moment, I'm aware of about 20 important events to track for each Order, but that number may increase or decrease. Of these 20 or so events, only a handful will always take place, while the rest are just potential events. Also, many - but not all - of these events have at least two points which need storage: the Date the event is supposed to happen (if it is no longer just a "potential" event) the Date the event did happen The events data will be updated in the database from external sources in the form of .xls or .csv files. For all events, it is only possible for the given event to happen once. What would be the best design for storing this milestone event data? Would you try to use a somewhat normalized design, where there is an Events table designed similar to this... CREATE TABLE dbo.Events ( EventID INT , OrderID INT , EventExpectedDate DATEIME2(7) , EventActualDate DATETIME2(7) , EventTypeID INT , EventSkip BIT ... ) Or would you try a less normalized approach and flatten all the possible events out into one very wide events table that mimics what the .xls/.csv update files will look like? CREATE TABLE dbo.Events ( EventID INT , OrderID INT , Event_1_ExpDate DATEIME2(7) /* No, the actual names would not include 1, 2, 3 */ , Event_1_ActDate DATETIME2(7) /* The actual names would name type of Event */ , Event_2_ExpDate DATETIME2(7) , Event_2_ActDate DATETIME2(7) ... , Event_20_ExpDate DATETIME2(7) , Event_20_ActDate DATETIME2(7) ) Or is there another pattern you would use?

I would like to query a range of criteria on multiple columns in MySQL Posted: 09 Mar 2013 12:02 PM PST I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example : We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int I have 1000 records in the table, as an example, I list 5 here. Fast | smart | Sharp ------------------------ 0 1 1 1 1 1 0 0 1 1 1 1 1 0 0 and let's say we wanna search for people who are smart and sharp as 0 1 1 but if there is no record with the value of 0 1 1 we want to get the closest to it which is 1 1 1 or 1 0 1 or etc. So now when I search for it, I get the exact value, if the value doesn't exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of result, how can I sort them from the closets to my query to the feartest ?

mysql_connect() system error :111 Posted: 09 Mar 2013 10:01 AM PST I have 2 VPS. I would like to connect from the frist to the second's mysql db like this : $db = mysql_connect("94.xxx.xxx.xxx:222", "user", "pass"); I got this error: Warning: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in /var/www/pcsart/test.php on line 4 My server is works on first server is works on port 222. In the first server I don't have a mysql. On the second is running on port 3306 First and second server's firewall got these rules : iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT tcp -- anywhere anywhere tcp dpt:mysql ACCEPT tcp -- anywhere anywhere tcp dpt:mysql ACCEPT tcp -- anywhere anywhere tcp dpt:222 ACCEPT tcp -- anywhere anywhere tcp dpt:222 ACCEPT tcp -- anywhere anywhere tcp dpt:mysql Chain FORWARD (policy ACCEPT) target prot opt source destination ACCEPT tcp -- anywhere anywhere tcp dpt:mysql ACCEPT tcp -- anywhere anywhere tcp dpt:222 Chain OUTPUT (policy ACCEPT) target prot opt source destination ACCEPT tcp -- anywhere anywhere tcp dpt:mysql ACCEPT tcp -- anywhere anywhere tcp dpt:222 ACCEPT tcp -- anywhere anywhere tcp dpt:mysql What i'm doing wrong?

Fast paginated result, for various filter clauses Posted: 09 Mar 2013 07:39 AM PST I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I am trying to improve its efficiency (est. 60 calls per second on that query only). So first of all, I read What is an Effective Way to Count the Number of Pages in Oracle?, and the article pointed too, but unfortunately it does not discuss the execution plan of the queries presented at all (I'll get to see them come Monday). Here is the query I had come up with (the table is partitioned on part_code which is a date range): select <all-columns> from ( select rownum as rnum, <all-columns> from ( select /*+index (my_table index)*/ <all-columns> from my_table where part_code in (?, ?, ?) and date between ? and ? and type in (?, ?, ?) and func_col1 = ? /*potentially: and func_col2 = ? and func_col3 = ? ... */ order by date, type, id ) ) where rnum between M and N; /* N-M ~= 30 */ Note: most of the queries will be carried out with a single func_xxx filter, and I think M will be small... but have no guarantee and in theory M can be up to 9999. Note: ~72 partitions in total, but only ~39 active at most, ~300,000 different values of func_col1 (but with some having ~50,000 rows and some having 1 row), ~10 values of type, id is unique (generated through a sequence). It does work, but there is a nasty surprise in the execution plan: the 2nd query (with rownum as rnum) is completely executed, fetching up to ~50,000 rows from the DB, before the pagination kicks in. Fetching ~50,000 rows from the DB to return some ~30 of them seems particularly inefficient. In the execution-plan this shows up as: View \_ Filter (rnum) \_ View <= here comes the trouble \_ Sort \_ ... I can create an index over the table if necessary, and I can transform the existing partitioned index (part_code, func_col1, date, type, id). The index has exactly the order required by my order by clause; but it does not seem to be sufficient (and removing the order by clause does not get me any closer anyway). Is there a way to prevent the "materialization" of the view and have Oracle build it on the fly as the outer query needs more data (ie, moving to lazy evaluation of the inner queries) ?

Improving performance of SQL Server after migration to cluster servers [closed] Posted: 09 Mar 2013 05:00 AM PST Since migrating our database (SQL Server 2008) to Cluster Server, it has become very slow, and we cannot fetch data and reports from it. What are the most important things we should be checking after such a change to get the performance back on track?

Type conversion in expression may affect "CardinalityEstimate" in query plan choice? Posted: 09 Mar 2013 08:36 PM PST I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data. We constraint the events on each table with a check constraint on the datetime column. This allows the optimizer to limit the tables that are searched for queries that filter on the event datetime column. The names of the check constraints were generated by SQL Server, so it's hard to know what they do by looking at their name. I want the constraint names to have the form 'CK_TableName_Partition'. I can generate a rename script using this query and copying data from from sql_text column. The WHERE clause matches check constraints whose names look like they were generated by SQL Server: SELECT checks.name AS check_name, tabs.name AS table_name, skemas.name AS schema_name, cols.name AS column_name, N' EXECUTE sys.sp_rename @objname = N''' + skemas.name + N'.' + checks.name + N''', @newname = N''CK_' + tabs.name + N'_Partition'', @objtype = ''OBJECT'';' AS sql_text FROM sys.check_constraints AS checks INNER JOIN sys.tables AS tabs ON tabs.object_id = checks.parent_object_id INNER JOIN sys.schemas AS skemas ON skemas.schema_id = tabs.schema_id INNER JOIN sys.columns AS cols ON tabs.object_id = cols.object_id AND cols.column_id = checks.parent_column_id WHERE checks.name LIKE ( N'CK__' + SUBSTRING(tabs.name, 1, 9) + N'__' + SUBSTRING(cols.name, 1, 5) + N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2 ) ORDER BY table_name; The output looks like this: check_name table_name schema_name column_name sql_text CK__tbAcquisi__Acqui__5C4299A5 tbAcquisitions_201301 Archive AcquisitionDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5', @newname = N'CK_tbAcquisitions_201301_Partition', @objtype = 'OBJECT'; CK__tbAcquisi__Acqui__76026BA8 tbAcquisitions_201302 Archive AcquisitionDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8', @newname = N'CK_tbAcquisitions_201302_Partition', @objtype = 'OBJECT'; CK__tbAcquisi__Acqui__7D6E8346 tbAcquisitions_201303 Archive AcquisitionDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346', @newname = N'CK_tbAcquisitions_201303_Partition', @objtype = 'OBJECT'; ... CK__tbRequest__Reque__60132A89 tbRequests_201301 Archive RequestDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbRequest__Reque__60132A89', @newname = N'CK_tbRequests_201301_Partition', @objtype = 'OBJECT'; CK__tbRequest__Reque__1392CE8F tbRequests_201302 Archive RequestDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbRequest__Reque__1392CE8F', @newname = N'CK_tbRequests_201302_Partition', @objtype = 'OBJECT'; CK__tbRequest__Reque__1AFEE62D tbRequests_201303 Archive RequestDT EXECUTE sys.sp_rename @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D', @newname = N'CK_tbRequests_201303_Partition', @objtype = 'OBJECT'; The result of the query seems to be correct and the server executes it quickly. But the root node of the execution plan has a warning: Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

RSExecRole missing Posted: 09 Mar 2013 05:08 AM PST I have multiple servers and 4 of them have reporting services installed with the ReportServer & ReportServerTempDB databases. A few days ago I was setting up security for a new login and I found out that the RSExecRole role is missing in my dev server (in the reportserver database). It exists on the ReportServerTempDB, master & msdb databases. I have found a way to create it on the master & msdb databases on msdn but it didn't help me creating it on reportserver with all the securibles & properties similar to the other environents I run. Has anyone encountered this problem before? Can anyone help me with a script and an explanation about this role?

Implementation of stack in MySQL Posted: 08 Mar 2013 11:15 PM PST I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion. The Stack must be a collection of tables with the stack top pointing at a particular table. We may have various such stacks in our DB. The stack length may increase if for example the field from the table is full. Likewise the stack length may decrease in the opposite manner! Could you please give me an implementation idea! is it really possible at first place? Hope I'm clear with my question.

Want to Get Brands In Wordpress Database Structure Saved as Custom Post types Posted: 09 Mar 2013 12:34 PM PST Hello out there here I am having difficulty in fetching the brands per ctegory in the sidebar of my website Here is the description .. I am using the feeds and feed uploader to upload feeds and create brands and product categories in wordpress The problem is there is no link within the product_category and product_brand and I want to show specific brands in the side bar of a specific category not the whole brands list which is going to long down So I tried out these heavy queries to fetch the brands as per category, but using the INNER JOIN made the database too slow and the website keeps loading without showing brands the query is working fine the only thing I want to know is to speed up the queries for the customers so they don't fade away Here are the queries I am using $term = get_term_by('slug',get_query_var('term'),'product_category'); $q5 = "SELECT DISTINCT p1.ID FROM $wpdb->posts p1 INNER JOIN $wpdb->term_relationships tr1 ON (p1.ID = tr1.object_id) INNER JOIN $wpdb->term_taxonomy tt1 ON (tr1.term_taxonomy_id = tt1.term_taxonomy_id) WHERE tt1.term_id = '$term->term_id'"; $q2 = "SELECT tt2.term_id FROM $wpdb->posts p2 INNER JOIN $wpdb->term_relationships tr2 ON (p2.ID = tr2.object_id) INNER JOIN $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id) WHERE p2.ID IN ($q5)"; $q3 = "SELECT DISTINCT tt3.* FROM $wpdb->posts p3 INNER JOIN $wpdb->term_relationships tr3 ON (p3.ID = tr3.object_id) INNER JOIN $wpdb->term_taxonomy tt3 ON (tr3.term_taxonomy_id = tt3.term_taxonomy_id) INNER JOIN $wpdb->terms t3 ON t3.term_id = tt3.term_id WHERE 1=1 AND tt3.term_id IN ($q2) AND tt3.taxonomy = 'product_brand' ORDER BY t3.name ASC"; $brands = $wpdb->get_results($q3); The first two queries runs fine but the last one make the datbase query too slow so any help please

MySQL incredibly slow on WAMP and XAMPP Posted: 09 Mar 2013 08:34 PM PST I've installed WAMP on my new Windows 8 machine and for some reason when I use PHPMyAdmin to import an sql file (it's only about 5mb), it takes several minutes before timing out. And then when I check I can see it's only imported a fraction of the tables. I've tried other sql files and it's the same story. These same sql files import perfectly fine (and in a few couple of seconds) on my Windows 7 machine also running WAMP. I've since uninstalled WAMP and tried XAMPP, and the problem still exists. Even just browsing the databases with PHPMyAdmin takes a long time between page loads. I am guessing it's a problem with MySQL. Any suggestions would be helpful.

PostgreSQL replication for archiving Posted: 09 Mar 2013 04:09 AM PST I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice. I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work. Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either. Thanks

column precision exceeded. ORA-01438 is so unmeaningful Posted: 09 Mar 2013 05:34 PM PST I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not. Let's suppose you have a Java batched prepared statement writing data to database. If one of the columns has a value larger than specified precision it throws ORA-01438 exception. Is there any way to find out which column is that?

Applying user-defined fields to arbitrary entities in a SQL Server database Posted: 09 Mar 2013 07:34 PM PST Currently we have an old (rather crude) system that has user defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and it wasn't really designed to scale well. Our system has around 60 different types of entities, which makes things even more complicated. Essentially the implementation looks like this: USER_DEFINED_FIELDS: UDF_ID int ENTITY_TYPE int UDF_NAME nvarchar(64) UDF_DATA_TYPE int UDF_FORMAT nvarchar(16) UDF_MASK nvarchar(24) UDF_FLAGS int UDF_VALUES_NUMBER: UDF_ID int ENTITY_ID int VALUE int MODIFIED datetime UDF_VALUES_TEXT: UDF_ID int ENTITY_ID int VALUE nvarchar(255) MODIFIED datetime etc... This gets nice and fun when we generate our own ways to index compound primary keys, but that's another DailyWTF-worthy story. Obviously this is pretty hideous, and leads to some spectacularly horrific queries being generated, but it's worked alright for now because we limit each entity to a maximum of 5 user defined fields. As a quick disclaimer, I wasn't with the company when this design decision was made! ;) Anyway, we're about to start a shiny new project and will inevitably need a better way of doing this, with no restrictions on the number of UDFs we can apply to entities, increased performance, and less horror in the generated query department. If we could run a NoSQL solution like Mongo or Redis I'd be happy and wouldn't need to bother you all, but sadly that's not an option. Instead, I need a way to do this from within SQL Server 2008 R2. So far, I've come up with the following options: Individual UDF table for each entity type, with identical structures. Benefits: Queries are faster and simpler, solves the compound index problem, can search by UDF. Downsides: Table versioning is more cumbersome, lots of extra tables, difficult to index across similar entity types (e.g. purchase orders and sales orders) Binary blob UDF column in every entity's table. Benefits: Really fast: no fetch overhead, no extra JOINs. Downsides: Can't search by UDF, extra proprietary code required. XML column in every entity's table. Benefits: Fast like binary blobs, seems to be indexable. Downsides: No idea how they work - looks complicated! So, do any of these ideas have merit? Is there a better alternative?

Access denied Error in MySQL and Drupal Posted: 09 Mar 2013 12:34 AM PST We are trying to deploy a Drupal application on our production server. We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying. We made all necessary corrections in settings.php and also give GRANTs to the webserver to access the database. The webserver and the database server are physically two different machines. We are able to connect to the database server from our web server through the command line. There is no firewall enabled on either server. Any update we make to the database means the site goes down and displays an ACCESS DENIED error. Can any one guide me to resolve this issue? Thanks

ON DELETE cascade alternate Posted: 09 Mar 2013 01:34 PM PST I am trying to create a database for 2 Tables like: Table1 : tb1-id(pk) | tb2-id | other Table2 : tb2-id(pk) | other FOREIGN KEY Table2(tb2-id) REFERENCES Table1(tb1-id) ON DELETE CASCADE Table pseudo-code: CREATE TABLE Table1 ( tbl1-id INT PRIMARY KEY , tbl2-id INT FOREIGN KEY REFERENCES Table2(tbl2-id) ON DELETE CASCADE ... ) Table2: CREATE TABLE Table 2( , tbl2-id INT PRIMARY KEY ... ) There can be 2 records in Table1 that point to a single record in Table2. If we DELETE 1 of the 2 records pointing to Table2 from Table1 MySQL would also DELETE the referenced record from Table2. I want the referenced record to be deleted only when both the referencing records in Table1 are deleted. Is there any other MySQL method/constraint that can fulfill the above requirement.

Parallel Statistics Update Posted: 09 Mar 2013 05:55 PM PST In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update stats.

Stored Procedures Best Practices: Fenced or Unfenced? Posted: 09 Mar 2013 08:12 AM PST I believe I understand the reasons behind fenced and unfenced stored procedures. Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine should there be issues with things like pointers. Unfenced runs "inside" of the database, which means that performance is better. From what I have also researched, SQL PL is always basically unfenced, because it is SQL and therefore cannot access memory like programming languages can. C/C++ and Java procedures can run fenced or unfenced. But since they can possibly access memory, there should be a consideration for running them fenced, unless there is a certainty on the quality of the code to not crash and it needs performance. First of all, am I correct in my understand of the above? Next, is it generally a best practice to start out with all stored procedures (even those defined as SQL PL) as fenced first? Any other best practices for stored procedures, especially as related to fencing and/or security? EDIT: Further research has shown that SQL PL procedures cannot run fenced. Since they do not contain any code that could harm the database engine such as pointers or file I/O, DB2 knows they are safe and runs them inside the engine (ie, unfenced). That being said, I am still looking for best practices regarding all other stored procedures.

How to prevent high memory, CPU and time consumption by MySQL restore? Posted: 09 Mar 2013 04:34 AM PST I have a local test machine with 2 GB RAM and a dual core processor. I imported a dump on that using mysql -uuser -psecret < script.sql script.sql is a 700 MB file which contains a single database with more than 100 tables. Restore took approximately 25 minutes. Question: Why it took so long time? I have already optimized my server settings. The dump also contains extended inserts and disables keys). Is 700 MB is too large for this kind of machine? mysqld was using 100% of one of the CPU when I checked using htop and 22% of the memory. Now what actually I want is to convert all my tables of that database to InnoDB. So for that I executed a simple shell command: mysql -uuser -psecret -Bse "select Concat('Alter Table ' , TABLE_SCHEMA ,'.',TABLE_NAME , ' Engine= \'InnoDB\' ;') from information_schema.tables where ENGINE='myisam' and TABLE_SCHEMA='db_name';" > Innodb.sql so Innodb.sql contains the script for converting tables to InnoDB. Now when I run Innodb.sql one of my table takes more than 20 minutes for conversion. It contains only 1,378,397 records. In total it takes more than 30 minutes. In the meantime memory usage by mysqld daemon was 73%. The CPU usage was OK at this time. How can I minimize the time consumtion? Should I go for changing MySQL server settings or anything else? If anybody wants my my.cnf setting I will share that.

MySQL Performance Monitoring Posted: 09 Mar 2013 02:19 PM PST I already have system availability monitoring for all my MySQL instances in Nagios. I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema stats trend over time, to help identify sources of problems etc. I've seen Cacti exists and seems pretty popular. What have your experiences with Cacti been (pros/cons)? Are there any other common solutions I should investigate?

You are subscribed to email updates from Recent Questions - Database Administrators - Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610