New answers tagged

0

Apologies, don't do MySQL but hopefully the approach can be adapted from this SQL Server example. For SQL Server I'd use OUTER APPLY / TOP 1 to get the latest insert per article but MySQL doesn't appear to support it. Create a table as per your schema and a stored procedure for the insert: USE tempdb; GO BEGIN TRY DROP PROCEDURE dbo.ArticlePrice_Insert ...


0

I would say that there are probably more query statements in the batch that produced that warning than you are showing here (or potentially even a DLL Trigger). The [sys].[sysschobjs] table is an internal, system catalog table that cannot be referenced directly (unless you connect to the DAC -- Dedicated Administrator Connection). However, that table is ...


3

If you right-click on the warning -> properties you should see some Warnings:


4

These are query planner warnings. In the actual execution plan (perhaps not the estimated one) I would expect to see a "warnings" section listing what the planner/engine is concerned about. A common warnings are when an operation spills data to disk or was wanting index statistics that were unavailable. The most common warning is for apparently missing ...


0

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


3

To answer your question directly: No. Using floats for a primary key is not a good idea. Why? Because floats can run into problems with rounding and precision (Is 1.20 the same as 1.2? Because mathematically it is.) Personally I would create a primary key on a single field. Preferably an integer. Integers are typically more efficient than floats and there ...


2

To be fair I know nothing of sqlite but treating it strictly as a design problem I would have <storeid1, storeid2, distance> With a primary key on a combination of the three. The table isn't going to change very often (only when new stores arrive) and the distance between two stores will never change so you don't have to worry about inserts a great ...


1

LIKE with a wildcard at the start of the expression can't use a btree index. In order to make that query use an index, you need to create a trigram index for which you need to install the pg_trgm extension first. create index on test_table using gin (nickname gin_trgm_ops);


1

So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ? NO, SQL Server would not do it immediately. Rebuilding indexes does lot of page movements and so does stats update. There is quite a chance that pages already present in memory, before index operation, would ...


1

FROM ( SELECT ... ) JOIN ( SELECT ... ) does not optimize well. Think of a better way to write the query. If that fails, put one of the subqueries into a TEMPORARY TABLE and add an index to it. Consider using the datatype DATE, not INT, for dates. OR is a performance killer (because it prevents use of an index). Consider other ways to deal with IS ...


1

First, add these indexes: ALTER TABLE cat_01 ADD INDEX `ShopBuyDates` (`shop_id`,`buy_date`) ALTER TABLE orders ADD INDEX `OrderSales` (`order_id`,`sales`) Then try this query, and report the results. Make sure to run it at least twice, and discard the first result's performance, to flush out the effects of populating the cache. SELECT 'cat_01' as ...


0

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)? I would also check various wait statistics, using Paul Randal's query as good starting point http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


2

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group: SELECT id FROM dbo.SomeTable st1 WHERE EXISTS ( SELECT * FROM dbo.SomeTable st2 WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i )


0

I would like to give you some hints for testing on performance. In order to make such tests, and get the right profiling, you have to flush the contents from the database buffer cache (DBC) before each query, otherwise the database will store some of the needed blocks in the buffer cache and that will affect the performance each time. Also, keep an eye on ...


0

Alas, not much that would explain your issue. Observations: Version: 4.0.17-nt 2 GB of RAM Uptime = 05:57:03; some GLOBAL STATUS values may not be meaningful yet. You are running on Windows. You appear to be running entirely (or mostly) MyISAM. The More Important Issues When the tables are all MyISAM, key_buffer_size should be about 20% of available RAM....


0

Try creating an index on column[DownloadDate] (if you do not have one) and then see whether the issue still persists. create index idx_DownloadDate on dbo.tbl_Download (DownloadDate);


0

You need to provide more information for us to be sure: the table definitions including all the indexes and other keys defined upon them, if any of the referenced objects are views rather then tables then their definitions too and those of their dependant objects, and the query plan (I'm assuming sybase can be asked to present you with that as easily as ...


2

The bad plan is probably a culmination of many problems. Which means that there are many ways of tackling it. My guess is that the culmination of problems causes two plans to look falsely close to each other in cost, and then the differences in memory setting (probably effective_cache_size) between master and slave is the straw that broke the camel's back ...


0

Delete does not always use the same indexes as a select but optimize the select will typically help try this select count(*) FROM sko.stage_närvaro e JOIN ext.v_imp_närvaro v ON e.person_id = v.person_id AND e.termin_fakta = v.termin_fakta AND e.läsår_fakta = v.läsår_fakta AND e.försystem = 'Extens' an index on försystem should ...


2

It seems that you created view ext.v_imp_närvaro on the base table sko.stage_närvaro, if it is right then it will be good choice to create a temp table with qualifier rows of where clause and then execute delete statement on table and use temp table for check existent of rows.. SELECT person_id,termin_fakta,läsår_fakta INTO #Temp FROM [ext].[v_imp_närvaro]...


5

I don't know fully details of your environment, i.e. if the tables in question are mostly used for writing or reading. How often you do this delete? what is the primary key and clustered index of [sko].[stage_närvaro]? If I wanted to optimise this delete there are a few things I would consider: 1) an index on the underlying tables of the view ext....


5

If you are interested in locking, there are several extended events available: lock_acquired lock_released lock_escalation The first two events have a duration column in (microseconds) which you could filter on for your thresholds. They also have a resource_description action which will give you some detail on the resources involved. The lock_escalation ...


8

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable. With a filter on increment >= 200 CREATE EVENT SESSION [locks_lock_waits] ON SERVER ADD EVENT sqlserver.locks_lock_waits( ACTION(sqlserver.sql_text) WHERE ( [sqlserver].[is_system] = 0 ...


0

EXPLAIN may be saying 2, but the real number could be 1 or 10000. Don't trust EXPLAIN. Here's how to get a more accurate count of rows touched by a query: FLUSH STATUS; SELECT ...; SHOW SESSION STATUS LIKE 'Handler_%';


1

These metrics often have to be looked at in parallel with other metrics. Simply doing a table scan is not a bad thing in itself. Often the query engine will pick a table scan even if an index exists if it's faster, for example on a tiny table. You need to look at other metrics, such as Disk Read/Sec & Disk Write/Sec, along with PLE and others. Note ...


0

Try using exists operator which should perform a search and stop when the first record that matches the condition is found: select 1 from dual where exists ( select * from someTable where owner_id = 0 and reviewed = 0 );


0

LIMIT provides a window into the result set. LIMIT 1 is equivalent to LIMIT 0, 1; return 1 row starting at offset 0. Explain plan reports the estimated number of rows the LIMIT is running against. However, only one match should be examined. Testing on your SQL Fiddle shows the query runs against the INDEX (KEY) which should be the fastest access method. ...


4

BCHR can be very misleading due to read-ahead: The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking ...


1

PLE just tells you how long the oldest page has been stored in memory before being evicted due to memory pressure. It could be that you have a high frequency of little queries that hit cache and perhaps a large one that flushes out the cache and requires them to recache. Without having additional data this looks like an environment that perhaps is split ...


0

EXPLAIN estimates row counts, often poorly. Even so, the counts do not reflect how much stuff is hauled around. If it is only id, that is not much. If it is all of * (as in SELECT *), that is a lot more. This is especially the case when it has to reach into the data to get all the columns. Using index means that it is cruising through the index BTree. ...


1

On my tests, I do not see a significant difference (full table scan, 6 rows only, like your example): $ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE CAST(c AS BINARY) = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done 5.10 4.93 5.00 4.96 4.97 5.21 4.95 5.04 5.00 5.03 $ for i in $(seq 1 10); do for ...


0

I suspect this query has died due to the volume in the tables. Running a query that returns all userids 24000 times will eventually cause performance issues. At some point you your tables grow to the point they don't fit in memory (very fast access) and you end up reading data from disk (very slow queries for most queries. This is a sudden switch. One ...


0

for grins give this a try `> is changed to >= so not exact same query SELECT TOP 1 subscription_id FROM subscription AS sub JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id JOIN person_group AS apg ON apg.person_id = bi.person_id AND apg.group_id = @Id AND apg.start_date < GETDATE() ...


12

If you can't change the query you can use a plan guide. Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this). If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (...


4

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


1

I'd start investigation with the following: Check physical servers parameters like CPU, physical memory, hard drive speed, network , etc, and make sure they are comparable (ideally identical) . Check Oracle memory settings (memory_target) on both servers Make sure dbms_stats procedures were executed on new server after copying database (at least ...


-1

You can try to use the parameter OPTIMIZER_FEATURES_ENABLE as a quick fix.


4

This query usually perform better: SELECT rn.ID, --... other columns go here rn.OrganisationID FROM ( SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id) FROM #t ) rn WHERE n= 1


1

Note sure if it will be more efficient select * from ( SELECT PID.ID, PID.OrganisationID , row_number() over (partition by PID.OrganisationID order by PID.id) as rn FROM #t AS PID WHERE PID.OrganisationID <> 0 ) tt where tt.rn = 1


1

In 1GB of RAM, you may be able to set innodb_buffer_pool_size to 50M, maybe 100M. But definitely not 800M. The 80% advice is predicated on having at least 4GB of RAM, and 80% is too high even for that. Having other things on the same machine (Apache?) adds to the cramped quarters. If you have Apache, lower MaxClients to 10. Other things to keep out of ...


1

There are benefits to indexing foreign keys as they provide better join performance as described by SQLSkill's team here and here. It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes. A script can be found here or Indexes ...


0

You can download and install the Microsoft SQL Server 2012 Performance Dashboard Reports from https://www.microsoft.com/en-us/download/details.aspx?id=29063. On that dashboard, you will see a link for Missing Indexes. This report will break down details for a set of Proposed Indexes determined by the SQL Server Engine. You should right-click and Export ...



Top 50 recent answers are included