Tag Info

New answers tagged

-2

For completness see my solution. To replace the simple command: SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (...) there is fast and ugly one: DECLARE @IDZapisovac int = 1 DECLARE @MinCas DateTime2(2) = NULL DECLARE @IDUkazatel smallint DECLARE @Cas DateTime2(2) = NULL DECLARE helper CURSOR FOR SELECT IDUkazatel FROM ... OPEN helper FETCH NEXT ...


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

SELECT [33500 List].ID , [33500 List].dayt , [33500 List].tyme , [33500 List].[MOBI Size] , [33500 List].Title , [33500 List].Tiep FROM [33500 List] WHERE ((([33500 List].Title) Like "the *")); SELECT TOP 15 [33500 List].ID , [33500 List].dayt , [33500 List].tyme , [33500 List].[MOBI Size] , [33500 List].Title , ...


0

Make the outer joins inner joins, and enforce the relationships. Superficially, it looks like every PAMtx should have a proper transaction type and meter mapping anyway. If you can't do that -- because the relationships doesn't always hold -- try a UNION of an inner join to one (or two) WHERE NOT EXISTS queries for those rows that don't match. Also ...


0

Are your indexes & tables maintained recently and are you statistics up-to-date? Fragmentation and wrong statistics can cause big problems.


0

Just for fun, why not try this SQL instead, which rearranges things a bit: create table #t(name sysname) insert #t(Name) select Name from sys.databases where state_desc = 'ONLINE' except select name from ( values (cast('master' as sysname)), ('model'), ('msdb'), ('tempdb'), ('ReportServer'), ('ReportServerTempDB'), ...


0

Would This work? Got rid of case statement in where clause... SELECT name, OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') AS [LookUpID] FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND state_desc = 'ONLINE' AND ISNULL(OBJECT_ID(QUOTENAME(name) + ...


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

I assume you use SQL Server because you used the tag: Add an index to [PAMTx].CustomerId Foreign keys are not automatically indexed in SQL Server.


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


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


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


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


0

The query is pretty simple and ought to run fast enough. My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock. Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting 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 ...


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


0

You could rewrite with a LEFT JOIN: SELECT IT.[iInternalTransactionID] , IT.[iInternalAccountID] , C.[iContractID] , IT.[dtTransactionDate] AS TransactionDate , (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName ... -- ### unchanged up to here ##-- , G.TermNo , G.TermNo * G.MaxMonthlyAmount AS TotalAmount -- ### end ### -- FROM ...


0

Since you have identified that the sub-select (SELECT ...) CPH cannot join with higher level nesting, how about changing your sample rewrite as something like: INNER JOIN ( SELECT MAX([iContractID]) AS [iContractID] , MAX([iTermNo]) AS [iTermNo] , MAX([cMonthlyAmount]) AS [cMonthlyAmount] FROM [tbl_ContractPaymentHistory] WHERE ...


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.


0

There seems to be something wrong here: the 0 cost on the index full scan is suspicious and if I had to guess I would say that you're missing something: probably the stats on the index. This in turn leads the optimizer to believe that it can run the FULL INDEX SCAN "for free" and goes on with a suboptimal plan. This could also be a rounding error problem, ...


2

Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines: CREATE TABLE Category ( Category_ID INT NOT NULL AUTO_INCREMENT primary key, Parent_Category_ID INT NOT NULL REFERENCES Category(Category_ID), Description varchar(255) not null ) ...


2

You're committing one of the cardinal (excuse the pun) sins of database schema design here - you're using the Entity-Attribute-Value model. Check out the writings of Joe Celko or Bill Karwin on this issue. It's also called the OTLT (One True Lookup Table) or MUCK (Massively Unified Code Key - there's a reason that particular acronym was coined!). You would ...


0

Queries with WHERE column IN (SELECT subquery) may not be best optimized in various version of MySQL. The usual solution is to rewrite using joins and of course add appropriate indexes. The query can be rewritten equivalently as: SELECT u.* FROM users AS u JOIN ( SELECT selector FROM users AS u2 WHERE u2.key = 'username' AND u2.value = ...


2

The first thing to look at is db.serverStatus().ft. This has a bunch of metrics that may be helpful, to figure out where you're spending time. These are documented here: http://docs.tokutek.com/tokumx/tokumx-server-status.html Usually the way to improve query time is to make sure you have the right index for your query. You might be doing a query on ...


0

From looking at your query, I think the planner is making the right choice, you have no WHERE clause or LIMIT, so the database has to return every row anyway, so it has the choice look at the entire table and look at the indexes or look at the entire table. Have you tried restricting the rows with a WHERE clause or used a limit? I cant think of many time ...


1

I have no idea what I'm doing™, but the following, equivalent query seems to do the trick for me (at least, it was equivalent before you had edited your query to use a LEFT JOIN...): SELECT m.id, sub.cnt FROM m JOIN ( SELECT d.m_id, COUNT(1) AS cnt -- (+ other functions) FROM d WHERE d.m_id IN ( SELECT COLUMN_VALUE FROM TABLE( NEW ...


1

Hmmm.... this is an interesting one. The Oracle optimizer is a bit of a black box as far as mere mortals like myself are concerned... Jonathan Lewis wrote a 536 page book on the topic and that's just the **fundamentals* (and it ain't bedtime reading!). Two questions. a) Did you flush the caches before/after each query - maybe the first affects the second - ...


1

First, if email_id and delivery_id together are a unique key, please add a primary -composite- key on both tables for (email_id, delivery_id) Second, the concat is not necessary and will prevent the previous key from being used. Try: SELECT ol.* FROM open_log ol JOIN sent_log sl ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id)


1

SELECT * into dbo.dblog FROM fn_dump_dblog ( NULL, NULL, N'DISK', 1, N'C:\tranlog.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, ...


4

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently. If this is the case, the first query comes back as {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID} {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack ...


0

It is the big number of indexed fields in each index. Remember that the update will update the table and also the index tables that hold affected rows, and in your case, all indexes will be affected. As a side note, your indexes are 'bad'. ix_ID_SCHEME_LAST_FIRST_DOB is included in ix_ID_SCHEME_LAST_FIRST_DOB_GENDER, so it is redundant ...


4

I suggest a radially new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions: PostgreSQL Crosstab Query SELECT k.name, keyword_id, project_id, now()::date AS the_date , t AS today, y As yesterday, w AS week, m AS month FROM crosstab( $$ SELECT rn , ...


1

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT. I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already. SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...


0

It turned out to be this: left outer join pooling.clientaccount as ca on ( paa.clientaccountid = ca.id or (h.id = -1 and ca.id not in ( select clientaccountid from security.practitioneraccountauthority ) ) ) Causing the major slowdown, because the on ...


5

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice: Perform a table / clustered index scan (therefore all columns are there) Perform a seek, then perform lookups to retrieve the columns not covered Which way it will choose depends on a variety of things, including how narrow the index is, how many rows ...


0

Having those other fields in the index is just making it worse if you're always using select *. Try creating index just for CustomerID. That might help, but it depends on several things like how many IDs there is in the in list and how many rows per CustomerID there is in the table.


3

Note the slightly modified schema of my test in the fiddle. Using actual primary keys and proper column names instead of id. Also, you seem to be operating with dates exclusively. So I suggest to convert your timestamp columns to date. Items 1 and 2 SELECT k.keyword_id , k.name , pr.project_id , COALESCE(min(pr.position), 0) AS pos , ...


3

As ypercube commented No, if the query is what you show, he is totally wrong. It's pretty sargable as it is. You can verify this by: Creating a simple test table with a [Date] column. Insert a large number of rows with varying dates. NOTE: In the above "large number" and "varying dates" is a precaution to ensure that your query is selective enough. ...


0

It looks to me like you don't need the derived table. Is there any reason you couldn't write it like this: SELECT Sum(x) * 0.1, Sum(y), a FROM tx INNER JOIN ty ON tx.a = ty.a WHERE x = 1 GROUP BY a This probably won't solve all of your performance issues, but if you set statistics IO on and look at the logical ...


0

I got a solution. This SQL query solved the problem. SELECT * FROM `nw_object_detail` WHERE ((`details` like '%"screen_type":%"%LCD%"%') OR (`details` like '%"screen_type":%"%LED%"%')) PHP Solution if(count($options)>1): $condition = '('; $count = 1; foreach( $options as $option ): $condition .= '(`details` like ...



Top 50 recent answers are included