Tag Info

New answers tagged

0

I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.


1

The ORDER BY inside the subquery is rather useless if you are looking for userid. You should do two things First rewrite the query (remove the ORDER BY authorId) SELECT p.* FROM Posts p WHERE p.authorId IN (SELECT f.userId FROM Followers f WHERE f.followerId = 9 ) ORDER BY ...


1

An alternative to Roger's answer, would be to LEFT JOIN to TblZ. Personally I think for the example you've posted, an APPLY is probably not the most appropriate method of data retrieval, however it would be worth testing performance against the APPLY to see which is quicker. I'd also recommend not selecting *, simply because you could be pulling columns ...


2

For starters, you can remove multiple references of the same table: Select A.w, oa.f as valueZA, oa.k as valueZB, From DatabaseA.TblA A LEFT JOIN DatabaseA.TblB B A.x = B.x outer apply ( Select Z.f, Z.k From DatabaseZ.TblZ Z where Z.w = A.w ) oa where A.isActiveBit = '1'; You can replace outer with cross, depending on the ...


0

I have a function that I keep on the master database for these cases. I will start by creating my function as per code below: use master go create function dbo.getCities( @thecities varchar (100)) returns table as return WITH cteTally AS (SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n FROM ...


0

This query can expressed less awkwardly as a join. We join the two tables social_objects and social_permissions on their IDs (that is, match them up with IDs) and simplify the WHERE clause. SELECT so.* FROM social_objects so, social_permissions sp WHERE so.id = sp.id AND ((sp.model = 'User' AND sp.foreign_key = 1) OR (sp.model = 'SocialList' AND ...


2

I think the best way would be to "prove" that your subqueries - that are compared with a value - do always return one or no value. By making sure they all have either: TOP (1) ... ORDER BY ... (or the equivalent modern ORDER BY ... FETCH NEXT 1 ROWS ONLY) WHERE clause that uses the primary or a unique key, e.g.: WHERE table_id = 496 HAVING clause that ...


0

Just going to let you guys know what I did, thanks for responding. I ended up sticking with my original query but added keys to my table, which are apparently a thing I didn't know about. When I did ALTER TABLE tblstatushistory ADD KEY (id), ADD KEY (itemtype) It dropped the time waaaay down. Thanks for pointing me towards resources that taught me about ...


1

The EXPLAIN statement can be very useful when trying to track down the cause of a slow query. There's some great explanations of how to use the EXPLAIN statement and how to interpret its results here (with more real-word examples at /case{2..4}) and the official documentation here. Hope this helps!


0

Sounds like you want only the orders with a certain status/type... SELECT tblstockingorders.*, tblstatushistory.status AS actualStatus FROM tblstockingorders INNER JOIN tblstatushistory ON tblstatushistory.id=tblstockingorders.stockingorderid WHERE tblstatushistory.itemtype=2 AND tblstatushistory.status=0 editted version ...


1

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory. Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance ...



Top 50 recent answers are included