declare (whatever)?
below is an example i read. Where do you put this to make it execute, is it
the view screen or the stored procedure screen?
I'm using MSDE now to learn, and I can't get nothing working except simple
select query statements.
In the northwind example (northwindcs), how would I do a parameter query
like this:
Have a dialog box ask user to enter customerid to bring up. (in a query now
not a form)
Also, how would you check if a certain customerid exist? Example, CHOPS is
one customerid. If I wanted to use a query to check if it exist, and return
no records, but just do an action (like add a record) if it didn't exist,
how?
CPU SQL
(ms)
-- Convert to varchar (implicitly) and compare right two digits
-- (original version -- no I didn't write it)
4546 select sum(case right(srp,2)
when '99' then 1 when '49' then 1 else 0 end)
from sf
-- Use LIKE for a single comparison instead of two, much faster
-- Note that the big speedup indicates that
-- CASE expr WHEN y then a WHEN z then b .
-- recalculates expr for each WHEN clause
2023 select sum(case when srp like '%[49]9' then 1 else 0 end)
from sf
I tried some variations of this, and indeed it seems that there is a cost
when the expression appears with several WITH clauses. I tried a variation
of this, where I supplemented the test table with a char(2) column, so I
could factor out that the WITH clauses themselves were not the culprits.
CREATE TABLE realthing (realta real NOT NULL,
lasttwo char(2) NOT NULL)
go
INSERT realthing (realta, lasttwo)
SELECT r, right(r, 2)
FROM (SELECT r = convert(real, checksum(newid()))
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b) AS f
go
DBCC DROPCLEANBUFFERS
go
DECLARE @start datetime
SELECT @start = getdate()
SELECT SUM(CASE right(realta, 2)
WHEN '99' THEN 1
WHEN '49' THEN 1
WHEN '39' THEN 1
ELSE 0 END)
FROM realthing
SELECT datediff(ms, @start, getdate()) -- 20766 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @start datetime
SELECT @start = getdate()
SELECT SUM(CASE WHEN right(realta, 2) LIKE '[349]9' THEN 1 ELSE 0 END)
FROM realthing
SELECT datediff(ms, @start, getdate()) -- 8406 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @start datetime
SELECT @start = getdate()
SELECT SUM(CASE lasttwo
WHEN '99' THEN 1
WHEN '49' THEN 1
WHEN '39' THEN 1
ELSE 0 END)
FROM realthing
SELECT datediff(ms, @start, getdate()) -- 920 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @start datetime
SELECT @start = getdate()
SELECT SUM(CASE WHEN lasttwo LIKE '[349]9' THEN 1 ELSE 0 END)
FROM realthing
SELECT datediff(ms, @start, getdate()) -- 1466 ms.
Thus, when using the char(2) column LIKE is slower despite that there
is only one WHEN condition. So indeed it seems that right(realta, 2)
is computed thrice in the first test.
Another funny thing is the actual results from the queries - they are
different. When I ran:
select count(*) from realthing where lasttwo <> right(realta, 2)
The result was about half of the size of realthing! I can't see that
this difference affects the results though.
Now, your article had a lot more tests, but I have to confess that
you lost me quite early, because you never discussed what is the
actual problem. Since you are working with floating-poiont numbers
there is a great risk that different methods not only has different
execution times, but also gives different results.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
