154 reputation
6
bio website
location
age
visits member for 3 years
seen Mar 15 at 1:03

Mar
4
awarded  Curious
Mar
3
comment Query for orders that contain a product without using sub-query
Aha... your second query nailed it.
Mar
3
comment Query for orders that contain a product without using sub-query
That's for sure :)
Mar
3
comment Query for orders that contain a product without using sub-query
Its frustrating that the table view seems to add so many joins to the query that it doesn't realise there's a faster method of filtering.
Mar
3
comment Query for orders that contain a product without using sub-query
I had one for product ID and it said it already had orderId. I don't understand what it was doing. Anyhow I deleted it and added (ProductID, OrderID) and now the query with the view is down to 2 seconds even though its still doing index scans. So that's an improvement which will do for now.
Mar
3
comment Query for orders that contain a product without using sub-query
I will try your second query. The first works with index seeks until I swap out the order table for an order view at which point the optimizer seems to lose touch with the ability to use the product id smartly. The query goes from 1 second to 7.
Mar
3
comment Query for orders that contain a product without using sub-query
The optimizer is only using the product id to seek in the products table. Maybe I can jig it to do something smarter. (OrderID, ProductID) are in fact not unique. Some attributes further distinguish products with the same id.
Mar
3
accepted Query for orders that contain a product without using sub-query
Mar
3
comment Query for orders that contain a product without using sub-query
A join on an exists. That's amazing.
Mar
3
awarded  Editor
Mar
3
revised Query for orders that contain a product without using sub-query
added 23 characters in body
Mar
3
comment Query for orders that contain a product without using sub-query
Yes exactly. I don't see how to get a product id outside the view into the query.
Mar
3
asked Query for orders that contain a product without using sub-query
Feb
22
comment SQL Server Express Limitation
10Gb a database, one CPU and a single GB of memory is plenty OK for me I think.
Feb
22
accepted SQL Server Express Limitation
Feb
22
comment SQL Server Express Limitation
Sounds good. I will give it a go.
Feb
22
comment SQL Server Express Limitation
The SQL Express limitation kind. This post is quite recent so I guess its still there.... stackoverflow.com/a/26168739/221683
Feb
22
asked SQL Server Express Limitation
Aug
25
comment UPLOCK to avoid multiple reads
The row I'm selecting (the order) isn't the same row that's being inserted (the event). So how can I use OUTPUT to do both steps within a single statement?
Aug
25
asked UPLOCK to avoid multiple reads