Tag Info

New answers tagged

0

As the results of the query will be the same for existing data (unless you change the rules), have you thought of storing the results of your query in another table along with the max log_id value from the source? That way, when analysing the next chunk of data, you can add a filter to the query to select only data with log_id values higher than those ...


1

The only way to know is to benchmark it. If your query is fast, the chance of any notable impact on other queries is low, assuming your database server has sufficient disk bandwidth, multiple CPU cores, sufficient memory, and an appropriate innodb_buffer_pool_size setting for both the working dataset and the hardware. If you don't already have a replica ...


0

this query will use your resources including network, CPU, Disk,etc. 20+ query with union is not a good idea. specially when it runs every 10 second!!! It will put lots of load on your hardware and it might reduce your Throughput if your hardware and/or configuration does not cope with the load.


0

Solution to the query: select * from ( select * from ProQzQuizReport order by points desc, time_spend asc) a group by user_id; http://sqlfiddle.com/#!2/65fbf0/9


0

Well, with these Indexes, there shouldn't be any issue. How many rows does your table have ? how many joins you use in your queries, If you're using joins, do those tables have appropriate indexes ? The above mention queries should almost not have any issues. Post the actual queries you're running which are slow. Although, If you still want to go ...


1

I experimented with this and found something very interesting. Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino. If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put ...


0

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder. The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is ...


1

All things being equal, it is logical to assume that better hardware = better performance. However, computers are strange beasts - I know that with Oracle, if you assign very large sizes (you need good hardware) to certain caches, you can actually slow down the machine. The only real suggestion that I have is that you test, test and test again. You cannot ...


6

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


2

The scripts go over all of your tables, rebuild all the indexes and update their statistics. The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable. I'm not sure, though, why you need to run the first command which disables the indexes.. ...


2

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague). ...


0

If the sampling of your dev table (100k) remains consistent with the actual prod table, then the statistics should reveal the same query plan and so the increase in performance should hold pretty consistent. However that depends on making sure hardware is the same AND the load on the system is the same. When you say a 30% increase are we talking query time? ...


2

Have you tried this? SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ] FROM ( SELECT Id, StatusDate, 'Results_201505' as tbl FROM Results_201505 WHERE A = 0, B = 1, C = 3 UNION ALL SELECT Id, StatusDate, 'Results_201504' as tbl FROM Results_201504 WHERE A ...


4

Every time I had to do this, I used the RML Utilities (x86 and x64 package download links can be found on this page). Basically, you just have to set up a server-side trace using one of the templates included in the package. The captured trace can be analyzed using ReadTrace (included in RML Utilities) and it populates a database with query analysis ...


0

Query plan generation is complicated. The number of possible plans grows exponentially with the complexity of the query. Each possible plan will be optimal within a small range of data counts and distributions. Change any one of these, however, and another plan becomes optimal. Say you add an index. This can be used for some range of counts and ...


1

There is no definite answer. It really depends on the data and the change you have performed. For instance, if you created an index that helped in the 100,000 records table, in many cases it will help with the 10,000,000 records table. However, if, let's say, you had a cursor and you've rewritten it to a set based solution, it will be good enough for the ...


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...


0

From a different perspective... SHOW GLOBAL STATUS;, then For SQL counts, divide Com_delete by Uptime to get the DELETE statements per second since MySQL was started. Etc. For actual I/O, divide these by Uptime to get "per second since MySQL started": Key_reads and Key_writes track I/O for MyISAM index operations. Innodb_buffer_pool_reads and ...


2

I don't think you'll be able to get good performance while using OFFSET. The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X. But you do! Assuming this is for some kind of GUI, make a note of the earliest ...


1

There are two ways of determining this. RECENT VERSIONS OF MYSQL One is to use the performance_schema (assuming 5.6 or higher). There are many tables that you can query to get at this information, particularly if you have innodb_file_per_table = 1 in your my.cnf. If you do USE performance_schema there are several tables that can help you to find this ...


1

As in all things SQL Server, the answer is "it depends". A good database developer can design an application to mitigate SQL Server hardware requirements with attention to detail on database design, index/query tuning, and application design (e.g. appropriate data caching). So it seems you may have covered that part if you see index seeks rather than scans ...


2

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow): From the PostgreSQL documentation: from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the ...


1

After thinking about this, I decided to answer instead of comment to provide more detailed information. Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference I suggest you avoid ...


0

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented). I can't think of any performance difference. The plans certainly look so similar that ...


1

IMHO and in the described use-case, you will never use more than one core. The reason is that your workload is IO bound, not CPU bound. As your 3 connections are creating a new Index, each of those needs to read the whole table from disk: this is what is taking time, not computing the Indexes.


1

Answering my own question, as future readers will benefit from it : Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up. After ...


1

You can put the table in KEEP Pool cache and this may have a positive impact on your queries. I have implemented it in my system for a 16 GB partition and we did get good performance gains. However putting a table in cache does not mean that its data is loaded into memory automatically. It only means that since we are using a separate buffer pool for these ...


3

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will: Have the same effect on dead tuples - it physically re-writes the whole table Retains any existing indexes Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering ...


1

It achieves the same effect as a VACUUM FULL except that it can be executed within a transaction block. Of course, any indices or constraints on the original table would be lost in the given example. I would need to know some additional information to help explain the performance increase: Are there any indices or constraints on the original table? Is ...


0

Probably you have an ordinary "cold cache" case. When you INSERT 10,000 rows into a table, the rows need to be added to the appropriate place(s) in the "data". Also, 10,000 entries need to be added to each index's BTree. (How many indexes do you have? Please provide SHOW CREATE TABLE.) If you have AUTO_INCREMENT, then the rows will be "appended" to the ...


0

This most probably has something to do with the new cardinality estimator. See my reply to this question. If performance improves when you change the compatibility mode you have a good chance you are running in the same problem.


4

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc). In other cases, a soft delete is not a performance ...


0

Face it, wherever you have the UUID as a KEY, it will be in a fragmented BTree. But, the BTree is kept reasonably clean. That is, when a BTree block is too full to accept another row, it splits into two blocks, each about half full. As time goes on, any new inserts into either of those blocks will simply add to the blocks without immediately splitting. ...


0

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists. ...


0

You should set optimizer_switch in MariaDB back to the old MySQL 5.5.11 value in my.cnf [mysqld] optimizer-switch = index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on You could login to MariaDB abd set is as well set global optimizer_switch = ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


0

You may use REPLACE INTO. The disadvantage of it is that it creats high IO, as each existing record will be deleted and then inserted (as opposed to being updated). Try loading the new rows' IDs into a separate table on the destination server, then run a delete on the destination joining this new table with the existing table using the ID. After that you ...


0

Using the Database Object Size Functions mentioned above: SELECT primary_key, pg_column_size(tablename.*) FROM tablename;



Top 50 recent answers are included