Tag Info

New answers tagged

1

First of all, depending not just on the number of rows but also on the total size of your table, it may not be excessive to create a new Foreign Ken, especially if scheduled properly. However, it may well be too heavy a change. In that case, you could consider creating an Intersection Table like: CREATE TABLE FlightPair ( departurekey INT REFERENCES ...


1

Hi you can do it like this: SELECT o.number, pd.Product, pd.Component, o.Quantity, o.Date FROM ProductDetail pd INNER JOIN Order o ON o.Product = pd.Product as per the comment I would advise following this tutorial


1

Another way, using the LAG() function: WITH x AS ( SELECT hotel_id, day, LAG(day, 3) OVER (PARTITION BY hotel_id ORDER BY day) AS day_start, 2 * SUM(price) OVER (PARTITION BY hotel_id ORDER BY day ROWS BETWEEN 3 PRECEDING ...


0

A simple union with an order should be enough to achieve your results. And this will give more consistent results if your source tables aren't correctly ordered. All be it possibly with worse performance. select c1 as Results FROM t1 union select c2 as Results from t1 order by Results asc


0

select c1 as c3 from T union select c2 as c3 from T


0

this should do the trick if you are using T-SQL select C1, C2, a.Chuck from testa right join (select C1 as Chuck from testa UNION ALL select C2 as Chuck from testa) as a ON a.Chuck = testa.C1


4

You can do much with window functions here. My first draft was half intended to be base for a materialized view, half for direct use (see version history). Now updated to present two optimized queries for each case (with / without materialized view). Also improved in multiple details, much faster, yet. Test case Building on this table: CREATE TABLE ...


-1

if reqtime is inserted as now(), you can use now()-max(reqtime), otherwise Pieter's answer is great.


4

This SQL works fine in SQL Server (with appropriate syntax modifications): UPDATE Visits SET Duration = TimeDiff( ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1) ,R.ReqTime ) FROM Visits R ; Unfortunately, the syntax hits a limitation of MySQL that an updated table cannot be referenced (again) ...


3

SELECT hotel, totprice FROM ( SELECT r.hotel, SUM(r.pricepp)*@spacesd_needed AS totprice FROM availability AS a JOIN availability AS r ON r.date BETWEEN a.date AND a.date + (@days_needed-1) AND a.hotel = r.hotel AND r.spaces >= @spaces_needed WHERE a.date BETWEEN '2014-01-01' ...


0

Here's an example data dictionary script that makes heavy use of table and column descriptions that shows how useful they can be for self documenting, http://www.csvreader.com/posts/data_dictionary.php .


2

SQL Server does not page, not in the sense the OS pages. Data does not need to be paged becase the memory is just a cache of the data files. Data can simply be removed from memory and read back when needed. Caches (eg. Procedure cache) are not paged either. Entries are removed and, if needed, they can be recreated from scratch (eg. compile). The closest ...


13

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


1

If you are doing date-time math in mysql, use mysql's built-in functions. However, if most of your processing is in PHP, then it is much more efficient to store time in the database as INT - do the math as needed, format it once at output time. Many advantages including consistent timezone functions. I generally pre-process my queries in PHP, so the ...


1

First and foremost only ever name the columns you actually need in the select list. Never ever use SELECT *. That said, you may already be in a hole out of which you cannot dig. You could create a view to return only the active rows and "data" columns: create table T(c1 int, c2 int, active bit); create view V as select c1, c2 from T where active = true; ...


2

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


1

SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name) FROM t),3) as percentage FROM t GROUP BY name FIDDLE


0

It depends on your DB type. If you use InnoDB, query cache is not required. Moreover query cache harms the speed of overall performance. Hence, if you use MyISAM for wordpress, you should use it.


2

I'd have a table sit between users and messages with a one-to-one many relationship to both. Call it users_messages for example. Each row in users_messages would describe all the users who have an involvement in any particular message. For example if a message had 3 users involved then the table would have 3 rows for this. Each row linking to the same ...


2

A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO ...


2

Your $date have to be the correct MySQL date format : '2014-08-29 10:03:30' You can use this: $date = date('Y-m-d H:i:s', strtotime($time)); $sqlTemplate = "UPDATE table SET lastTime = %s" ; $sql = sprintf($sqlTemplate, $date); Checkout the formats that can be your $time string : http://php.net/manual/en/function.strtotime.php and ...


3

UPDATE table SET lasttime=TIME_FORMAT(SEC_TO_TIME(((SUBSTRING_INDEX(time,':',1)*60)+SUBSTRING_INDEX(time,':',-1))-480),'%i:%s'); Do the calculation in seconds then back to time,no need for php FIDDLE


0

Incase you need it to deactivate it for a single user, you can add a column named something like DEACTIVE_USER_ID in message table with default value as NULL and make a update for message table when a single user dectaivate it.


1

It seems to me that which db is better is not your primary concern here and instead you have two different considerations which cut against eachother, namely business knowledge vs customer desires. Ultimately that will be a business decision, not a technical decision. Obviously there is opportunity cost as Max has noted in a comment. There is no way ...


0

The immediate cause of the infinite loop is this. Per documentation: Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND. But there's more: CREATE FUNCTION upsert(_ins text, _upd text) RETURNS int AS $func$ DECLARE loop_ct int := 0; row_ct int; BEGIN LOOP IF loop_ct > 1 THEN -- terminate ...


0

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time). Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I ...


0

I would make sure you update stats with the paranoid option. USE db_name; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN'; Outside of that definitely go back and check what the differences are, and discuss them with your virtualization guys


2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database). I dropped all indexes but the PRIMARY KEY, and inserted this new index: ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index ...


1

Normalization is a great concept and essential for well performing relational database. However it can become counter productive. For example you almost never see addresses broken up like this: StateRegionTable CityTable ZipTable StateId CityId ZipId StateDesc CityDesc ZipDesc AddressTable ...


0

Use plain JOIN, not LEFT SELECT SQL_NO_CACHE chars.name FROM chars JOIN inv ON (chars.pid = inv.pid) JOIN institems ON (inv.institem = institems.id) WHERE inv.institem = 10001013730 || institems.container_slot_1 = 10001013730 || institems.container_slot_2 = 10001013730 || institems.container_slot_3 = 10001013730 || ...


2

Use a data-modifying CTE to chain the inserts in a single statement. WITH inskey AS ( INSERT INTO key (key_name) VALUES ($$key_1$$) , ($$key_2$$) RETURNING * -- returns row including newly generated key_id ) INSERT INTO related_key (key_id, related_key_id) SELECT i.key_id, t.related_key_id FROM inskey i JOIN ( VALUES ...


2

You can do this sort of thing by creating what are essentially property bags of these values. This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case. This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and ...


1

The following updates column 'cn' with sequence number starting from 1 DECLARE @id INT SET @id = 0 UPDATE X SET @id = cn = @id + 1 GO http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/


1

From SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 there is support for 15,000 partitions so to be honest, you don't really need to do that much dynamically. Instead of having a complex daily process (Dynamically add filegroup and boundaries) with an opportunity to fail, simply create the partitions up-front from now to the year 2020, and you're well ...


0

Performance is a very fluid concept. It can vary according, for example, to the number of rows in your tables (use of indexes or not). It can depend on whether you use InnoDB or MyISAM. It can depend on what else is occurring on the system at the same time. It can depend on your hardware. And it can certainly depend on your RDBMS. You ...


1

To add a new partition, use SPLIT RANGE. Assuming you have the following partition: CREATE PARTITION FUNCTION pfTest(int) AS RANGE LEFT FOR VALUES (10, 20, 30); CREATE PARTITION SCHEME psTest AS PARTITION pfTest TO ([GRP1], [GRP2], [GRP3]); .. you can add a new partition by "splitting" the last range from (30 to infinity) to (30-39) and (40 to infinity). ...


0

You already answered this yourself: No, there isn't in plain SQL. You can use PL/PgSQL if you want variables, in a function or a DO block. Most of the uses for query variables in MySQL are satisfied by CTEs (WITH queries), window functions, etc in PostgreSQL. Well, actually there is, but they're not suitable for general use within queries. You usually ...


0

For the second example you don't need a variable (neither in MySQL nor in Postgres): select id from client where platformID in (select id from platform where bios like '%INTEL%'); Don't be afraid of sub-queries, Postgres' query optimizer is much smarter than MySQL's. If the above is too slow, rewriting it ...


4

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them ...


0

This is easy to do inside a PL/pgSQL function: create function myfunc() returns void language plpgsql as $$ declare aintconst constant int = -333; arealconst constant real = -9.99; pfid int; clientid int; begin select id from platform where bios like '%INTEL%' into pfid; select id from client where platformID = pfid into ...


1

Information schema vs. system catalogs We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information. The information schema provides standardized views which help with ...


3

Assuming the column you want to change is the one the FKs point to, you need to drop all of the foreign key constraints - it's not enough to simply disable them. You can't drop the table, anyway; and you can't drop the column if it's actually referenced, and the only way to add the IDENTITY property is to drop/re-create the column, or drop/re-create the ...


1

Don't you really want a computed column (as per your title), so you can preserve the original value, and calculate a new one? eg CREATE TABLE dbo.tblTestCheckConstraint ( id INT PRIMARY KEY, NewColumn INT NOT NULL, ccNewColumn AS CASE WHEN NewColumn > 4 THEN 1 ELSE NewColumn END ) INSERT INTO dbo.tblTestCheckConstraint ( id, ...


4

Check constraints can only be used to verify if the data falls within a given set of parameters. The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client. To test this, I created your table, along with your proposed ...


0

select LAST_UPDATE_DATE_TIME as LAST_UPDATE, SCHOOL_CODE, PERSON_ID from SCHOOL_STAFF WHERE STAFF_TYPE_NAME='Principal' AND LAST_UPDATE_DATE_TIME = (SELECT MAX(LAST_UPDATE_DATE_TIME) FROM SCHOOL_STAFF s2 WHERE PERSON_ID = s2.PERSON_ID)


0

CREATE FUNCTION [dbo].[fnSplit] ( @sInputList VARCHAR(8000), -- List of delimited items @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items ) RETURNS @List TABLE (colData VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 BEGIN SELECT ...


1

If a materialized view is already fast refreshable, the DBMS_ADVISOR.TUNE_MVIEW procedure will return a message saying "the materialized view is already optimal and cannot be further tuned" So there is nothing to do.


0

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers. The good news: you do not need parameters for a ALTER TABLE statement at all. final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer"; ... ps = con.prepareStatement(sqlps); ps.execute(); Your column needs a data type. I added ...


1

Use the information_schema views, they're SQL-standard and contain the information you want. You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things. If you want to see how psql executes something like \dt, run psql ...


1

You can use the psql command line client. \dt will show a list of tables \dv will show a list of views \d [object_name] will describe the schema of the table or view Not sure how you would describe a query though. More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/



Top 50 recent answers are included