Hot answers tagged

10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


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


4

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on. When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or ...


4

Idea 1 Judging by their names, the columns "denormalizedData" and "hugeText" seem to be comparatively big, probably many times as big as the columns involved in your query. Size matters for big queries like this. Very big values (> 2kb) for text or jsonb get "toasted", which can avert the worst. But even the remainder or smaller values stored inline can be ...


4

tl;dr You can't force MySQL to use a lot of cores. 40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though. MySQL can use a lot of CPU cores as of recent ...


3

Preliminary notes You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related: Would index ...


2

You are correct that PostgreSQL cannot currently use one index to provide selectivity and another index to provide order, in the way you want. Adding this feature has been discussed, but I don't think anyone considers it a high priority. Creating a multicolumn index will not help in any way. From my testing Postgres will not change its query plan at all ...


2

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.) Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


2

Looking into my crystal ball, your query might work faster by orders of magnitude like this: SELECT i.*, rr.urllist FROM vw_image i LEFT JOIN LATERAL ( SELECT string_agg(r.url, ',') AS urllist FROM resourceonpage rop JOIN resource r ON r.id = rop.pageid WHERE rop.siteid = 2294 -- or: = i.siteid AND r.resourceid = i.resourceid ...


2

Would i benefit in this case from the myisam table engine? No you won't. What could i do to further optimize the query or the settings? Materialize the query. MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it. Create a table like ...


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 )


2

why it expects for 1 row (in Clustered index seek), when I specify recompile That is fine. Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator. A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all). In your case you have 2,000 seeks all returning 1 row ...


1

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so. It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table. The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the ...


1

Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have. Start with this: SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout FROM employees LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno LEFT JOIN attendanceOut ON ...


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

Be aware - I posted this before index definitions were posted Insert in the order of the PK to keep fragmentation down INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT 2, value, 0 FROM Z_SIMULATION_0 WHERE ID >= 1 AND ID <= 500000 ORDER BY value; or INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT top (...



Only top voted, non community-wiki answers of a minimum length are eligible