From this page:
http://www.artfulsoftware.com/infotree/qrytip.php?id=531&m=0
I wanted to see if, in SQL Server, this same mySQL query
- Could be reproduced
- Could be sped up with an index
- Would show up as a cursor in the estimated execution plan for the update statement
1. Reproduction
So, yes, this is a fairly simple script to reproduce.
set nocount on;
use enron;
go
if object_id('dbo.photos') is null
create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT);
else
truncate table dbo.photos;
go
if object_id('dbo.votes') is null
create table votes(id INT identity(1,1) primary key, userID INT, photoID INT);
else
truncate table dbo.votes;
go
DECLARE @counter INT;
SET @counter = 1;
WHILE @counter < 500
BEGIN
insert into dbo.photos(photo) values(rand() * 1000)
SET @counter = @counter + 1
END;
GO
DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256);
SET @counter = 1;
WHILE @counter < 50000
BEGIN
SELECT @userid = rand() * 100;
select @photoid = rand() * 1000;
if (@photoid between 0 and 500)
insert into votes(userid, photoid) values (@userid, @photoid)
SET @counter = @counter + 1;
END;
GO
|
However, in my version, I loaded a larger data set into the tables, as I wanted to have enough data to get a measureable difference when applying the index in step two.
Note also, that in my version, the values are randomly generated, but controlled, so they can only be inserted if they match a photo that we expect to actually have.
2. Indexing
Yes, the statement can be sped up with an index.
Here's the properties of the script, before index application:
Here's after applying the index:
It's a third of the cost. Win!
3. Cursor
There are loops - which is expected - but SQL Server doesn't dissolve into an explicit cursor to handle the update.
