Tag Info

New answers tagged

0

I think this is cleaner as you only want to know if the result is not NULL. If this is SQL Server, create the habit of adding the schema as well "dbo.my_table", if you don't specify it, SQL has to figure out the user's default schema every time a query runs, avoid the extra work by making it explicit. if exists(select 1 from dbo.my_table)


1

In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster. The overhead of parsing and planning multiple queries is already more than any possible gain in most cases. Not to speak of additional work to be done in the client, combining the results, which is typically much slower at that. ...


0

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN ...


1

This is just a guess, as I do not have all info, but you probably would be better by doing: EXPLAIN SELECT STRAIGHT_JOIN * FROM tusers PARTITION (p362) tu JOIN users PARTITION (p362) u ON u.group_id=tu.group_id AND tu.email_address=u.email AND tu.group_id = 362 WHERE tu.application_id=253555; Note the STRAIGHT_JOIN, ...


0

I think bit vector mapping may be a solution to optmize the performance of the cpu and you can easily utilize your cpu at 98-99% the processor get damaged for sometime and simply bit vector mapping will reduce the time consumption to load the page in frontend


2

The idea is that you want as many of your writes as possible to be asynchronous. On the other hand, pool_data_writes will always be greater than pool_async_data_writes (ditto for index writes). The tolerance depends entirely on your system. If each minute you have 3 pool_data_writes and only 1 pool_async_data_writes, you obviously have twice as many ...


1

Wouter got, hopefully, useful answers here: https://community.oracle.com/message/12608939 That said, being the Oracle ACE Director for this technology. Oracle XMLDB functionality is nowadays so interwoven with the Oracle database that this is an MANDATORY (install) functionality for Oracle 12c and onwards. It is the basis for much in the database like ACL ...


0

Attemping an answer based on comments The easiest and fastest way to do this is to take a set based approach. Like this: 1) Read NOW() into a variable @N 2) Load new data into Transactions 3) Update the Balance like this: UPDATE Balance B INNER JOIN ( SELECT SUM(value) as value, member_id, product_id FROM Transactions WHERE date >= @N) AS T ...


4

Some context The first things to observe is that your clustered index does not help with the lookup of the tid column, because t is the leading column in the index. If you flipped the order of t and tid in the key, I would expect the index hint to go away and the query to run faster without adding any new indexes. Specific Answer The most likely reason ...


1

When Orders and Deliveries are both properly indexed, the second query will generally perform better. There are two main considerations here: Generally speaking, and without seeing the details of your tables - what probably happens with the INSERT/UPDATE construct is that it will first allocate a number of pages in the database in the initial INSERT. But ...


1

I prefer to use the RMAN clone method. The overall method is to start with the PROD database, and then a DEV database that is in a NOMOUNT state with all datafiles/controlfiles/tempfiles/archivelogs removed: oracle> rman target sys@prod CATALOG rman/x@rman_catdb RMAN> list backup; 1 1399 53289602077 15-DEC-11 53289629964 15-DEC-11 1 ...


2

To use an analogy with banks, pessimistic locking is like having a guard at the bank door who checks your account number when you try to enter; if someone else (a spouse, or a merchant to whom you wrote a check) is already in the bank accessing your account, then you cannot enter until that other person finishes her transaction and leaves. Optimistic ...


1

OFFSET is not what you want to use. The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient. http://www.postgresql.org/docs/9.3/static/queries-limit.html Instead, add a where clause along the lines of cur.execute("... WHERE id > %s ORDER BY id LIMIT %s", ...


1

From your profiler trace you should be able to see the Login and Host that the queries are coming from. I would then speak to your operations and/or development team to identify the application and function of the queries (or they should at least be able to point you in the direction of where you need to go/who you need to speak to). Once you have that ...


0

You could declare a table variable, insert to that rather than printing and dump it in toto at the end of your process: declare @message table (i int identity(1,1), words varchar(100)); The identity column will keep things in chronological order. @tables' contents survive a rollback so are preferable over #tables or normal tables for this purposes. I ...


0

One of the common disk usage process on a windows 7 machine under the System process is Superfetch. You might want to look into disabling it disabling superfetch. The reason why it's likely Superfetch is because you're seeing Only reads It's not your OS disk You're running windows 7, not a server version more slowdown as the amount of files increases ...


4

Query Your query is forced to scan the whole table (or the whole index). Every row could be another distinct unit. The only way to substantially shorten the process would be a separate table with all available units - which would help as long as there are substantially fewer units than entries in all_units. Since you have ~ 11k units (added in comment) for ...


3

Fulltext Search is a specialized technology that complements a traditional SQL database. There is a simple full text indexing feature built into MySQL, but you will probably find other alternatives that are more featureful and higher performance. I did a presentation comparing them here: Full Text Search Throwdown. The short answer: Sphinx Search. Re ...


0

Initially, I looked into database benchmarks for this (I'm interested in realistic benchmarks), but found that neither the HammerDB nor Sysbench tools appear to handle multiple databases (i.e. schemas). I couldn't find any Google link despite searching for many combinations of "MySQL benchmark multiple schemas/databases". With respect to multi-tenancy, ...


0

You could try to do the following: SELECT rows_changed FROM information_schema.table_statistics WHERE table_schema = 'mydb' AND table_name='mytable'; This returns a number that increases with each table update, keeping track of it will allow to to detect change. Important note: the value is changed immediately after an UPDATE, not after COMMIT. So you ...


0

How big is this database? How many rows are in each table? Etc? I would say that normalized data is default state to try to obtain. It is a leaner database, rows are shorter, and indexes may be used more effectively. The short, leaner rows therefore lead to a smaller, leaner database. One of the major accelerators of performance is memory. If you can ...


0

This image says it all. Thanks Kin for pointing out your question and related answers. I've learned a lot in the process. By looking into your detailed question I thought on doing the same, comparing execution plans of our heaviest query...and voila!! The issues was a job that was supposed to be executing was already couple of weeks with schedule ...


5

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keywork on it you are actually creating a different index, and that does not only make things more efficient but, in some cases, it will make your queries less performant. You can check that two index were created by doing: mysql> SHOW CREATE TABLE test\G ...


2

There are a few things that might be causing this issue, but I can't be sure any of them are the real problem. The troubleshooting all involves turning on extra logging in the database, then seeing if the slow parts line up with messages there. Make sure you put a timestamp in the log_line_prefix setting to have useful logs to look at. See my tuning intro ...


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 ...


1

I remember to have the same problems and it has something to do with how vBulletin is programmed. Please check regular "cronjobs" (not talking about system cronjobs you have already mentioned not to be the culprit) run by vBulletin which are triggered by visting users. You can find them in version 4 of vBulletin in your admin panel, just below the settings ...


1

You can definitely keep all your dimensions and measures in one fact table and not use any dimension tables. Make sure your OLAP tool supports this though. Normalizing out your dimensions into other tables is done mostly to minimize the size of the fact table, which can get large fast. With no dimension tables you're looking at about 336 MB per year (not ...


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 ...


0

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times. Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values ...


1

While there some manual things that could be done at database level (moving tablespaces around, etc.), most of the settings for MySQL are transparent to the storage layer, meaning that you do not need to do anything to take advantage of the RAID. While a RAID 0 could provide you a better theoretical throughput in reads and writes (at the cost of having 4 ...


2

Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast. Edit after new information: it would ...


0

Looks like a case of Parameter Sniffing! Check google on it. Running these commands will have the same effect: DBCC FREEPROCCACHE This command removes all of the cached query plans and execution contexts from the plan cache. It is not advisable to run this command on a production server because it can adversely affect performance of running applications. ...


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 ...


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 ...


2

Try: LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId AND LastOperationTime IS NOT NULL The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs ...


0

I'm no Postgres expert so this might be wrong! Your primary key has 3 columns, sessionID as the first field. Does the file contain a decent spread of timestamps? you might consider making that the first field in the primary key or using a surrogate key as currently this is fairly wide. From your script I dont think you have a cluster. Different to SQL ...


2

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then. As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL: Compatibility Levels and Stored Procedures When a ...


-1

Your tables do have several indexes to update, did you remember to run a optimize table from time to time? /usr/bin/mysqlcheck -o --auto-repair MyDatabaseName This will optimize all tables and indexes in the Database MyDatabaseName. Depending on how your data looks this might help with INSERT and SELECT speed. I guess since you have tried everything you ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...


1

That is 100% normal: what you are seeing is the background threads performing the following operations: Flushing dirty pages from the buffer pool into the tablespace files. You can see that is happening on your case due to the difference between your LSN and the latest checkpoint on the SHOW ENGINE INNODB STATUS. Merging non-unique secondary indexes ...



Top 50 recent answers are included