New answers tagged query-performance
4
Some notes:
After rebuilding indexes, do not update all statistics!
REBUILD indexes, don't REORGANISE
An index rebuild will rebuild statistics anyway. A further update actually means you'll have worse statistics because of sampling ratio. You can rebuild column statistics though.
Also, it's worth comparing the server specs (RAM, CPU, Disk) and query ...
0
Could it be that the database is used in a different way in the new location?
This is a common theme - different queries running in the database copy (perhaps reporting-style queries).
In this case you may want to drop the existing indexes and create new ones that support these new queries.
You'd first have to let them use the db on the new location for a ...
0
When an index rebuilding operation is performed, it drops the index, recreates the index, and updates its statistic.
If I read the question correctly, your question of dropping indexes, recreating then update the statistics is pretty much the same as simply rebuilding the index.
Please note that index reorganization operation does not update the ...
0
Indexes fragmentation and statistics are not the only possible cause for a performance trouble.
Apart from this rebuild and reorganize are different process.
INDEX REBUILD: it drops the existing index and recreates the index. Can be done online or offline.
INDEX REORGANIZATION: it physically reorganizes the leaf nodes of the index. It is always online.
...
0
You cannot run any DML in a logical standby database unless you deactivate, temporary, the guard feature. To overcome you isse you can do:
SQL> ALTER DATABASE GUARD NONE;
SQL> SELECT GUARD_STATUS FROM V$DATABASE;
GUARD_STATUS
--------------------
NONE
And then run your dbms_stats.
0
Tom Kyte (of http://asktom.oracle.com fame) maintains links to several different methods of "unloading" a table (or query) to CSV.
(expdp comment redacted.)
2
The best index for this query is (u1_id, t)
My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the ...
0
You asked for suggestions. Here's one.
If I were trying to analyze this case, the first thing I would do is separate it out into two separate DELETE statements, one to cover the IS NULL case, and the other to cover the value_was < Value_now case. See whether they both run slow. I expect the IS NULL case to do a table scan, unless indexes work ...
0
You could try to use EXISTS clause instead of IN, since its usually less performance expensive:
DELETE FROM TABLE T_delete
WHERE EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was IS NULL
AND T_delete.ID1 = t_join.ID1)
OR EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was ...
5
This (orders_products) is a many-to-many table. I think it's common to have 2 composite indexes on such tables as it helps in many common queries.
I would definitely add two (unique) indexes, on (orders_id, products_id) and on (products_id, orders_id).
Not sure if defining them both as UNIQUE would be a further improvement in MariaDB's optimizer.
And if ...
1
I Don't know your database but hope maybe this will help
I setup some test data so I have an orders_products table with 27,000 rows populated like this so I have each order with three random items from a possible set of 10
Create Table orders_products (Orders_Id Number, Products_Id Number);
create index orders_products_I1 on orders_products(orders_id);
...
0
The first operation looks expensive, with the warning signs Using temporary; Using filesort. Consider adding an index on orders_products that allows you to search on products_id:
create index IX_OrdersProcucts_ProductId
on orders_products(products_id, orders_id);
By adding orders_id in addition to products_id, the index becomes covering for:
SELECT ...
1
Since is_keyword_checked can take only two possible values, 0 or 1 (and it's not nullable), you can try the rewriting. There is no OR and the index on (is_keyword_checked) will be used:
SELECT *
FROM domain
WHERE domain_ID NOT IN
( SELECT domain_ID
FROM domain_setting
WHERE is_keyword_checked = 1
) ;
or with a NOT EXISTS ...
0
If I'm reading your query correctly your asking for anything NOT in domain_setting EXCEPT for those with is_keyword_checked = 0. You should be be able to just get rid of that first not in check.
Further subqueries are not always as efficient as you might think. You could rewrite the query to be
SELECT * FROM domain join domain_settings using(domain_id) ...
1
Very likely your problem is that you have an index on the source table which is making the first hits easy. However at some point you are hitting rows that are not in the cache and so this is requiring a sudden increase in disk I/O accounting for the performance drop. In essence suddenly you start missing the cache and so you end up having to read a bunch ...
0
Most importantly, the main reason why you need to put it in a variable is if you want the exact same value (GETDATE() at that exact time) to be used elsewhere on the code. Otherwise, the performance difference of it is really a moot point.
3
The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows
DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > ...
0
The performance problem actually had to do with the LEFT OUTER JOIN tables. If I changed them to INNER JOIN, or if I excluded their data from the SELECT columns, the query ran fine.
What I ended up doing was creating a View on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds ...
-1
You have experienced one of the main problems with linked servers.
ALL data is retrieved over the network and then filtered.
IF the #tmpFile is relatively small compared to the others you could consider a stored procedure that RECEIVES #tmpFile, then runs all the joins locally and returns a dataset to the caller.
0
Have you tried the FORCE ORDER query hint? It forces the compiler to keep the order of the joins as listed in the query when optimizing it.
SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id
INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
...
1
You can certainly try this;
SELECT DISTINCT msisdn
FROM std_msc_opr_wise
WHERE
call_dt BETWEEN '2013-03-27' AND '2013-04-28'
AND msisdn NOT IN (
select msisdn
from as_treat_pre_usage_30days
group by msisdn
having SUM(std_total_og_mou)>0
)
1
You're right. Your tables would look something like this then ( oracle ddl ) :
create table Users (id int,
name varchar2(1000),
constraint pk_users primary key (id)
);
create table Questions (id int,
user_id int,
content CLOB,
question_date date,
...
0
You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure. You also should see no difference at all from the fact that the procedures are stored in a different database from your data.
From the start, though you're presenting us with two different unknowns -- ...
Top 50 recent answers are included
