DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL statement running slow.

  1. #1
    Bryant Smith Guest

    SQL statement running slow.


    SELECT field1,field2
    FROM table1
    WHERE field2 LIKE '%stringtext%'
    AND field1 NOT IN
    (SELECT field1 FROM table2 WHERE field3 ='stringtext')

    I have a mid-sized database in Access 2000, where this SQL statement is currently
    running slow. Does anyone have a suggestion?


  2. #2
    Gonzalo Medina Guest

    Re: SQL statement running slow.


    "Bryant Smith" <bryantsmith@excite.com> wrote:
    >
    >SELECT field1,field2
    > FROM table1
    > WHERE field2 LIKE '%stringtext%'
    > AND field1 NOT IN
    > (SELECT field1 FROM table2 WHERE field3 ='stringtext')
    >
    >I have a mid-sized database in Access 2000, where this SQL statement is

    currently
    >running slow. Does anyone have a suggestion?
    >

    This kind of SQL-Stament is slow even if you write the same SQL statement
    in SQL Server, I will recomend:

    1.- Check the index in both tables, for table1.field2, table2.field3
    2.- I will try to change the whole SQL Statement, because the use of LIKE
    won't help you.
    3.- Create a QueryDef for table2, then use this querydef to join with the
    table1. You will get a better perfomance.

    Let me know if I'm wrong, we can talk about it.

    Have fun :-)




  3. #3
    Trayce Jordan Guest

    Re: SQL statement running slow.

    Try using joins rather than subqueries. They're generall faster and more
    compatible with ANSI SQL syntax.

    Try using:
    SELECT T1.field1, T1.field2 FROM Table1 T1 LEFT OUTER JOIN Table 2 T2 ON
    T1.field1 = T2.field2
    WHERE T1.Field2 LIKE '%stringtext%' AND T2.field3 = 'stringtext'

    As an earlier post indicated, the LIKE clause (especially when used with '%'
    before and after the text) takes time. If there's a way to avoid using the
    LIKE keyword and change your logic, I'd look to implement that.

    Hope this helps.

    "Bryant Smith" <bryantsmith@excite.com> wrote in message
    news:39e25362$1@news.devx.com...
    >
    > SELECT field1,field2
    > FROM table1
    > WHERE field2 LIKE '%stringtext%'
    > AND field1 NOT IN
    > (SELECT field1 FROM table2 WHERE field3 ='stringtext')
    >
    > I have a mid-sized database in Access 2000, where this SQL statement is

    currently
    > running slow. Does anyone have a suggestion?
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center