Viewing 15 posts - 1 through 15 (of 121 total)
I've just been reading that 2019 has a lot of improvement in the area of the query optimizer, table variables, etc. Not really looking for new features. Just wondering if...
October 5, 2021 at 2:31 pm
Actually, it depends on how the table is searched.
Do you often filter the rows by only type when SELECTing? If so, then the clus index makes sense as is. ...
August 25, 2021 at 12:39 am
Looking at the Clustered Index for the oeordlin_sql table, I'm a wee bit concerned (especially for insert and update performance) over having the leading column be in the "ultra...
August 24, 2021 at 7:39 pm
Here is all the indexes for the tables. If you need different format, let me know. P.S., the view only creates aliases for some of the columns in the...
August 23, 2021 at 8:08 pm
Here is all the indexes for the tables. If you need different format, let me know. P.S., the view only creates aliases for some of the columns in the oeordlin_sql...
August 23, 2021 at 8:07 pm
I think you're right. I've found that if I use the LOOP hint to force a loop join, which passes thru the ord_no and line_seq_no thrue to the wspkglin join...
August 23, 2021 at 4:59 pm
It’s funny nobody suggested the most obvious choice in such situation - RIGHT JOIN:
Select s.Shipment_No
, p.pkg_no
,...
August 6, 2021 at 3:15 pm
I would think the subquery would (almost) certainly be able to take advantage of the fact that the query only needs to lookup a single Shipment_No, rather than processing...
August 4, 2021 at 8:09 pm
This subquery actually seems to work well. Any ideas if it would have any performance benefits over the nested join query?
select s.Shipment_No, pl.pkg_no, pl.ord_no
from wsPKGShipment s
left join...
August 4, 2021 at 7:42 pm
Thanks for that, now I'm clear.
Yeah, I think the first query you posted should do that.
As to performance, make sure the s and p tables have an index on...
August 4, 2021 at 4:55 pm
Let me try to explain what i need better. I want all shipments, regardless of whether they have matching wsPKG or wsPKGlin records. I want to join wsPKG records to...
August 4, 2021 at 4:34 pm
If I understand your requirements correctly, the code below will do what you want.
As to performance, make sure the s and p tables have an index on Shipment_No and...
August 4, 2021 at 3:55 pm
Might this work?
select s.Shipment_No, p.pkg_no, l.ord_no -- You need to get ord_no from either s or p instead
from wsPKGShipment s
left join wsPKG p
ON s.Shipment_No =...
August 4, 2021 at 3:50 pm
After rewriting the query and adding new indexes:
Total Cpu - 1 min 7 sec -> .488 sec
Total Logical Reads - Over 78M -> 150K.
Very impressive improvements. Thanks guys !!
July 21, 2021 at 4:15 pm
The ID is included to make sure the key is unique. Unique indexes process better in SQL Server. All key columns from the clustered index are automatically included in...
July 21, 2021 at 3:52 pm
Viewing 15 posts - 1 through 15 (of 121 total)