• (cs) in reply to Rich
    Anonymous:
    Gsquared:

    I use the second format myself, but only for readability.  It makes more sense to me to have the condition of the join as part of the join statement.  Makes it clear that that part of the script is to handle the join, while the Where statement determines which subset of rows to return from either table.  Other DBAs use the other because it makes more sense to them and is more readable to them.

    There are things you can do in the second form (with joins) which are just not possible in the first. I can't remember the example off the top of my head, it may be if you have more than two tables...

     

    Rich 

     

    One query is How many customers did not purchase Cherrios during the month of March

    Select Count(*)
    From Customers C
     

     
  • (cs) in reply to Rich
    Anonymous:
    Gsquared:

    I use the second format myself, but only for readability.  It makes more sense to me to have the condition of the join as part of the join statement.  Makes it clear that that part of the script is to handle the join, while the Where statement determines which subset of rows to return from either table.  Other DBAs use the other because it makes more sense to them and is more readable to them.

    There are things you can do in the second form (with joins) which are just not possible in the first. I can't remember the example off the top of my head, it may be if you have more than two tables...

     

    Rich 

     

    One query is How many customers did not purchase Cherrios during the month of March

    Select Count(*)
    From Customers C
     

      Left
  • (cs) in reply to Rich
    Anonymous:
    Gsquared:

    I use the second format myself, but only for readability.  It makes more sense to me to have the condition of the join as part of the join statement.  Makes it clear that that part of the script is to handle the join, while the Where statement determines which subset of rows to return from either table.  Other DBAs use the other because it makes more sense to them and is more readable to them.

    There are things you can do in the second form (with joins) which are just not possible in the first. I can't remember the example off the top of my head, it may be if you have more than two tables...

     

    Rich 

     

    One query is How many customers did not purchase Cherrios during the month of March

    Select Count(*)
    From Customers C
     

      Left Join
  • Doctor (unregistered) in reply to Cody

    My sentiment exactly.

  • voutmaster (unregistered) in reply to BradC

    The two statements are identical. The second style was introduced in a more recent ANSI standard, but they are both supported by MS SQL. Choose the one that looks better to you.

  • Scott (unregistered)

    First comment and I jut had to reply.  It drives me nuts when a solution is accepted or disregarded based on the person providing the solution.  My stomach dropped when I read that the solution never was implemented.  Ridiculous.

  • (cs) in reply to voutmaster
    Anonymous:

    The two statements are identical. The second style was introduced in a more recent ANSI standard, but they are both supported by MS SQL. Choose the one that looks better to you.

     

    No they're not.  There are some things you just cannot do withthe old syntax... Here's an example:

    Create Table Customers
    (CustID int Primary Key Not Null,
     CustName varchar(20) Not Null,
     State Char(2) null)
    Go
    Create Table Orders
    (OrdID Int Primary Key Not Null,
     CustID Int Not Null
          References Customers(CustID),
     OrdDate DateTime)
    Go

    Insert Customers values( 1, 'Al', 'OR')
    Insert Customers values( 2, 'Bob', 'CA')
    Insert Customers values( 3, 'Dave', 'AZ')
    Insert Customers values( 4, 'Jane', 'CO')
    Insert Customers values( 5, 'Mary', 'OR')
    Insert Customers values( 6, 'Yussef', 'WA')
    Insert Orders Values(1001, 1, '1 Jan 2006')
    Insert Orders Values(1002, 2, '10 Jan 2006')
    Insert Orders Values(1003, 3, '15 Jan 2006')
    Insert Orders Values(1004, 4, '18 Jan 2006')
    Insert Orders Values(1005, 3, '18 Jan 2006')
    Insert Orders Values(1006, 3, '5 Feb 2006')

     

    Now write a query that lists all the customers not in CA and the count of their orders in the month of January.

    Using the new ANSI-92 Joins, it's easy:
    Select C.CustName, Count(OrdID)
    From Customers C
        Left Join Orders O
            On O.CustID = C.CustID
              And O.OrdDate Between
                '1 Jan 2006' And '1 Feb 2006'
    Where C.State <> 'CA'
    Group By C.CustName

    Using the old *= syntax, it's virtually impossible (you have to resort to subquerys and Exists and Not Exists)

  • (cs) in reply to Bullet

    Bullet:
    No they're not.  There are some things you just cannot do withthe old syntax... Here's an example:

    ... 

    Now write a query that lists all the customers not in CA and the count of their orders in the month of January.

    Using the new ANSI-92 Joins, it's easy:
    Select C.CustName, Count(OrdID)
    From Customers C
        Left Join Orders O
            On O.CustID = C.CustID
              And O.OrdDate Between
                '1 Jan 2006' And '1 Feb 2006'
    Where C.State <> 'CA'
    Group By C.CustName

    Wasn't the question specifically about the two ways of writing an inner join?  It's hardly surprising that you can do different things if you use different language features. ;-)  (Also, "new ANSI-92"?) 

  • duh (unregistered) in reply to ammoQ

    the first is non-ANSI syntax, and the second used ANSI syntax.   The queries are equivalent

  • duh (unregistered) in reply to rbriem

    nope, there are the same query.   look up "outer join"...

  • (cs) in reply to iwpg
    iwpg:

    Bullet:
    No they're not.  There are some things you just cannot do withthe old syntax... Here's an example:

    ... 

    Now write a query that lists all the customers not in CA and the count of their orders in the month of January.

    Using the new ANSI-92 Joins, it's easy:
    Select C.CustName, Count(OrdID)
    From Customers C
        Left Join Orders O
            On O.CustID = C.CustID
              And O.OrdDate Between
                '1 Jan 2006' And '1 Feb 2006'
    Where C.State <> 'CA'
    Group By C.CustName

    Wasn't the question specifically about the two ways of writing an inner join?  It's hardly surprising that you can do different things if you use different language features. ;-)  (Also, "new ANSI-92"?) 

     

       I apologize if I offended you, not my intent.  I understood the "question" to be a comparison between the old syntax for joins, (of any kind) and the new ANSI-92 Syntax..  I explicitly mentioned that they are not equivilent only when using outer joins. 

        You're right, as far as inner joins are concerned, the two syntaxes are equivilent, but for outer joins they are not.   Because the difference between them when using outer joins is significant, and because it is so commonly misunderstood, I thouight it was worth pointing out.  (and I still think so.)    Making a point in this thread about whether the "question" is narrowly defined only for inner joins is hardly relevant.  I see this forum as a place for a free interchange of knowledge, techniques and ideas, and not a place to show how right you are or to quibble about how the question was defined.   

     And the ANSI-92 syntax is "new" in comparsion to the "old"er syntax it replaced. 

  • (cs) in reply to duh

    Anonymous:
    the first is non-ANSI syntax, and the second used ANSI syntax.   The queries are equivalent

     

    The two queries used in the first post in this thread on this topic do happen to be equivilent. 
    On the more broader issue, as to whether ANSI-89 and ANSI-92 join syntax are generally equivilent, the answer is no they are not.

  • (cs)

    As a database developer responsible for a very high transaction system, I can agree with some (not all) of what is in this document.  Clearly, this WTF should never have happened, the solution is so simple that any qualified db developer with some balls could have prevented this from happening.  I just hope the database dev's out there can muster the courage to inform the un-enlightened how to handle this optimally.  Just think, it could be any one of us who takes that job next!  All this anonymizing prevents us from knowning for sure beforehand.

     

    -P 

  • sergiogiogio (unregistered) in reply to lohki0

    The loop is not a WTF to me. Using a single INSERT statement vs. such a loop has several problems:
    - you have much less control on what you kind of data transformation (and process logging) you can apply, because SQL was not designed for that. You can try coding mild-complex data transformation with SQL but then your solution becomes non-portable and non-readable.
    - if the input tables become too big, the transaction log may well not be able to hold all of the transaction you submit.
    I agree the statement should be prepared outside of the loop though.

  • Dave (unregistered) in reply to An apprentice
    Anonymous:
    Anonymous:

    the ones that don't automatically restart when the computer is rebooted.

    WTF? Have you never heard the startup folder in the start menu?

    :D 

    Rich 

    I think programs in the startup folder (and registry Run* entries) are not processed until someone actually logs in. While a service can be run in the background even without logged users.

    If you put the entry in "scheduled tasks" scheduled for "at system startup" it runs when the system is rebooted, even if no user logs in.
     

  • Bill Moore (unregistered) in reply to BradC

    The first one is going to be depreciated and i think it runs faster

  • Jasmine (unregistered)

    At the last company I worked at, almost every process to deal with multiple rows in the database was written like this. Most of the SQL procedures were also written to force SQL Server to process huge queries one row at a time. People really should learn SQL before they start writing SQL.

  • rawSpeed (unregistered)
    Alex Papadimoulis:

    A "smallish" broadcast of 2,000 messages took about three hours to process. From a technical perspective, this involved inserting 2,000 rows in a "Queue" table in their Microsoft SQL Server database. Obviously, the users were not to thrilled about this either, especially when they wanted to broadcast news to their subscribers.

    2000 messages / ( 3 hours * 60 minutes/ hour ) = 6 seconds per message.

    That is mind-bogglingly slow!  The worst record I've personally wtinessed was using webMethods to transport messages across a LAN from one Oracle database to another:  2 or 3 messages per seconds. 

    Of course the webMethods coders blamed the database and the stored procedure I wrote for them to process the messages at the receiving database.  But I demonstrated I could use SQL*NET to push messages directly between the databases, invoke the procedure, and get a through-put of hundreds of messages per second.

    webMethods to transport messages?  It was the corporate standard!

Leave a comment on “Very Slow Service”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article