examples

[JIMMIE WHITAKER]

I see a lot of example, but where do these example procedures go. Like
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?[color=blue]
> 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[/color]


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..Orde rs a
CROSS JOIN Northwind..Orde rs b) AS f
go
DBCC DROPCLEANBUFFER S
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 DROPCLEANBUFFER S
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 DROPCLEANBUFFER S
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 DROPCLEANBUFFER S
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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Aaron W. West]

Re: examples

Since you replied to what was originally my post, I'll respond.

First, MSDE is just a database engine. It may be packaged with other
programming tools (eg. Visual Studio .NET), but is not itself a programming
tool. It is programmable in the sense that you can create stored procedures
and SQL batches, but these have to be entered from some sort of client tool.

re: where do these procedures go...

I'm not very famiar with MSDE. I'm assuming it works just like the full SQL
Server, but is lacking some client tools. I enter all my queries into Query
Analyzer. If you don't have that included with MSDE, perhaps it's worth
spending about $50 for MS SQL Server Developer Edition to get it. But if you
can enter SELECT statements, I think you should be able to enter any
Transact/SQL batch, as long as your database user has the proper access
permissions. It's been a while since I tried it, but I think even Access can
be used to create tables and procedures, using SQL Passthru queries.

My examples were lacking DDL (data definition language, eg CREATE TABLE),
because I didn't think it important that anyone else try running these
queries. Erland provided DDL in his reply.

Everything up to but not including a GO is a batch of SQL statements that
you should be able to enter anywhere a SQL query can be executed, I think,
though you should be using some sort of SQL do ($dbh->do() in Perl DBI) or
SQLExecute() I think (ODBC in C) or cmd.Execute I think (ADO in Visual
BASIC?) or a passthru query that is set to return no results in Access.
Check the documentation for whatever language you are using, because clearly
the use of MSDE (a developer tool) requires the use of some sort of
programming tool to do what you are talking about.

I dont think you should ask how to bring a dialog box up here; that isn't a
SQL question...

re: customerid exists...

If you can execute a SELECT query, you can do this:

SELECT 1 FROM Northwind..Cust omers
WHERE CustomerID = 'CHOPS'

...using dynamic SQL. But parameterized SQL is more secure, faster, etc.

In Perl using DBI and DBD::ODBC (see www.activestate.com for Perl for
Windows)...

perldoc DBI

# DBItest.pl
my $user = 'SA';
my $auth = 'SAPassword'; #bad practice?
my $dbh = DBI->connect("dbi:O DBC:Northwnd", $user, $auth) || die;
#$dbh->do("USE Northwind");
my $sql = q(SELECT 1 FROM Northwind..Cust omers
WHERE CustomerID = ?);
# replace with a dialog if you like:
print "Enter customer ID:";
my $custid=<>; #'CHOPS';
chomp $custid;
my $sth = $dbh->prepare($sql ) || die;
$sth->execute();
my ($custexists) = $sth->fetchrow_array ();
if ($custexists) {
print "Customer $custid exists\n";
} else {
print "Customer $custid does not exist\n";
#display a dialog, retrieve values, etc
#$dbh->do("INSERT northwnd..Custo mers (...) VALUES (...)");
}

You may prefer to use Visual Basic rather than Perl to build Windows GUI
apps, although it certainly should be possible in Perl as well. If I were
building a GUI app in Perl I might try TCL/Tk:

http://search.cpan.org/~vkon/TclTk-0.75/Tk.pm

..... but again all this is off topic for a SQL newsgroup.

Decide which programming tool you want to use, make sure you have that tool,
check the documentation for the product, then ask your question in a topical
group if you still have problems.

And try a tutorial for the sort of thing you are talking about. Many MS
tools include tutorials for using the Northwind database.

-aaron


"JIMMIE WHITAKER" <kpsklab@worldn et.att.net> wrote in message
news:xFXFc.1911 92$Gx4.131412@b gtnsc04-news.ops.worldn et.att.net...
I see a lot of example, but where do these example procedures go. Like
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?

[Erland Sommarskog]

Re: examples

JIMMIE WHITAKER (kpsklab@worldn et.att.net) writes:[color=blue]
> I see a lot of example, but where do these example procedures go. Like
> 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.[/color]

If you are using MSDE only, there's no GUI tools with it. But you can
use the OSQL comand-line tool to run scripts.

However, judging from your talk of view and stored procedure screens,
it sounds like you have Enterprise Manager available. In such case you
also have Query Analyzer available, from which you can run all sorts of
queries.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp