Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
6
Newest
 Curious
Impact
~723 people reached

  • 0 posts edited
  • 0 helpful flags
  • 4 votes cast
Apr
19
comment Limit the number of votes
Going through the user row would prevent the deadlock situation you mentioned wouldn't it?
Apr
19
accepted Limit the number of votes
Apr
19
comment Limit the number of votes
Does it also need a holdlock on it?
Apr
18
comment Limit the number of votes
I don't see why serializable is sufficient. If two requests both read the absence of the vote row won't they both try the insert? One will fail on the key constraint and there'll be an exception.
Apr
18
revised Limit the number of votes
edited tags
Apr
18
asked Limit the number of votes
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.