Tag Info

New answers tagged

1

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1. The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it. The solution to this problem is pg_repack, which does not ...


15

That's because of Instant File Initialization. In short, SQL Server can take advantage of this privilege for database data files (not transaction log files). What this means is that SQL Server does not have to zero out the data file(s) when initializing. Without the "Perform volume maintenance tasks" privilege granted to the SQL Service account, upon the ...


1

The post you found is from 2007. Rather start with the current manual: When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the ...


0

A couple of things you might want to consider: Maintenance of the data. If the data is going to change often then it would be easier and quicker to have the data normalised so you only have to change it in one place and have all the usages of it automatically update. Conversely, if the data hardly ever changes then this is not a consideration. Full ...


0

Well you could attack this in the manner you previously stated, or you could go another route. Typically, in this situation, I would create three tables. Table 1 - Cards: This would contain the basic card information. Table 2 - Types: This would be one record for each possible type (i.e. one record for green, one for blue, one for creature A, one for ...


2

Use a Sequence. A sequence is a database-wide generator of unique integers that can provide unique surrogate keys.


1

From what I understand, I will try to sketch a simple database schema. I will put the properties that could being customized by the store administrator into different tables from the "immutable" properties. product table contains "immutable" property attributes: +----+--------------+ | id | UPC | +----+--------------+ | 1 | product ABC | | 2 | ...


1

SHORT ANSWER Only as a last resort LONG ANSWER Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer. I have written about this before Sep 18, 2012 : How are multiple indexes used in a query by MySQL? Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2) In essence, MySQL will do lookups along ...


1

If you are testing a and b, INDEX(a, b) is likely to be better. Indexing a flag (by itself) is almost never useful. Please provide SHOW CREATE TABLE and a few WHERE clauses; I will give specific advice. Here's a quick cookbook for building an INDEX that will often be optimal. Given a WHERE with a bunch of expressions connected by AND: List all the ...


2

NOT IN is typically the slowest option. LEFT JOIN / IS NULL is more promising. Or NOT EXISTS: Help with this SELECT in the same table Query Just your query, formatted: SELECT u.id_user, u.username FROM "user" u LEFT JOIN friend_request fr ON fr.sent_to = u.id_user AND fr.sent_from = 288 LEFT JOIN friends f ON ...


0

When a query plan is constructed, MySQL finally decides to use only one of the indexes on the table which means that multiple single column indexes will never be used in a single query. However, if you have multi-column single index which covers all the required fields (coverage index), the database will never have to read from the disc which will make the ...


0

There is no clear answer to your question, as it really depends on the query. BUT: If you like to filter e.g. for two colums a combined index will have a better effect.


0

After doing a lot more investigation this is what I have found. It fixed the issue (significant performance gain and WRITELOG has an average wait time of 0.0126 which was initially 14.681) Apparently the issue was with the Number of Virtual Log files in my physical log file. There is a job scheduled to rebuild indexes every night, the job creates 36GB of ...


1

You don't want to be in the business of constantly dynamically creating a separate table for each store's admin to store their own data in. Instead, use a single table and designate a column to differentiate the admin's customization, organized by store. You haven't given enough details to get into the minutiae of the implementation, but this approach can ...


1

There are other DMVs that can help you quantify this. sys.dm_exec_sessions has columns cpu_time, memory_usage and total_scheduled_time amongst others. While you're running your plan-getting query in one session you can interrogate this DMV in a second session to find how expensive the first is. Pleasingly, you can also quantify how expensive your second ...


1

Install the extension pg_stat_statements with the SQL command CREATE EXTENSION pg_stat_statements You may want to make sure you create this by using an appropriate user (such as the user your application uses or some dba account). Be aware that whichever user creates the extension will also own it. This will require a server restart for it to be usable ...


0

Please run SET STATISTICS IO ON and run one of your queries and check the number of pages beeing read in your message tab. A lower execution time doesn't mean that it was more efficient, there are other dependencies like what else was running at the same time in the background? With number of pages read you can be sure which query uses more or less amount ...


0

Not specifically like AWR, but there are a number of built-in tools that can be deployed. The "mda" tables will be your best friends for identifying poorly performing queries. Rob Verschoor's site has detailed information on how to use them along with some pre-built tools. http://www.sypron.nl/mda.html Official documentation can be found here: ...


0

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex. When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient. For fast queries on ...


0

If you really need to handle millions of veicles, you will need a dba. I mean, SQL server is Ok Oracle too, Postgresql fine. But you will need to know how to index properly, how to partition and when historicize your data. So, opinion based, I suggest you Postgresql: it has a good Geographic data support (this can be a plus for you), it is free and robust.


0

I don't think the database server will be your bottleneck. Many databases can do sub-second responses to queries provided your database is scaled and designed correctly. I think you need to be asking yourself questions about how the android devices will connect (mobile internet can be slow sometimes), how your middle tier logic will work and how the devices ...


0

Merge is not working for CLOB. it is working only for small string values. In the below sql : eai_msg, msg_req_text, msg_req_rply are all CLOB. If I pass data like "something", it works. It is not working for input of 1200+ char: "merge into some_table msg using (select 1 from DUAL) d " + "on (msg.corr_id= :correlationId) " + "when matched ...


2

Why have an id at all? Why not have PRIMARY KEY (user_id, post_id)? Why have user_id and post_id nullable? Shouldn't they be NOT NULL? @jynus is right about a covering index, but if you change the PK as I suggest, that separate index won't be necessary. innodb_buffer_pool_size should normally be 70% of available RAM. I don't see how (pre)caching would ...


0

You have the best index there is. It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data. (To further address all the comments...) Note that it needed to read about 200K rows (of the index) to do the count. That many rows takes time. INDEX(offer_id, ...


0

It cannot be sped up. Look at how much disk space the table uses (several GB, I expect). The query has to read the entire table. That's a lot of I/O. Now, let's go back to the purpose of the query. What is the purpose? What would you do with 100K ids? If you need to do something about each one, then it is easy to devise a way to "find the next id with ...


0

The progress report is very likely to be non-linear. This is because of the way caching and sorting work. Initially, everything can happen in RAM, and this is much faster than on disk. As things progress, I/O needs to be performed. Then the I/O gets more involved. Also, there is an issue of whether it decides to build the index(es) in RAM (in the ...


0

Create an EVENT to periodically UPDATE a stats table that stores the "last_ID" to which each of the 70 rankings was last pulled, and the current counts for each. Each UPDATE on the stats table entries (one per ranking), should join the real table with the entries you are counting, and have the respective criteria, setting stats.count to ...


0

I agree with "bad form". Plan A: Here's another approach when you do need to add another column(s). Create a new table with the same PRIMARY KEY ("Vertical partition"). (But not AUTO_INCREMENT.) Put the new column(s) in it. The JOIN to fetch the new data will be messy in your code, but only when you actually need those new columns. Also, make it a ...


3

You assumption about adjacency is correct. If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between ...


2

When considering the impact of new index, the important things to think are how often rows are added to the table, is the indexed field getting updated (often) and how many distinct values there are in that field (selectivity). For table this size, it's probably not going improve the performance that much, but if you're getting deadlocks it might help. If ...


3

I had another look at this and can reproduce your issue. Try adding OPTION ( MAXDOP 1 ) to your query. In my test rig with a 300MB file this ran in 1 min 42 seconds. The unhinted version ran for 30 minutes at 100% CPU before I killed it. You could also have a look at OPENXML. People often say it's faster with large XML files and it appears to be in this ...


2

I echo the "bad form" comment of @JohnM - design the thing properly, and if you have new requirements (or your design isn't perfect first time - unlikely I know :-) ), then choose to add new fields. Use JSON if it suits your clearly demonstrated requirements, otherwise stick with "normal" field types. I've seen too many systems where these "spare fields" ...


0

I am doing a lot of work with BLObs, typically ~2MB each. If I do an SQL query and limit the number of rows returned by a simple 'WHERE ID > x AND ID < y' the time of the query is not linearly related to the number of rows returned. What I found looking at the System Monitor was that the memory used increased as time passed thus I surmise increasing the ...


4

In short: No. There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure. When the index name is ...


0

I think the question is about how to restore faster from mysqldump's ceated dump files and not a different backup solution. One of the ways, you can do this is by creating groups of tables in your schema and create a separate DB user for each group and use MySQL permissions to not allow tables to be inserted to using all but one DB user. This is a proven, ...


0

You could split the table into two, moving the Photo column into the new one. The new table would need only 2 columns (id, photo) and 200K rows. CREATE TABLE employees_with_photo ( employee_id INT NOT NULL, -- or whatever the type of employees.id photo BLOB, CONSTRAINT employees_with_photos_pk PRIMARY KEY (employee_id), CONSTRAINT ...


0

At least it looks to me like it's possible that 2 clients update the status of the table to locked at the same time, and if that happens, the one who wasn't able to update it's gCounter into the status gets stuck in here: While (rec_Lock.Fields("LockSemaphore") <> gCounter & "Locked") I'm not a VB expert, so might have also misunderstood the ...


0

Okay, in case anyone stumbles upon a similar problem and Google directs them here: I don't exactly figured out why, but sort of figured out what causes it, phpMyAdmin was the culprit. Issuing the same commands directly in the mysql command line did not cause the temporary tables to be written to disk. Now, I also have phpMyAdmin on my development machine ...


0

If you are running away from MySQL and hoping that PostgreSQL is better, then first consider what type of UUIDs you have. If they are the 'sequential variant' (Type 1), and if you have some clustering by time, then MySQL (or any database) can take advantage of it. Here is a discussion of such: http://mysql.rjweb.org/doc.php/uuid Although MySQL does not ...


1

MySQL While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY ...


0

Cases like this can usually be solved for certain by enabling trace events 10046 (if you have authority to do so), and run tkprof on the resulting trace files: alter session set events '10046 trace name context forever, level 8'; select /* try one */ -- add your SQL here and invoke it; select /* try two */ -- add your same SQL here and invoke it; show ...


1

I have 3 solutions for you: Direct reference of sequence and using concat One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: http://stackoverflow.com/a/17925601/4206293 Using a UUID Another possible solution is, if you don't need you node-id in ...



Top 50 recent answers are included