| SQL Database Hosting Solutions? Posted: 14 Jul 2013 05:30 PM PDT I have a client who is running a local SQL Server (for 2 computers) for a client application. They are considering upgrading the local DB to a hosted database. In all the places i look Amazon RDS, Azure, etc, all the prices seem to be expensive. Their database has about less than 1,000 records. Any suggestions? Am i looking in the wrong places? |
| SQL Server 2005 onwards- Capturing Baseline Posted: 14 Jul 2013 10:30 AM PDT I want design a process by which I can capture the baseline data of multiple SQL servers( 2005 onwards) in a single database from where I can get all the info and alerts ..as of now I want to capture the info about below counters: 1) Free space available on disks 2) Database File and Log space available 3) Wait statistics 4) CPU and memory counters Is there any free tool that I can refer to and modify if needed or any useful article that can help me to set up the same. As of now I am referring to the article by Erin Stellato in SQLServerCentral.com. Any help would be greatly appreciated. Thanks Subhro |
| Finding rows for a specified date range Posted: 14 Jul 2013 02:08 PM PDT I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate: CREATE TABLE `pupilgroupteacher` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `groupid` smallint(5) unsigned NOT NULL, `startdate` date NOT NULL, `teacherid` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `date` (`groupid`,`startdate`), KEY `teacher` (`teacherid`), KEY `group` (`groupid`), CONSTRAINT `fk_pupilgroupteacher_2` FOREIGN KEY (`groupid`) REFERENCES `pupilgroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_pupilgroupteacher_1` FOREIGN KEY (`teacherid`) REFERENCES `employee` (`personid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin Having a teacher ID and a month, I need to find all groups for which this teacher worked (or is going to work) at least one day in the given month. Note: For a given teacherid there may be more than one startdate (if, for example, a teacher worked for a group, was replaced with another teacher and then replaced back on a later startdate). |
| How does Log shipping knows to keep track Posted: 14 Jul 2013 12:39 PM PDT I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012). At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours and below are my questions that I need to understand : - How does the log shipping process knows from when to start the logs it is shipping?
- Does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
- Can i be sure that no information was lost in these few hours ?
|
| GeomFromText in trigger has error Posted: 14 Jul 2013 05:50 PM PDT I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ;
when I insert a row with this query: INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`) VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', 'POINT(-0.23 169.93)');
this error has occurred: #1416 - Cannot get geometry object from data you send to the GEOMETRY field
|
| How does Log shipping knows to keep track [duplicate] Posted: 14 Jul 2013 01:21 PM PDT This question already has an answer here: I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012), At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping, The databases first backup-restore took a few hours and i wish to know: - how does the log shipping process knows from when to start the logs it is shipping? does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
- Can i be sure that no information was lost in these few hours ?
Thanks |
| How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5? Posted: 14 Jul 2013 02:57 AM PDT By data integrity, I mean: - Schema integrity
- Data (record level) integrity
Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be reliable. Another option is to parse the XML file generated by mysqldump. Since the file is too large (in GBs), is it worth it? Any other way you have confirmed it? |
| School database design Posted: 14 Jul 2013 02:56 AM PDT I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind. These are the assumptions: - The school has many students from grade 1 to grade 6.
- Each grade is divided into classes (e.g. we have grade 1 A, grade 1 B, grade 1 C) due to the class size.
- Grade 1 to grade 3 offer the same subjects, but from grade 4 to grade 6 users can select which courses to offer.
- Many teachers teach ONLY one subject BUT can teach MORE THAN one grade.
As an intermediate programmer, it all got twisted. I was able to create the following tables and columns students{(id),(name),(name),(parent_id)} -student details parents{(id),(name),(email),(phone),(address)} - parent details teachers{(id),(name),(email),(subject_id)} - teachers details subjects{(id),(name),(description)} - subject details attendance{(date),(status),(student_id)} - attendance of student with id of student_id, status in boolean TRUE = present, FALSE=absent
This is how far I have gone. Now I want to show a table where a student can be linked to all the subjects he/she is taking and a teacher linked to all the grades (no need to specify classes) he is teaching. How can I do that? I will appreciate it if I can be pointed in the right direction. |
| Postgres database locked up Posted: 13 Jul 2013 10:10 PM PDT I was running a process to update a bunch of rows in postgres database and it locked up. Now I can't select anything out of it. None of my processes are "idle in transaction" when I execute ps auxwww|grep ^postgres Also the output of my locks select is: -[ RECORD 1 ]------+---------------- locktype | relation database | 16384 relation | 11000 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 3/4 pid | 18655 mode | AccessShareLock granted | t -[ RECORD 2 ]------+---------------- locktype | virtualxid database | relation | page | tuple | virtualxid | 3/4 transactionid | classid | objid | objsubid | virtualtransaction | 3/4 pid | 18655 mode | ExclusiveLock granted | t
but I'm not sure what I can with that info. I also restarted my db and no luck. Any ideas? |
| Check existence with EXISTS outperform COUNT! ... Not? Posted: 14 Jul 2013 07:26 PM PDT I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. Yet in several recent scenarios I've measured a performance improvement when using count.
The pattern goes like this: LEFT JOIN ( SELECT someID , COUNT(*) FROM someTable GROUP BY someID ) AS Alias ON ( Alias.someID = mainTable.ID )
I'm not familiar with methods to tell what's happening "inside" SQL Server so I was wondering if there was a unheralded flaw with EXISTS that gave perfectly sense to the measurements I've done (could EXISTS be RBAR?!). Do you have some explanation to that phenomena? EDIT: Here's a full script you can run: SET NOCOUNT ON SET STATISTICS IO OFF DECLARE @tmp1 TABLE ( ID INT UNIQUE ) DECLARE @tmp2 TABLE ( ID INT , X INT IDENTITY , UNIQUE (ID, X) ) ; WITH T(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master.dbo.spt_values AS S ) , tally(n) AS ( SELECT T2.n * 100 + T1.n FROM T AS T1 CROSS JOIN T AS T2 WHERE T1.n <= 100 AND T2.n <= 100 ) INSERT @tmp1 SELECT n FROM tally AS T1 WHERE n < 10000 ; WITH T(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master.dbo.spt_values AS S ) , tally(n) AS ( SELECT T2.n * 100 + T1.n FROM T AS T1 CROSS JOIN T AS T2 WHERE T1.n <= 100 AND T2.n <= 100 ) INSERT @tmp2 SELECT T1.n FROM tally AS T1 CROSS JOIN T AS T2 WHERE T1.n < 10000 AND T1.n % 3 <> 0 AND T2.n < 1 + T1.n % 15 PRINT ' COUNT Version: ' WAITFOR DELAY '00:00:01' SET STATISTICS IO ON SET STATISTICS TIME ON SELECT T1.ID , CASE WHEN n > 0 THEN 1 ELSE 0 END AS DoesExist FROM @tmp1 AS T1 LEFT JOIN ( SELECT T2.ID , COUNT(*) AS n FROM @tmp2 AS T2 GROUP BY T2.ID ) AS T2 ON ( T2.ID = T1.ID ) WHERE T1.ID BETWEEN 5000 AND 7000 OPTION (RECOMPILE) -- Required since table are filled within the same scope SET STATISTICS TIME OFF PRINT ' EXISTS Version:' WAITFOR DELAY '00:00:01' SET STATISTICS TIME ON SELECT T1.ID , CASE WHEN EXISTS ( SELECT 1 FROM @tmp2 AS T2 WHERE T2.ID = T1.ID ) THEN 1 ELSE 0 END AS DoesExist FROM @tmp1 AS T1 WHERE T1.ID BETWEEN 5000 AND 7000 OPTION (RECOMPILE) -- Required since table are filled within the same scope SET STATISTICS TIME OFF
On SQL Server 2008R2 (Seven 64bits) I get this result COUNT Version:
Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#492FC531'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 81 ms. EXISTS Version:
Table '#492FC531'. Scan count 1, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#455F344D'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 76 ms. |
| Constraint based rules engine Posted: 14 Jul 2013 10:03 AM PDT I am evaluating software systems which configure products that tout constraint based versus rules based engines. Can someone explain (with maybe an example or two) to a non-expert what the difference is? My guess is that a constraint based engine is rules defining relationships between objects, and rules based engines are more like the WHERE clause of a SQL language query. |
| Oracle schema migration to new database with zero downtime Posted: 14 Jul 2013 07:03 AM PDT I have a problem: I have two production schemas on one database each serving two different appplicaitons. I need to migrate (move) one schema to an entirely new database that will be dedicated for that application with zero downtime. SO EXPORT/IMPORT can not be used. I have license to even active data guard. But if I setup data guard from current database DB to new DB, should I switch over OR failover or what else so that the current primary will continue to be primary in its role and only the new DB will change its role from standby to fully operational primary. Then we simply direct the application to new and start the new db. Thanks for your help. |
| MySQL HDD activity with empty process list Posted: 14 Jul 2013 01:04 PM PDT What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app. Thanx. |
| How do I use subquery on the same table in MySQL? Posted: 14 Jul 2013 06:03 AM PDT I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could create an index for (value_was, value_now) so I could do something like DELETE FROM TABLE WHERE ID1 IN (SELECT ID1 from TABLE where value_was IS NULL) OR ID2 IN (SELECT ID2 FROM TABLE WHERE value_was <= value_now);
This table doesn't have primary key. It has two composite keys. And I guess I cannot use the same table in subquery, but how do I improve the performance of the first query? Thanks very much any suggestion would be much appreciated. |
| InnoDB Tablespace critical error in great need of a fix Posted: 14 Jul 2013 05:03 AM PDT |
| Getting "BadImageFormatException" when attempting to connect through Visual Studio to Oracle Posted: 14 Jul 2013 05:04 PM PDT I get the following message: "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed." when I attempt to create a connection from both Visual Studio 2008 and 2012 to Oracle from my Windows 8 64bit workstation. I'm not running a web app when this message happens. I'm only trying to establish an Oracle connection with Visual Studio. Any solutions posted on the internet that I have seen that resolve this issue with others, doesn't seem to apply to my circumstances. - I did not have this issue when I was using Widows 7 64bit.
- I have set the Active Solution Platform to both Any PC and x86 when I have a solution open, but I still get the same message with either setting.
- I have the latest Oracle 32 bit Client since 64 bit Oracle client won't work with 32 bit Visual Studio.
- At this point, I am only trying to connect to Oracle from Visual Studio and not run a web application. So IIS settings have nothing to do with this issue.
My environment is - Windows 8 64bit
- Visual Studio 2012 and 2008
- Oracle 32 bit Client. But also tried 64 Bit Client.
- Oracle 11.2.0.3 database.
- Active Solution Platform was tried with both Any PC and x86.
Any suggestions would be appreciated. |
| phpMyAdmin Error After Upgrade to 3.5.8.1 Posted: 14 Jul 2013 06:23 AM PDT Im Nubie in mysql, I use that only for PHP. to the point, I try to upgrade myphpadmin 3.5.8.1, after I that, I cant accses my database and have error like This: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE db_name = 'happy2' AND table_name = 'foto' ORDER BY version DESC' at line 1
My Other ALL DB have error like that. Anyone can help me? Im very Appreciated for your answer. |
| SHOW TABLE STATUS very slow on InnoDB Posted: 14 Jul 2013 03:04 PM PDT Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ million records across 400+ or so tables. The problem comes with software like PHPMyAdmin runs "SHOW TABLE STATUS FROM dbase;" where it can take up to 50 seconds to execute that command. Is there a way to optimise this? (MySQL or MariaDB) Thanks! |
| Syncronize mysql databases between local and hosted servers automatically Posted: 14 Jul 2013 04:03 AM PDT We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging database. after that one is works we can move to live database. That one is need to be fully automatically synchronize so that developer dont need to run that tool each and every time |
| SELECTing multiple columns through a subquery Posted: 14 Jul 2013 08:03 AM PDT I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT MAX(comDate) FROM comments WHERE petid=pet.id) AS lastComDate, (SELECT userid FROM comments WHERE petid=pet.id ORDER BY id DESC LIMIT 1) AS lastPosterID FROM pet LEFT JOIN comments ON pet.id = comments.petid WHERE userid='ABC' AND deviceID!='ABC' AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH);
Basically, I am trying to get the lastComDate & lastPosterID from the same row - the row which is the latest one in comments for the specific pet. Please suggest how can I get them in an efficient way. The above query works, but seems overkill as same row is fetched twice. Moreover, the ORDER BY clause is significantly slower than the aggregate function - as I found while profiling query. So, a solution avoiding sorting would be appreciated. |
| Designing Simple Schema for Disaggregation of Demand Forecast Posted: 14 Jul 2013 02:04 PM PDT I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw Material > Work in Progress > End Product). - Orders are placed at each level.
- Number of orders shall be viewable in weekly buckets for the next 6 months.
- Demand forecast can be done for each product level.
- Demand forecast for any week within next 6 months can be done today.
- Demand forecast is done for weekly buckets, for the next 6 months.
Demand Forecast is usually done at the higher level in hierarchy (Raw Material or Work in Progress level) It has to be disaggregated to a lower level (End Product). There are 2 ways in which demand forecast can be disaggregated from a higher level to lower level: - User specifies percentage distribution for end product. Say, there's a forecast of 1000 for Work In Progress.. and user says I want 40% for End Product 1 and 60% for End Product 2 in bucket 10.. Then for 10th week (Sunday to Saturday) from now, forecast value for End Product 1 would be 400 and, for End Product 2 would be 600.
- User says, just disaggregate according to orders placed against end products in Bucket 5, and orders in bucket 5 for End Product 1 and 2 are 200 and 800 respectively, then forecast value for EP1 would be ((200/1000) * 100)% and for EP2 would be ((800/1000) * 100)% of forecast for 'Work in Progress'.
Forecast shall be viewable in weekly buckets for the next 6 months and the ideal format should be: product name | bucket number | week start date | week end date | forecast value | created_on
PRODUCT_HIERARCHY table could look like this: id | name | parent_id __________________________________________ 1 | raw material | (null) 2 | work in progress | 1 3 | end product 1 | 2 4 | end product 2 | 2
ORDERS table might look like this: id | prod_id | order_date | delivery_date | delivered_date
where, prod_id is foreign key that references id of PRODUCT_HIERARCHY table,
How to store forecast? What would be a good basic schema for such a requirement?
My idea to select orders for 26 weekly buckets is: SELECT COUNT(*) TOTAL_ORDERS, WIDTH_BUCKET( delivery_date, SYSDATE, ADD_MONTHS(sysdate, 6), TO_NUMBER( TO_CHAR(SYSDATE,'DD-MON-YYYY') - TO_CHAR(ADD_MONTHS(sysdate, 6),'DD-MON-YYYY') ) / 7 ) BUCKET_NO FROM orders_table WHERE delivery_date BETWEEN SYSDATE AND ADD_MONTHS(sysdate, 6);
But this will give weekly buckets starting from today irrespective of the day. How can I convert them to Sunday to Saturday weeks in Oracle? Please help designing this database structure. (will be using Oracle 11g) |
| SQL Server BPA 2008R2 Posted: 14 Jul 2013 12:04 PM PDT I have been using SQL server BPA for getting good information from Microsoft. I was using its 2005 version in which i used to export the results in csv format but recently I got two new servers which has got SQL Server 2008 R2 installed on it and I know I can't run BPA 2005 on these, so chose the R2 version of it, but it doesn't have an option to save the report on csv format, only xml, I have tried the excel to convert it into csv but no use, even-though it display the details but can't narrow down the results any idea of converting the results to csv format? |
| MySQL optimization - year column grouping - using temporary table, filesort Posted: 14 Jul 2013 04:04 PM PDT I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date (tran_date). Even though it is using the index it creates temporary table which is taking more time because of the temporary table and the filesort. Is there any way to optimize the query to improve the query time? SELECT COUNT( * ) AS cnt, CASE WHEN MONTH( tran_date ) >=3 THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 ) ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) ) END AS financial_year FROM `transactions1` WHERE tran_date >= '2010-06-01' GROUP BY financial_year Showing rows 0 - 4 (5 total, Query took 1.2095 sec) id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE transactions1 range PRIMARY,tran_date tran_date 8 NULL 346485 Using where; Using index; Using temporary; Using filesort Keyname Type Unique Packed Field Cardinality Collation PRIMARY BTREE Yes No tran_date 205720 A tran_ID 617162 A coupon_No BTREE No No coupon_No 617162 A account_typeBTREE No No account_type 3 A prodCode BTREE No No prodCode 430 A tran_date 308581 A tran_date BTREE No No tran_date 205720 A cust_ID BTREE No No cust_ID 3265 A tran_date 308581 A account_type 308581 A points_earned 617162 A Update : Tried adding partition which is not that much helpful in comparison with non partitioned one. Does replication help in this case for reading this table?. There will be more grouping based on the dates (using the date functions) when reading the data. Edit: I altered the query and reduced the query execution time. The query I used is, SELECT SUM( count ) FROM ( SELECT COUNT( * ) AS count, CASE WHEN MONTH( tran_date ) >=3 THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 ) ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) ) END AS format_date FROM transactions1 GROUP BY tran_date ) AS s GROUP BY format_date Showing rows 0 - 4 (5 total, Query took 0.5636 sec) id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 229676 Using temporary; Using filesort 2 DERIVED transactions1 index NULL tran_date 8 NULL 617162 Using index But when using SELECT COUNT( * ) AS count, CASE WHEN MONTH( tran_date ) >=3 THEN concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 ) ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) ) END AS format_date FROM transactions1 GROUP BY tran_date Showing rows 0 - 29 (229,676 total, Query took 0.0006 sec) gives less time without using the SUM(count) in the derived table. Is there any other way to get the sum without using the subquery in MySQL or can the subquery be optimized to get the index. |
| oracle local partition index Posted: 14 Jul 2013 05:31 AM PDT I have a table like this: CREATE TABLE employees (employee_id NUMBER(4) NOT NULL, last_name VARCHAR2(10), department_id NUMBER(2)) PARTITION BY RANGE (department_id) (PARTITION employees_part1 VALUES LESS THAN (11) , PARTITION employees_part2 VALUES LESS THAN (21) , PARTITION employees_part3 VALUES LESS THAN (31) );
Are these statements the same? CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL; CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL ( PARTITION employees_part1, PARTITION employees_part2, PARTITION employees_part3 )
Thanks, |
