Tag Info

Hot answers tagged

7

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables? No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit. I would suggest trying, in this order: Increase the memory allocation to SQL Server. You ...


7

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel. In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning ...


6

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does. This is because SQL is "declarative" not "procedural": you say what you want, not how to do it. It's nicer to read though...


5

With your current structure, your query may be doing >1,000,000,000 row scans. This is probably due to the FIND_IN_SET - meaning the column cannot be used in an index, resulting in the optimizer choosing other indexes with very bad selectivity (you have to read 500000 rows instead of 1 million, that is very bad selectivity). The fact that an index is being ...


3

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have: SELECT d.dbId, d.dlState, d.retreivalTime, d.seriesName, <snip irrelevant columns> FROM DataItems AS d JOIN ( SELECT seriesName, MAX(retreivalTime) ...


3

An index can be used to optimize the GROUP BY, but if the ORDER BY uses different columns, the sorting cannot use an index (because an index would help only if the database would be able to read the rows from the table in the sort order). A COLLATE NOCASE index does not help if you use a different collation in the query. Add a 'normal' index, or use GROUP ...


2

Example 4 has the fewest scans and reads: Example 1 SQL Server parse and compile time: CPU time = 4 ms, elapsed time = 4 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. example1 Id FirstName 1 2 Aaron 1 3 John 1 8 Aaron 1 9 John 1 14 Aaron 1 15 John 1 20 Aaron 1 ...


2

There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results. However, if you had such a thing (a new employee with no registered work_log), a JOIN wold omit that ...


1

I would rewrite the query as following SELECT DISTINCT id FROM ( SELECT id from patient WHERE company_id=1 AND name_last LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_first LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_remote LIKE 'peter%' ) A; I would also change the indexes as follows ALTER ...


1

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%. Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. ...


1

If you don't have a DBA chances are the server hasn't been configured according to simple but critical best practices. You can't really make a definitive diagnosis until you've done that. Engage and expert to assess what needs getting done. Then either make the changes or have them do it.


1

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable? If they use unrelated data sets, then yes. If they share a data set, and the cache is cold for the first query and the query is mostly I/O bound, then the second one might complete in moments. You need to consider ...


1

You could use the shell command time postgres@db:$ time psql db -c 'SELECT 1' ?column? ---------- 1 (1 row) real 0m0.108s user 0m0.040s sys 0m0.032s Or, to combine a meta command with an SQL command, you could pipe a string to psql: postgres@db:~/script$ echo '\timing \\ SELECT 1;' | LANG=C psql Timing is on. ?column? ---------- ...


1

There are too many different topics in one question, and even after many updates not all is clear. I'll just pick the elephant in th room and ignore the rest: Query 1 -> Index Scan using r_pkey_index on relationships r (cost=0.43..57.53 rows=13 width=0) (actual rows=1 loops=1) Index Cond: ...


1

At 14 million rows in your largest table and 16GB of RAM devoted to SQL Server it sounds like your entire DB almost fits in memory. If that is true the problem is likely to be quadratic (or worse) behaviour in your queries, which can only be truly fixed by rewriting the worst offenders. That said, I would try one or both of: Adding some SSD RAM to the ...


1

Let's say the table is called mydb.mytable This query SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1; can dramatically be improved if you index the table. There is one of two techniques you can try when indexing the table Technique #1 ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5); Within the ...


1

Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees. To make sure, view your estimated execution plan with one or both of the subqueries, and compare them. In your example, you could place the subquery in a JOIN instead, which would make it both ...


1

At the moment, I am using an instance of MySQL 5.6.19 which has the performance_schema enabled on a 2GB x64 Linux laptop (nothing stellar by any means!). My normal response time is approx. 0.5 s - not too bad. What's your setup (RAM, disk config, OS &c.)? I must say that I'm surprised at 3 seconds on a server. Are you sure that it's not some sort of ...


1

If you are just migrating the Schema itself, and not any of the data, Index Fragmentation of your user database tables should be irrelevant. The instructions in the migration script will come from system database tables holding the metadata for your user database/schema.



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