Tag Info

New answers tagged

0

There are some hint, but which version are you using? Is it enterprise edition? Anyway: If you can, move transaction log to a faster disk Analyze the where. Will it use an index to identify records to delete? If not, can you add an index? Do you have any index on the table that you can drop? If yes, drop them. Do you have foreign keys versus this table? ...


0

Adding few more points.. 1.try checking if the predicate has index on it and also see stats 2.if you are deleting large number of rows and you also dont want temp table option.go for tablock option. 3.see if you have any triggers ..specifically after delete triggers To get more help.post the query you are using, table info plus any blocking info.. Excuse ...


0

I'm using Dynamics AX fairly extensively and you can view the estimated execution plan of this query relatively easy if you declare all your @p1..@Pn parameters like this: declare @P1 bigint, @p2 nvarchar(10), .... SELECT 2 AS f1, T3.RECID AS f2, T4.RECID AS f3, ...


0

Off the top of my head you can reduce the number of cycles by using a query wrapper and grouping on that. For example: zabbix=> \copy ( select hostid, value, extractDay, extractMonth, intYear, DailyUsage from ( select h.hostid, sum(value) as value, extract(day from to_timestamp(clock)) as extractDay, extract(month ...


8

If you are trying to delete a large number of rows in a single statement, then it is likely you are waiting on log activity. So you can: Make sure your log is adequately sized so that growth events don't slow you down. With the defaults your log is probably starting at 1MB with 10% growth. Growth events are expensive, and if you are logging even 10 GB of ...


0

You should try deleting them chunk by chunk, probably deleting in loop, each delete iteration it's own transaction and then clearing the log at the end of each loop iteration. Also, you would need to find the number that you are going to use as value in chunk for deleting the records. It requires a thorough testing, would be better if you can test for the ...


3

First retrieve id and the computed scores, then sort and join the result back to news: SET @line = 0; SET @last = 25; SELECT B.*,titlescore,timescore,textscore FROM ( SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line FROM ( SELECT id, MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ...


0

(I'll attempt some out-of-the-box thinking here. Don't hurt me if I'm completely off ;) I assume an essential part of your query execution time is spent on the clause computing the score for the news text. You'll need to verify that for yourself before continuing. In most of the cases, highly relevant articles (the top 25) will generate a decent score ...


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

By default, the results are entirely buffered in memory for two reasons: 1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory). 2) Unless specifying a FETCH_COUNT, psql uses the ...


0

The child tables need to have indexes on their foreign keys too, cause postgres won't auto-create them: I think the code above will can help: CREATE INDEX rxmsg_vid_index ON rxmsg USING btree (vid ); CREATE INDEX drivers_vehicle_id_index ON drivers USING btree (vehicle_id ); And i dont know if you do that but the "point" column need to ...


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


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


0

I think you know about performance of using UNION instead of OR, but it's not in this case vs. using CASE that just add an inline filter. In your second query you will have two Sort actions as a side effect of having two GORUP BY statements and also two Filter actions with adding a Concatenation by using UNION. Note: The count of actions is not a ...


0

I'm agree with second suggestion of RolandoMySQLDBA answer that using UNION instead of OR makes the seeking of the results too faster, And also we know: The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


0

Eventually I realized that it was some king of caching and probably on Linux level. After applying # sync && echo 3 | tee /proc/sys/vm/drop_caches followed by mysql> RESET QUERY CACHE; the query time became linear. Note though, that before this the effect was stable on different queries.


1

What you have is a full table scan with no way to speed it up. Look into FULLTEXT(bussiness_name, trading_name) indexing as a much faster way to search for words. You would need MATCH (bussiness_name, trading_name) AGAINST("$data['searchstring']" IN BOOLEAN MODE). If status is rarely 1 and is_approved is rarely 1, then this would help: INDEX(status, ...


6

SUGGESTION #1 SELECT A.* FROM table1 A INNER JOIN ( SELECT '2015-03-01' dtcolumn UNION SELECT '2015-03-15' UNION SELECT '2015-04-01' UNION SELECT '2015-04-15' UNION SELECT '2015-05-01' ) B ON A.dtcolumn >= B.dtcolumn AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY; SUGGESTION #2 SELECT * FROM table1 WHERE (column >= '2015-03-01' ...


1

Try something like this: (I did this on Oracle, it should mostly work elsewhere, the WITH clauses are mostly for just faking sample data .. so not entirely necessary) with w_date_list as ( -- just some sample input dates - these are from your IN list (note that you want to re-org them as a "table" not an IN list - there's ways of doing that if you need ...


1

It is slow because the table is big, and it performs lots of IO when reading sparse data. The index on w2 would be used to tell which rows will be read from the base table, which is huge. And since the rows in that table are not in order- physically that are fragmented, there will be probably one disk access per row (This is to read the value of w1) To make ...


0

Looks like (I'm by no means a Postgres expert) you have about two million rows in LEAD_DETAIL table that satisfy the state condition... those two million rows are retrieved, hash joined to leads, the result sorted to return the top 5000 rows. Could you move the state to leads table, so that it becomes a column there? Create and index on (upper(state), ...


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


2

In situations where one static sql (procedure) executes better than an equivalent one, I would examine: a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case. b) are the ...



Top 50 recent answers are included