Tag Info

New answers tagged

0

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows: CREATE VIEW dbo.MyView WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS NumberOfRows FROM [dbo].[Products]; GO CREATE ...


2

Question: Does this mean the query is using indexes? The Using intersect(FK_DataValues_Sites,FK_DataValues_Variables) means that the query is using two indexes on table datavalues with the Index Merge Intersection Access Algorithm and then (Using where) the rows are additionally checked against some other conditions. Question: Any other suggestions? (I ...


0

So the problem was sitting in front of the PC - once again. It turned out that the query was not exactly similar, there was one where condition more in every query. We had different entity framework versions on the two notebooks that created different statements.


0

Your arrow points to the #user seeks column for the clustered index in the report. The execution plan says it is doing a scan on the non clustered index. Are you expecting it to do a seek on the clustered?


2

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


1

I like this presentation by Giuseppe Bianchi. Starting on page 5 it contains a desctiption of the TLS protocol - segment size, header size, HMAC overhead. As for the handshake, the impact on replication should be negligible. It will only occur on connection, and there may be a key exchange going on every hour, depending on the configuration. As for the ...


2

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database: If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added. An ...


1

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause. ...


0

We had a problem with AMD chipset where they would drop into power saving mode, reducing their clock speed by 50%. Worth a look.


1

I think your first idea is better. The second way, as you state, is how you would model the data in an RDBMS. If you're going to use MongoDB for fun, you might as well explore the fact that it has a different data model, and structure your collections accordingly. While I'm sure performance is not going to be an issue for the scale of this project, ...


1

MindArray IPM by MindArray Systems Pros: Response time analysis Server Based RAC support Free Version for basic info Allows a specific time range selection Easy To Use Cons: Not a Open Source


0

You can add a new column to replies, call it position, and fill it with consecutive numbers of replies per thread (the position of the reply in the thread). For example id | thread_id | text | position 1 | 1 | .... | 1 2 | 2 | .... | 1 3 | 1 | .... | 2 4 | 1 | .... | 3 5 | 2 | .... | 2 6 | 3 | .... | 1 ...


0

Look at the query SELECT * FROM replies WHERE thread_id = 1234 ORDER BY id ASC LIMIT 125400,10 /* whoops */ My guess is you are probably forcing MySQL to gather the records with thread_id 1234 and then sorting the rows in id order. You need to give MySQL more help. SUGGESTION #1 : Use a Better Index I would suggest changing the thread_id index as ...


2

Based on your comments, here is a maybe faster version: SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2 WHERE TABLE1.COL1 IN ('1','TEXT','HO TED') AND TABLE1.COL2=1800 AND TABLE1.COL3>=100 AND TABLE2.COL1=10519198 AND TABLE2.COL3=('TEXT') And you need to create a tree-index on ...


4

Main problem is the missing index. But there is more. SELECT user_id, count(*) AS ct FROM treenode WHERE project_id = 1 GROUP BY user_id; You have many bigint columns. Probably overkill. Typically, integer is more than enough for columns like project_id and user_id. This would also help the next item. While optimizing the table definition, consider ...


5

Several things might be going on here and might be difficult to pin down but here's a few things. After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may ...


2

Is this a permanent condition? Or a temporary one? My first guess is that if you crashed midway through the load, Oracle has to go through the instance recovery process once the system comes back up. Part of that will involve undoing the transaction that was in progress when the system crashed. That, in turn, will involve reading the UNDO vector and ...


1

I think you should change your server parameter to optimize server performance, you can go through this Percona Wizard https://tools.percona.com/wizard Also you can refer a blog post http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ this shows what parameter to consider to get optimize server performance. I ...


6

I'd first add an index on (project_id, user_id) and then in 9.3 version, try this query: SELECT u.user_id, c.number_of_nodes FROM users AS u , LATERAL ( SELECT COUNT(*) AS number_of_nodes FROM treenode AS t WHERE t.project_id = 1 AND t.user_id = u.user_id ) c -- WHERE c.number_of_nodes > 0 ; -- you probably want ...


6

mysql should optimize a query like your example and not do a full table scan just to retrieve 10 records. Adding an order by without an index before the limit 10 would perform a full table scan. Are you sure that your "table" is really a table and not a view? You might be selecting from a slow view.


2

If the query has not got any specific ordering, it returns the first 10 rows it receives. But if there is a "WHERE ..." or "ORDER BY ..." clause, it must first get the full resultset and then fetch the first 10 rows. If a simple "SELECT..." with no "WHERE ..." or "ORDER BY ..." clause takes that long, I would probably suggest to ANALYZE the table ...


2

Oracle by default does not cache query and function result, but both cache do exist. http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html The importance of these: there is more than caching blocks. Even if you process everything in memory, why process the same requests again and again, if you already know the answer? What you are ...


1

There is no "result cache". There is the database buffer cache. Oracle stores recently-accessed data blocks in memory so that it doesn't have to go to disk every time for hot blocks - those chunks of data which are most frequently accessed. Function and query results are not stored, only the data blocks which are in demand. See here: ...


0

The first methods slower than the second one most like is because your new table, OrderInformation, does not contain a lookup index for the Order ID column. I believe that if you add an index both of these methods will execute with similar execution times


1

The rownum pseudocolumn is assigned as rows are processed. The predicate rownum = <<x>> will evaluate to false if <<x>> is anything other than 1. Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like Read a row from table1 Evaluate the col2 = ...


0

You have 50+ billion records in a table in MySQL? Not only do I doubt that, but I would also say that if it is true, you probably need to normalize your system. also a possible duplicate of: What are the max rows for MySQL table see Ash's response about horizontal partitioning. This will help cut down query execution times on that large of a system.


2

A capped collection would allow you to maintain a relatively small list, though you would want to have a decent buffer, or be very confident in terms of the size of your documents. The other option would be a TTL collection, but that would be more prone to fragmentation since it will be doing a lot of deletes (this will mean having to deal with said ...


0

Could also use 'Between' as a simpler syntax in this case. Execution plan would probably still be the same.


-1

Usually when performing DML operations, especially ones that affect a high percentage of rows, it is better to disable the indexes, and rebuild them afterwards. This is because most of them time, the engine is trying to reorganize the index after each DML operation. For INSERT and DELETE, it is obvious that a clustered index will be rebuilt after each row. ...


0

It depends on what you mean by improve performance. You can't make that query faster, it needs to scan the table. But you can keep it from using too many resources and blocking other processes by chunking it, which is essentially iterating through batches of rows to perform multiple smaller updates. There must be some option to just write it to the ...


-1

Generally only the first 3 digits are taken into account for the compatible parameter, so either one is fine.


0

Percona has an extremely awesome wizard for initial server configuration files (I've used it on our own extremely busy production servers). It requires registration, but usage is free.


1

It depends on the targeted SQL engine. Mainly because each engine has different approaches when it comes to scanning full tables, and using indexes. If we talk about a non-indexed table or a hint that forces a full-table scan, there is not really a difference between the two examples, because at a lower level they're both filters, and will be treated in the ...


2

InnoDB Architecture Please keep in mind what goes into the InnoDB Buffer Pool 16KB Data Pages for Tables that have been accessed 16KB Index Pages for Indexes that have been accessed Changes to Secondary Indexes (could take up to 50% of buffer pool in a high-write envrironment) MySQL's idea of a JOIN Believe it or not, whether you have one big table ...


0

For 10 values where you have no index, this is such a micro-optimization that you shouldn't worry about it. The database has to read all the rows, and those reads are going to be less efficient that doing a few comparison operations. The first can more efficiently be written as: where KeyColumn in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) This should have the ...


0

Sounds like your statistics are getting out of date, then eventually SQL's auto update statistics will kick in and update the stats, proper execution plans are being generated, then everything starts running faster again. If you have installed Service Pack 1 for SQL Server 2008 R2 then you can turn on Trace Flag 2371 which will change the algorithm which is ...


2

I don't know why you can't see the InnoDB plugin. I've never seen that happen. You can also use this method to check what version of InnoDB you have loaded: mysql> SHOW VARIABLES LIKE 'innodb_version'; If it returns a number, then you're using the InnoDB plugin. If it returns nothing, then you're using the builtin InnoDB (or no InnoDB at all). I ...


3

Congratulations, someone has reinvented EAV (Entity Attribute Value). Please study up on the subject with them. The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them. I would also ask: If someone wants to ...


1

to deiced if schema design is good or bad what you should ask yourself is "what different types of query i will use against schema to access this data". good or bad is very subjective and based on our own experience we can do educated suggestions but most accurate thing will be what is your business requirement. start from looking at most frequent ...


1

Relational databases are designed to ensure data structure complexity and flexibility. Having all records in a single table can IMHO mean two things - a bad design, or you don't need a database at all. Why not just use an Excel table then? The example you provided shows that you want to store people and their houses, colors, etc in the same table. Having ...


2

This is bad design practice and should be avoided. Switch to NoSQL DB if this is really needed.


1

Unfortunately, it is O(N). Internally MySQL creates a new table with the desired structure, copies all your data over to that, and then finally swaps datafiles to effect a drop of the original table with a "rename" of the new. You can find this in MySQL's manual -- it's buried (hence why I can't find the direct link for you right this moment), but it's ...


0

As Marc mentioned, the primary key is a logical construct and doesn't by itself indicate the clustering key. I won't repeat that info in my answer here, but if you want a deeper explanation of the differences between the structures behind clustered and non-clustered indexes, I have a video here which may be helpful for you. Now, it's entirely possible for ...



Top 50 recent answers are included