p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Query question


Message #1 by "Christian Cryder" <christianc@g...> on Fri, 22 Mar 2002 16:24:48 -0700
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1D60E.4AA2E440
Content-Type: text/plain;
	charset="iso-8859-1"

Does it compile it to a SQL server Stored proc? If not it is likely to be
slower than a SQL server proc. SQL Server would be able to do a much better
job of optimising the queries than some other 3rd party software. Also as
procs are run they are optimised further each time they are run, ie the
performance of each proc will improve over time. If it just compiles the
code again each time you aren't getting a speed gain.

The other reason to use procs is that you can use them to encapsulate a
bunch of SQL scripts on one module. So some of the code I posted before
could not be run outside a proc. As another example I have a website where
someone can add a new project. I won't go into detail, but this means that
the new project needs to be added and initialised. That means a whole bunch
of records need to be inserted. If I use a proc for this I can call it a
forget about it.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: Christian Cryder [mailto:christianc@g...]
Sent: Tuesday, 26 March 2002 3:57 AM
To: sql language
Subject: [sql_language] Re: Query question


Ah, I see...I was able to get it to work doing this:

SELECT *
FROM Commission
WHERE VendorID IN (
    SELECT VendorID FROM Commission WHERE RepID = 30
)

Now, as to why I'm storing RepLevel in this table, its because it can vary
from the default which is stored in Reps (ie. a Rep has a default level, but
can sometimes be assigned to other levels).

Now, here's another question...several people have suggested using stored
procs for this. What is the rationale for using stored procs as opposed to
regular SQL? I've heard that there are performance advantages, but I'm using
JDBC prepared stmts (which I believe are compiled to stored procs behind the
scenes anyway). So what are the other reasons to use a store proc?

Thanks,
Christian
----------------------------------------------
Christian Cryder [christianc@a...]
Internet Architect, ATMReports.com
Barracuda - http://barracuda.enhydra.org
----------------------------------------------
 "What a great time to be a(n employed) Geek"

> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Monday, March 25, 2002 12:28 AM
> To: sql language
> Subject: [sql_language] Re: Query question
>
>
> The RepLevel is an attribute of the Rep is it not? If so, why don't you
> store it in the Reps table?
> That way you'd have the VendorID, the RepID and the Amt.
>
> Next, to do your first query you could use a subquery:
>
> SELECT
>     VendorID, RepID, Amt
> FROM
>     Table1
> WHERE
>     VendorID IN
>         (
>         SELECT
>             VendorID
>         WHERE
>             RepID = XX
>         )
>
> To do you second query, you'd need to use some conditional logic,
> eg using a
> CASE statement...but the syntax may vary depending on your
> database product.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Christian Cryder" <christianc@g...>
> Subject: [sql_language] Query question
>
>
> : Hi folks!
> :
> : I have a question about how to perform a query (and if this is
> really easy
> : and I'm just being stupid, please feel free to point me the direction of
> : some resources and I'll be happy to go spend some time researching...but
> I'm
> : stuck right now, so I'm asking :-). Let's say I have a table called
> : Commission that looks like this:
> :
> : VendorID    RepID       RepLevel    Amt
> : ----------- ----------- ----------- ---------------------
> : 1           20          1           50.0000
> : 1           21          1           50.0000
> : 1           30          2           15.0000
> : 1           50          3           10.0000
> : 2           20          1           60.0000
> : 2           21          1           60.0000
> : 2           40          2           20.0000
> : 2           50          3           15.0000
> :
> : This would indicate that there were two different Vendors; Reps 20, 21,
> 30,
> : and 50 each get paid something for Vendor 1, while Reps 20, 21,
> 40, and 50
> : get paid for Vendor 2. So, here's my question...
> :
> : 1. Let's say I want to generate a commission report for Rep 30;
> how would
> I
> : write a query to grab all the records for any vendor associated
> with this
> : Rep? For instance, since Rep 30 is linked to Vendor 1, I need
> to get back
> : all the records associated with Vendor 1, like this:
> :
> : VendorID    RepID       RepLevel    Amt
> : ----------- ----------- ----------- ---------------------
> : 1           20          1           50.0000
> : 1           21          1           50.0000
> : 1           30          2           15.0000
> : 1           50          3           10.0000
> :
> : 2. Now to complicate it a little...see the RepLevel column? What'd I'd
> like
> : to do is say that a rep can see the amounts for any level <= to
> his level,
> : but for levels > his level, he should see $0 amts. Here's an example:
> :
> : If I query for RepID = 30, I'd like to see this:
> :
> : VendorID    RepID       RepLevel    Amt
> : ----------- ----------- ----------- ---------------------
> : 1           20          1           50.0000
> : 1           21          1           50.0000
> : 1           30          2           15.0000
> : 1           50          3            0.0000
> :
> : vs. if I query for RepID = 20, I'd like to see this:
> :
> : VendorID    RepID       RepLevel    Amt
> : ----------- ----------- ----------- ---------------------
> : 1           20          1           50.0000
> : 1           21          1           50.0000
> : 1           30          2            0.0000
> : 1           50          3            0.0000
> :
> : Does that make sense? Again, I hope these aren't too obvious of
> questions,
> : I'm just fairly new to SQL still and am not sure how to proceed. Please
> feel
> : free to educate me ;-)
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>




  Return to Index