SQL Server Performance

ACC2k: SQL Statemt imposs./endless loop?

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by MakeItSo, Dec 30, 2004.

  1. MakeItSo New Member

    Hello friends,

    I am having a little problem with an ASP I'm just progging.
    Background: I query an Acc2k database for employees who speak certain languages.

    This is a typical SQL statement as created during runtime (output by response.write):

    SELECT * FROM Personen_Sprachen
    WHERE RecID IN
    (
    (SELECT RecID FROM Tab_Sprachen WHERE Lang='Englisch' AND Level>=4),
    (SELECT RecID FROM Tab_Sprachen WHERE Lang='Französisch' AND Level>=2)
    )

    The query doesn't return an error in Access, but it doesn't produce a data output either (data window won't even open). "Tab_Sprachen" is also the underlying main table of the query "Personen_Sprache", so there might be a sort of circular reference causing problems...

    The ASP produces "an exception has occured" error at the conn.execute statement. After that, the database is locked by the ASP, I cannot delete the LDB and only a restart of my computer will unlock it!

    I am posting here, since it is very obviously the SQL statement, that's causing the problems.

    Can you help me with this?

    Thanks & Best wishes,
    Andy

    "Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown
  2. mmarovic Active Member

    I am not access expert, but I think you should also post Personen_Sprachen query to be helped.
  3. MakeItSo New Member

    Thanks for the quick response mmaroovic!<br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />Nothing spectacular in query Personen_Sprachen, except, as mentioned, the dependency on Tab_Sprachen:<br /><br /><pre id="code"><font face="courier" size="2" id="code">SELECT S.RecID AS Pers, P.Name, P.Vorname, P.Abteilung, P.Office, P.Telefonnummer, P.Sonstiges, S.Lang, S.Level<br />FROM Tab_Personen P INNER JOIN Tab_Sprachen S ON P.ID = S.Person_ID;</font id="code"></pre id="code"><br /><br />"Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown
  4. mmarovic Active Member

    Until an Access expert responds, let's try this code:


    SELECT * FROM Personen_Sprachen
    WHERE RecID IN (SELECT RecID
    FROM Tab_Sprachen
    WHERE (Lang='Englisch' AND Level>=4)
    or (Lang='Französisch' AND Level>=2)
    )

    From performance point of view query with union instead of 'or' operator might be faster, but I really don't have Access experience to judge.
  5. MakeItSo New Member

    I tried this before and: That is exactly the problem! It doesn't return the records I want...
    It returns those people to whom at least one of the two criterions apply.

    However, I need an AND coniguration. So perhaps the UNION would be it..
    I'll give it a try. Need to leave now.

    Thanks a bunch for that UNION hint, though!
    If I don't make it back tomorrow:
    Have a good start into New Year!

    Cheers,
    Andy

    "Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown
  6. mmarovic Active Member

    quote:I tried this before and: That is exactly the problem! It doesn't return the records I want...
    It returns those people to whom at least one of the two criterions apply.

    However, I need an AND coniguration. So perhaps the UNION would be it..
    How about:

    SELECT * FROM Personen_Sprachen
    WHERE RecID IN (SELECT RecID
    FROM Tab_Sprachen
    WHERE Lang='Englisch' AND Level>=4
    )
    and RecID IN (SELECT RecID
    FROM Tab_Sprachen
    WHERE Lang='Französisch' AND Level>=2
    )

    Select distinct *
    from Personen_Sprachen ps
    join ...

    would work too, but I don't know Access join syntax...

    ... and Happy New Year!
  7. MakeItSo New Member

    Access Syntax is pretty much like SQLServer's.
    That join idea sounds really good!
    I'll try both.

    Thanks for all your help so far!

    Cheers,
    Andy

    "Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown
  8. MakeItSo New Member

    Got it!
    I used your ...IN() AND IN()... structure from above and corrected another mistake on my side:
    [shame]
    I need to use Person_ID as criteria, of course, not the unique RecId
    [/shame]
    ;o)

    For anyone stumbling across this thread in need of something similar,
    So here's one typical final SQL with superfluous records outfiltered:

    SELECT * FROM Personen_Sprachen
    WHERE Person_Id IN
    (
    SELECT Person_Id
    FROM Tab_Sprachen
    WHERE Lang='Englisch' AND Level>=3
    )
    AND Person_Id IN
    (
    SELECT Person_Id
    FROM Tab_Sprachen
    WHERE Lang='Französisch' AND Level>=2
    )
    AND Lang IN
    (
    'Englisch', 'Französisch'
    )
    ORDER BY [NAME] ASC, [LEVEL] DESC

    Once again big thanks and a good start into 2005!
    ANdy

    "Never lose your cool - even the most honest finder couldn't give it back to you..." - Unknown

Share This Page